CODEWITHSUNDEEP
sql-server
webdad3
webdad3

Reputation: 9080

SQL Generation of Insert statements

I have the following script that I would like to add to a stored procedure. Right now, when I run this the SELECT statement prints out all the INSERT statements. I would like to run these insert statements automatically from within my stored procedure.

I've tried various ways by executing dynamic sql but I haven't been successful mostly due to the system tables my script references. Is there anyway to generate proper INSERT statements and run them at the same time?

I'm using SQL Server for this.

MY CODE:

DECLARE @tablename as varchar(255) = 'tblsmoker'
DECLARE @SQL VARCHAR(MAX)
SET @SQL = (SELECT 'INSERT INTO tblCheck (ColumnName,ColumnValue,SID,SName,RID,RName)VALUES (''' + QUOTENAME(c.name) + ''',NULL,0,NULL,0,NULL);'
            FROM sys.columns c
            WHERE c.object_id = OBJECT_ID(@tablename))
print @SQL
EXEC(@SQL)

This code is giving me:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 0

Views: 82

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

STUFF the insert statements into another variable, and then finally use dynamic sql to execute the variable.

Be sure to separate the INSERT statements inside the variable with a semi-colon.

Try this slight modification of your dynamic sql attempt:

DECLARE @tablename as varchar(255) = 'tblsmoker'
DECLARE @SQL VARCHAR(MAX) = '';
SELECT @SQL = @SQL + 'INSERT INTO tblCheck (ColumnName,ColumnValue,SID,SName,RID,RName)VALUES (''' + QUOTENAME(c.name) + ''',NULL,0,NULL,0,NULL);'
            FROM sys.columns c
            WHERE c.object_id = OBJECT_ID(@tablename)
print @SQL
EXEC(@SQL)

Upvotes: 2

Related Questions