September 20, 2016 at 3:44 pm
I am trying to generate an insert into statement dynamically for a table that may or may not have the exact smae layout every time. I am able to get the column names and Id's for the table using
SELECT
c.name ,
C.column_id
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID('Inkref')
But am not sure how I would go about turning the name column into a comma seperated string (column1,column2,column3, etc... ) for the insert into statement. Is there something similar to being able to SELECT * FROM that can be used for an insert into statement? If the statement only had to be run once, I would just use SELECT * INTO , but I may need to run the Insert statement more than once.
September 20, 2016 at 4:32 pm
there's a neat trick using FOR XML to get a delimited list of columns.
here's a snippet i wrote that i use in a procedure to generate sample insert/update/merge statements.
it assumes i'm working with a similarly named table, like Invoice/Stage_Invoice, but it's a solid model for you to tweak:
DECLARE @Tablename SYSNAME = '[dbo].[CommandLog]'
SELECT DISTINCT UColumns,
t.name,
InsertCommand =
'INSERT INTO ' + quotename( t.name) + '(' + sq.IColumns + ')' + CHAR(13) + CHAR(10)
+ ' SELECT ' + REPLACE(sq.IColumns,'[','MySource.[') + CHAR(13) + CHAR(10)
+ ' FROM [Stage' +t.name + '] MySource' + CHAR(13) + CHAR(10)
+ ' LEFT OUTER JOIN ' + quotename( t.name) + 'MyTarget ' + CHAR(13) + CHAR(10)
+ ' ON MySource.[PK] = MyTarget.[PK] ' + CHAR(13) + CHAR(10)
+ ' AND MySource.[Col2] = MyTarget.[Col2]' + CHAR(13) + CHAR(10)
+ ' WHERE MyTarget.[PK] IS NULL',
UpdateCommand =
'UPDATE MyTarget ' + CHAR(13) + CHAR(10)
+ 'SET ' + sq.Columns + ' ' + CHAR(13) + CHAR(10)
+ '--SELECT * ' + CHAR(13) + CHAR(10)
+ 'FROM [Staging' + t.name + '] MySource ' + CHAR(13) + CHAR(10)
+ 'INNER JOIN ' + quotename(t.name) + ' MyTarget ' + CHAR(13) + CHAR(10)
+ 'ON MySource.[PK] = MyTarget.[PK] ' + CHAR(13) + CHAR(10)
+ 'AND MySource.[Col2] = MyTarget.[Col2]',
MergeCommand =
'MERGE INTO '
+ quotename(object_schema_name(t.OBJECT_ID))
+ '.'
+ quotename(t.name)
+ ' [MyTarget]'
+ 'USING ' + CHAR(13) + CHAR(10)
+ '(' + CHAR(13) + CHAR(10)
+ 'SELECT ' + CHAR(13) + CHAR(10)
+ REPLACE(sq.IColumns,'[','stg.[') + CHAR(13) + CHAR(10)
+ ' FROM [Staging' + t.name + '] stg ' + CHAR(13) + CHAR(10)
+') AS [MySource]' + CHAR(13) + CHAR(10)
+ ' ON MySource.[PK] = MyTarget.[PK]' + CHAR(13) + CHAR(10)
+' WHEN NOT MATCHED ' + CHAR(13) + CHAR(10)
+' THEN INSERT ' + CHAR(13) + CHAR(10)
+'(' + CHAR(13) + CHAR(10)
+ sq.IColumns + CHAR(13) + CHAR(10)
+')' + CHAR(13) + CHAR(10)
+' VALUES ' + CHAR(13) + CHAR(10)
+'(' + CHAR(13) + CHAR(10)
+ REPLACE(sq.IColumns,'[','MySource.[') + CHAR(13) + CHAR(10)
+' )' + CHAR(13) + CHAR(10)
+'WHEN MATCHED ' + CHAR(13) + CHAR(10)
+'THEN UPDATE ' + CHAR(13) + CHAR(10)
+'SET ' + CHAR(13) + CHAR(10)
+ UColumns + CHAR(13) + CHAR(10)
FROM sys.objects t
JOIN (SELECT
OBJECT_ID,
Columns = STUFF((SELECT
',' + CHAR(13) + CHAR(10) + ' MyTarget.' + QUOTENAME(sc.name) + SPACE(30 - LEN(sc.name)) + ' = MySource.' + QUOTENAME(sc.name)
FROM sys.columns sc
WHERE sc.object_id = s.object_id
ORDER BY sc.column_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,''),
IColumns = STUFF((SELECT
',' + QUOTENAME(si.name)
FROM sys.columns si
WHERE si.object_id = s.object_id
ORDER BY si.column_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,''),
UColumns = STUFF((SELECT
',' + 'MyTarget.'+ QUOTENAME(su.name) +' = MySource.'+ QUOTENAME(su.name)
FROM sys.columns su
WHERE su.object_id = s.object_id
ORDER BY su.column_id
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
FROM sys.columns s) sq
ON t.object_id = sq.object_id
WHERE t.object_id = object_id(@Tablename)
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply