sql server - SQL dynamically store data from a database to another -

hi guys im new here , need help. im building sql query purpose data imported database excel file existing database hold information. dont want build linq query that. want build native sql query.

the query has problem. lets ^^.

declare data cursor select [n#ºcontab] 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 data @n#ºcontab         set @associadoid = (select id l_7associadosapddemo..l_associado numero = @n#ºcontab)      use efa     declare @colnum int = 1     declare @colrows int     declare colnames cursor         select column_name             information_schema.columns             table_name = 'eventos'             order 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             @colname              set @sqlquery = n'select efa..eventos.' + @colname + ' efa..eventos efa..eventos.n#ºcontab = ' + @n#ºcontab             execute sp_executesql @sqlquery, n'@observacoes varchar(max) output', @observacoes output;                            insert 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 start cursor data gets rows n#ºcontab table eventos in efa database.

then start cursor colnames column names table eventos efa database can column name , value column.

declare colnames cursor         select column_name             information_schema.columns             table_name = 'eventos'             order ordinal_position 

the main reason can column name , store on column named descricao , value on column named observacoes inside table name l_biografia on l_7associadosapddemo database.

the problem when column name string , concat on dynamic select query select output not value need. if try column active first column, contains value 1. want retrieve value 1 im getting select output. variable @observacoes nvarchar type doesnt output, gets empty.

so im stuck in here

set @sqlquery = n'select efa..eventos.' + @colname + ' efa..eventos efa..eventos.n#ºcontab = ' + @n#ºcontab execute sp_executesql @sqlquery, n'@observacoes varchar(max) output', @observacoes output; 

the query work has should work. thing cant value when execute dynamic select.

so can retrieve value select ive dynamically built sp_executesql output?

thank guys in advance. if need more info ask.

if dynamic query return x rows use this:

you can create table variable used store string values custom column

declare @table table (value nvarchar(max))  

then add:

insert @table(value) execute sp_executesql @sqlquery... 

and use or query it

select value @table 

if 1 value dynamic query, use that:

set @sqlquery = n'select @observacoes = efa..eventos.' + @colname + ' efa..eventos efa..eventos.n#ºcontab = ' + @n#ºcontab execute sp_executesql @sqlquery, n'@observacoes varchar(max) output', @observacoes output; select @observacoes... 

i added output variable @observacoes = after select in @sqlquery .

the reason being not automaticaly store result of select output variable when add output in declaration , sp_executesql. still have set somthing...