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.

+3


source to share


1 answer


If your dynamic query is returning X rows then use this:

You can create a table variable used to store string values ​​from a custom column

DECLARE @table AS Table (value nvarchar(max)) 

      

then add:

Insert into @table(value) Execute sp_executesql @SQLQuery...

      

and use it or request it



Select value From @table

      

If you are only getting 1 value from a dynamic query use this:

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

      

I just added the @Observacoes = output variable after selecting in @SQLQuery.

The reason is that it does not automate saving the result of selecting an output variable when added output

to the and declarations sp_executesql

. You still need to install it on something ...

+1


source







All Articles