SQL dynamically stores data from a database to another

Hi guys, I'm new here and I need your help. I'm building a sql query that aims to get data from an imported database from an excel file to an existing database that will store this information. I don't want to build a LINQ query for this. I want to create my own SQL query.

The request only has a problem. Let's move on to the fact that ^^.

DECLARE Data CURSOR FOR
SELECT [N#ºContab] FROM EFA..Eventos

DECLARE @N#ºContab NVARCHAR(200)
DECLARE @AssociadoId INT

DECLARE @LoopNum INT = 0
DECLARE @LoopRows INT

OPEN Data

SET @LoopRows = @@CURSOR_ROWS

WHILE @@FETCH_STATUS = 0 
    BEGIN
        IF @LoopNum = @LoopRows BREAK

        FETCH NEXT FROM Data INTO @N#ºContab
        SET @AssociadoId = (SELECT Id FROM L_7AssociadosAPDDEMO..L_Associado WHERE Numero = @N#ºContab)

    USE EFA
    DECLARE @ColNum INT = 1
    DECLARE @ColRows INT
    DECLARE ColNames CURSOR FOR
        SELECT column_name
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE table_name = 'Eventos'
            ORDER BY ordinal_position

    OPEN ColNames

    SET @ColRows = @@CURSOR_ROWS
    WHILE @@FETCH_STATUS = 0 
        BEGIN
            DECLARE @ColName VARCHAR(200)
            DECLARE @SQLQuery NVARCHAR(MAX)
            DECLARE @Temp NVARCHAR(MAX)
            DECLARE @Observacoes VARCHAR(MAX)

            IF @ColNum = @ColRows BREAK

            FETCH ColNames
            INTO @ColName

            SET @SQLQuery = N'SELECT EFA..Eventos.' + @ColName + ' FROM EFA..Eventos WHERE EFA..Eventos.N#ºContab = ' + @N#ºContab
            EXECUTE sp_executesql @SQLQuery, N'@Observacoes VARCHAR(MAX) OUTPUT', @Observacoes OUTPUT;              

            INSERT INTO L_7AssociadosAPDDEMO..L_Biografia (L_AssociadoId, Descricao, Observacoes, Data, DataCriacao, DataUltimaActualizacao)
                VALUES (@AssociadoId, @ColName, @Observacoes, '1900-01-01 00:00:00', '1900-01-01 00:00:00', '1900-01-01 00:00:00')

            SET @ColNum = @ColNum + 1
        END
    CLOSE ColNames
    DEALLOCATE ColNames

    SET @LoopNum = @LoopNum + 1
END
PRINT 'DONE'
CLOSE Data
DEALLOCATE Data

      

        
        
        
      

    

So I start a Data cursor that gets all rows with N # ºContab From the Eventos table in the EFA database .

Then I ran another Cursor ColNames to get all the column names from the Eventos table from the EFA database so that I can get the column name and value from that column.

DECLARE ColNames CURSOR FOR
        SELECT column_name
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE table_name = 'Eventos'
            ORDER BY ordinal_position

      

        
        
        
      

    

The main reason is that I can get the column name and store it in a column named Descricao and a value in a column named Observacoes inside the table name L_Biografia in L_7AssociadosAPDDEMO .

The problem is that when I get a column name that is a string and I settle for a dynamic SELECT query, I get the SELECT output, but not the very value I want. So if I try to get the Active column , which is the first column, it contains the value 1 . I want to get this value 1 , but I am getting SELECT output. So the @Observacoes variable is of type nvarchar, so it doesn't get the result, it becomes empty.

So i'm stuck here

SET @SQLQuery = N'SELECT EFA..Eventos.' + @ColName + ' FROM EFA..Eventos WHERE EFA..Eventos.N#ºContab = ' + @N#ºContab
EXECUTE sp_executesql @SQLQuery, N'@Observacoes VARCHAR(MAX) OUTPUT', @Observacoes OUTPUT;

      

        
        
        
      

    

Working with the request should work. The only thing I can't get is the value when I do a dynamic SELECT.

So what can I do to get the value from the SELECT ive dynamically built with the output of sp_executesql?

Thanks guys in advance. If you need more information, just ask.