hoseam (4/23/2014)
Now that's an easy one cause I created a table for you, so you are working with the data you know. say now the user input tblDef_RETURNS as table, and you have to do the same to this table. Your code wouldn't work because you don't know anything about this table.The solution has to cater for any table that the user might pass in. as I indicated that the input parameters are @Product_Id, @NewProduct_Id and @TableName
DECLARE @Product_Id VARCHAR(100), @Fund_Id VARCHAR(100), @NewProduct_Id VARCHAR(100), @NewFund_Id VARCHAR(100), @TableName VARCHAR(100)
DECLARE @SQLscript VARCHAR(2000)
SELECT @TableName = 'permTable', @Product_Id = 'AGP1', @Fund_Id = 'E016', @NewProduct_Id = 'PIC1', @NewFund_Id = 'D016'
SELECT
@SQLscript =
'INSERT INTO [' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
' (' + STUFF(input.ColumnList,1,2,'') + ')' + CHAR(10) +
'SELECT ' + CHAR(10) +
' ' + STUFF([source].ColumnList,1,2,'') + CHAR(10) +
'FROM [' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
'WHERE product_id = ' + QUOTENAME(@Product_Id,'''') + ' AND fund_id = ' + QUOTENAME(@Fund_Id,'''') + ''
FROM information_schema.tables t
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' + CASE
WHEN column_name = 'product_id' THEN ' = ' + QUOTENAME(@NewProduct_Id,'''')
WHEN column_name = 'fund_id' THEN ' = ' + QUOTENAME(@NewFund_Id,'''')
ELSE '' END AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) [source] (ColumnList)
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) input (ColumnList)
WHERE t.table_name = @TableName
PRINT @SQLscript
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden