Get name from variable using index in T-SQL

Question

Using the following two queries

Query 1:

DECLARE @ContentColumnNamesSRC NVARCHAR(4000) = NULL,

SELECT 
    @ContentColumnNamesSRC = COALESCE(@ContentColumnNamesSRC + ', ', '') + '[' + name + ']'
FROM    
    tempdb.sys.columns 
WHERE 
    1 = 1
    AND object_id = OBJECT_ID('tempdb..#tempTable')
    AND column_id < 9 -- First 8 columns are ID data, which is what I am after

Query 2:

DECLARE @ContentColumnNamesDST NVARCHAR(4000) = NULL,

SELECT 
    @ContentColumnNamesDST = COALESCE(@ContentColumnNamesDST + ', ', '') + '[' + name + ']'
FROM    
    tempdb.sys.columns 
WHERE 
    1 = 1
    AND object_id = OBJECT_ID('Import.dbo.ContentTable')
    AND column_id < 9 -- First 8 columns are ID data, which is what I am after

I can get the first 8 columns from each table into a variable.

What I would like to do is find a way to get the values out of the variable, such that I can match the column names.

They should be identical in each table, and I need it to be able to create a dynamic merge statement, such that the columnsnames from each variable

@ContentColumnNamesSRC

and

@ContentColumnNamesDST

line up, so I can use it in a merge statement.

The point of this is to be able to use it in a loop, and all i would have to do is change which tables it looks at and the merge statements would still work.

Ideally, id like to end up with something like the following:

SELECT @StageSQLCore = N'USE Staging;

BEGIN TRANSACTION
MERGE '+@StageTableCore+' AS DST
USING '+@ImportTableCore+'  AS SRC
ON (SRC.[Key] = DST.[Key])
WHEN NOT MATCHED THEN
INSERT ('+@StageTableCoreColumns+')
VALUES (
    '+@ImportTableCoreColumns+',GETDATE())
WHEN MATCHED 
THEN UPDATE 
SET 
    DST.'+@ContentColumnNamesDST[i]' = SRC.'+@ContentColumnNamesSRC[i] +'
    ,DST.'+@ContentColumnNamesDST[i]' = SRC.'+@ContentColumnNamesSRC[i] +'
    ,DST.'+@ContentColumnNamesDST[i]' = SRC.'+@ContentColumnNamesSRC[i] +'
    ,DST.'+@ContentColumnNamesDST[i]' = SRC.'+@ContentColumnNamesSRC[i] +'
    ,DST.[ETLDate] = GETDATE()
 ;

 COMMIT'

EXEC (@StageSQLCore)

it looks like you should simply be repeating the dynamic SQL generation from the first part (where you populated @ContentColumnNamesSRC), but with the dst.blah = src.blah, no? — Marc Gravell
– Marc Gravell, Commented Sep 27, 2016 at 9:25
Thank you for your comment. I thought about that, but I can't seem to figure out how to line them up with eachother, because each columnName comes from two seperate tables. Even if they are identical, I then need to get the columnname out of the variable twice, once with DST. and again with SRC. I haven't been able to figure that out. — Tommy
– Tommy, Commented Sep 27, 2016 at 9:37
And you are sure that columnid from 1 to 8 will match with columnid 1 to 8 of other table? — Kannan Kandasamy
– Kannan Kandasamy, Commented Sep 27, 2016 at 9:44
Reasonably sure. In any event, if they don't. It needs to fail. As the staging table then does not contain the same columns as the source tables. So if it does not, it needs to fail. Which is inline with requirements. — Tommy
– Tommy, Commented Sep 27, 2016 at 9:58

Kannan Kandasamy · Accepted Answer · 2016-09-27 10:23:28Z

1

You can generate Merge SQL like this if both the ordinal are matching

DECLARE @MergeSQL NVARCHAR(4000) = NULL

SELECT --*--,
    @MergeSQL = COALESCE(@MergeSQL + ', DST.=', '') + QUOTENAME(bc.column_name) + ' = SRC.' + QUOTENAME(bc.COLUMN_NAME) + char(13)
FROM    
    test.INFORMATION_SCHEMA.COLUMNS tc
    inner join testb.INFORMATION_SCHEMA.COLUMNS bc
    on tc.TABLE_NAME = bc.TABLE_NAME
    and tc.ORDINAL_POSITION = bc.ORDINAL_POSITION
    and tc.TABLE_NAME = 'History'
WHERE 
     tc.ORDINAL_POSITION < 5 -- First 8 columns are ID data, which is what I am after
     and bc.ORDINAL_POSITION < 5

    select @MergeSQL

answered Sep 27, 2016 at 10:23

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tommy Over a year ago

I didn't consider doing it like this. I've modified it to be as the answer I've posted. Not to take away from this answer, as this lead me to the right solution.

Collectives™ on Stack Overflow

Get name from variable using index in T-SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related