Build a dynamic list of INSERT statement values

user_1862755 0
Viewed 22269

I am writing a stored procedure to create a set of DELETE statements for an administrator to run against a database

As part of the "rollback" solution, I would like, for every row I am going to delete, to also create, separately, a corresponding INSERT statement so that should the person running the script wish to undo, they can simply run the insert statements against the database

My question is, if I have a table with say 8 columns (Col1..Col8), how can I extract the values in a comma separated list for all columns so that I end up with

INSERT INTO Table VALUES (Col1value, Col2value, ...Col8value)
sql-server-2008 sql
5 Answers

This will will work.. pass table name, column name on which you want to apply condition and pass condition. Like exec sp_get_columns 'tbl_name', 'column_name', '100'

CREATE or ALTER PROCEDURE SP_GET_COLUMNS
@TABLE_NAME varchar(256)
,@COND_COL varchar(256)
,@COND varchar(256)
AS
BEGIN            
SELECT IDENTITY(int, 1, 1) seq_no, COLUMN_NAME into #temp FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME
Declare @Data AS Nvarchar(MAX) 
SELECT @Data = COALESCE(@Data + ','','',', '') + COLUMN_NAME FROM  #temp
DECLARE @query varchar(1000) = 'SELECT CONCAT(' + @Data + ') from ' + @TABLE_NAME + ' where ' + @COND_COL + ' = ''' + @COND + ''''
CREATE TABLE #temp2 (val VARCHAR(8000))
INSERT INTO #temp2 exec(@query)
SELECT REPLACE(REPLACE(CONCAT('INSERT INTO ',@TABLE_NAME,' VALUES(''', REPLACE(val,',',''',''') ,''')'),',''''',',NULL'),','''',',',NULL,') as Query FROM #temp2
drop Table #temp,#temp2
END
user_162899600
Related