how to store multi row query result in a variable in SSIS

Question

My query:

SELECT CONCAT('E.',+COLUMN_NAME +'='+'SE.'+COLUMN_NAME,',')
FROM Company2.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'employee'

Query result:

E.Id=SE.Id,
E.Name=SE.Name,
E.Salary=SE.Salary,

I want to store query result in variable like

 @variable=E.Id=SE.Id,E.Name=SE.Name,E.Salary=SE.Salary,

so that I can use in update statement like this:

Update E 
set @variable
from Employee as E
join Staging_Employee as SE
on E.Id = SE.Id

Any idea that how i can achieve this?

Extra information: Basically I am trying to update Employee table from Staging_Employee table in a database.And trying to store columns in a variable so that I can use my update statment in SSIS (excute sql task) and employee table has 30+ columns.

Thanks in advance

Edit-1:

For insert: (1) oldedb source > (2)lookup task(reference table is empty Employee table in DB) > (3)Insert rows to Oledb destination.

For updates: From (2) lookup matched output > lookup task(reference table is employee table in database check all records id=id) > staging table > Execute sql task (!! Here I want to use update statment to update records from staging table to actual table in DB and I am basically trying to use variable in update statment to save field = value (Query Result) in variable so that when table schema changes fields=value automatically get updated****!!

Edit-2:

I used John's solution and it works as long as PK of table is named ID for instance I modified John's code to use for all tables for instance @table=anytable and @stage_Table=staging_anytable:

For example Trying to use code for sales table:

Declare @Table varchar(100)
Declare @stage_Table varchar(100)
Set @Table = 'Sales'
Set @stage_Table ='Stage_Sales'



    Declare @SQL varchar(max) ='
    Merge  '+@Table+'
    Using '+@stage_Table+' B on A.ID = B.ID
    When  Matched Then
    Update SET '+Stuff((Select ',A.'+quotename(Column_Name)+'=B.'+quotename(Column_Name) 

                    From INFORMATION_SCHEMA.COLUMNS

                    Where Table_Name= 'sales' and Column_Name<>'ID'

                    For XML Path ('')),1,1,'')  +'

    ;

    '
    --Print @SQL
    Exec(@SQL)

But I get this error because ID is SalesID: Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "A.ID" could not be bound. Msg 207, Level 16, State 1, Line 3 Invalid column name 'ID'.

Any idea @John how I can modified your code in this situation?

It seems like you know the column names already. Why do you need SSIS for this and not just a stored procedure? I'd also recommend only processing changes. Also, are you missing inserts? — KeithL, Aug 09 '17 at 16:56
BTW, to answer your question. Store the result set into a ADO object and loop thru it in a foreach, and then append the items into a variable and then insert that variable into a sql statement (make sure to remove last comma) and then in execute SQL Run the SQL variable. — KeithL, Aug 09 '17 at 16:59

John Cappelletti · Accepted Answer · 2017-08-10T01:54:12.280

1

Have you considered MERGE

EDIT - Dynamic

Declare @SQL varchar(max) ='
Merge  Employee A
 Using Staging_Employee B on A.ID = B.ID
 When  Matched Then
  Update SET '+Stuff((Select ',A.'+quotename(Column_Name)+'=B.'+quotename(Column_Name) 
                From INFORMATION_SCHEMA.COLUMNS
                Where Table_Name= 'employee' and Column_Name<>'ID'
                For XML Path ('')),1,1,'')  +'
;
'
Print @SQL
--Exec(@SQL)

The Generated SQL is

Merge  Employee A
 Using Staging_Employee B on A.ID = B.ID
 When  Matched Then
  Update SET A.[Name]=B.[Name],A.[Salary]=B.[Salary]
;

edited Aug 10 '17 at 01:54

answered Aug 09 '17 at 17:52

John Cappelletti

79,615
7
44
66

John I want to achieve this in SSIS package and Update SET A.Name = B.Name is not what I want. I want to SET column=value from the variable I want to keep package dynamic so that I don't have to change columns in update statements if there is a change in underlying table. – shaadi Aug 10 '17 at 01:09
@shaadi As you know SQL does not support Macro Substition, however, you can go DYNAMIC. Rather than doing 30'ish updates (1 per field), I would suggest a Dynamic Merge – John Cappelletti Aug 10 '17 at 01:55
John your code rocks! but there is an issue with it check my Edit-2. – shaadi Aug 10 '17 at 02:49
@shaadi In my answer, I assumed the PK was A.ID and B.ID. Looking at your enhancements, I think you would have to supply the keys (A and B) as well. – John Cappelletti Aug 10 '17 at 02:55
@shaadi FYI, with MERGE, you could also add an WHEN NOT MATCHED to handle the INSERTS as well. – John Cappelletti Aug 10 '17 at 02:56
@shaadi Just an after thought... you would have to exclude computed columns if any. – John Cappelletti Aug 10 '17 at 03:00
@shaadi to identiy computed columns SELECT * FROM sys.columns WHERE is_computed = 1 – John Cappelletti Aug 10 '17 at 03:05
TableName and ColumnName for computed columns SELECT sysobjects.name AS TableName, syscolumns.name AS ColumnName FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = sysobjects.id AND sysobjects.xtype = 'U' --User Tables WHERE syscolumns.iscomputed = 1 – John Cappelletti Aug 10 '17 at 03:06
thanks I am wondering how can I select and save name of primary key of a table in variable I haven't done googling yet, but if you know I will save my time. pseudo code: select variable = PK from anytable – shaadi Aug 10 '17 at 03:27
@shaadi take a peek at https://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table ... This may not be 100% in your envronment – John Cappelletti Aug 10 '17 at 03:32
@shaadi Happy to help – John Cappelletti Aug 10 '17 at 03:49

how to store multi row query result in a variable in SSIS

1 Answers1