CODEWITHSUNDEEP
sql-servert-sqlssisbusiness-intelligence
shaadi
shaadi

Reputation: 171

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

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?

Upvotes: 1

Views: 1861

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

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]
;

Upvotes: 1

Related Questions