Dynamic SQL - Insert a variables via INSERT INTO x SELECT statement

Question

I would like to ask how can I insert a variables into a table using INSERT INTO x SELECT statement via dynamic SQL.

I have following table:

 |-------------------|-----------------|--------------|-----------------|
 |     TableName     |     ColName     |     Value    |  SQL_Statement  |
 |-------------------|-----------------|--------------|-----------------|

I get a content for Value column by this query:

INSERT INTO #ReturnTable(Value) SELECT TreeHolder FROM prm.Schm_Root WHERE ParentTreeHolderId = 'DD040D31-4591-4658-A02E-A6ED00AB64F2';

But I need to fill whole table. Please consider that other values are variables, not SQL queries.

SELECT @TableSchema = TableSchema FROM #TableNames WHERE Id = @Counter;
SELECT @TableName = TableName FROM #TableNames WHERE Id = @Counter;
SELECT @ColName = ColName FROM #TableNames WHERE Id = @Counter;
SET @SQL_Statement = 'SELECT ' + @ColName + ' FROM ' + @TableSchema + '.' + @TableName + ' WHERE ' + @ColName + ' = ' + '''''' + CAST(@GuidArgument AS NVARCHAR(50)) + '''''' + ';';

Now I have this query that fills a table:

SET @SQL_String = N'INSERT INTO #ReturnTable SELECT
''' + @TableName + ''',
''' + @ColName + ''',
''' + @SQL_Statement + ''',
'' + Value + '',
   (SELECT ' +
       @ColName + '
    FROM ' +
       @TableSchema + '.' + @TableName + '
    WHERE ' +
       @ColName + ' = ''' + CAST(@GuidArgument AS NVARCHAR(50)) + '
'')';

EXECUTE sp_executesql @SQL_String
PRINT @SQL_String;

The thing I need is to rewrite this query from INSERT INTO ? VALUE to INSERT INTO ? SELECT format.

This is a follow up to your previous question: stackoverflow.com/questions/51888923/… — Kamil Gosciminski
– Kamil Gosciminski, Commented Aug 17, 2018 at 8:26
I'm mentioning it for others for reference. Though, for me your question is unclear. You need an INSERT with SELECT not with VALUES, but I don't see insert with values in here. Also, you didn't specify how you declare your variables. — Kamil Gosciminski
– Kamil Gosciminski, Commented Aug 17, 2018 at 8:32
Please please please quote your Strings and parametrise your variables! SQL injection is not your friend! Why QUOTENAME is important — Thom A
– Thom A ♦, Commented Aug 17, 2018 at 8:51
@Chyu manage it when you write the SQL, not later. There is never a good reason for poor Dynamic SQL practices — Thom A
– Thom A ♦, Commented Aug 17, 2018 at 8:54

D-Shih · Accepted Answer · 2018-08-17 11:16:49Z

1

If I understand correctly, you want to insert SQL execute syntax string and it results in ReturnTable table.

I would let subquery SQL execute syntax save in a variable. because the will be more clear what you need to do.

Declare a new variable @SQL_excuteStatement variable to save your execute syntax.

the @SQL_Statement to carry the original SQL string.

set @SQL_Statement =  'SELECT ' + @ColName + 
     ' FROM ' + @TableSchema + '.' + @TableName + 
     ' WHERE ' + @ColName + ' = '+'''''' +  CAST(@GuidArgument AS NVARCHAR(50)) + '''''';

and use select ... from table instead of subquery in select

There is a sample for you.

DECLARE @SQL_String NVARCHAR(MAX)
DECLARE @TableSchema NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(MAX)
DECLARE @ColName NVARCHAR(MAX)
DECLARE @Counter int = 1
DECLARE @SQL_Statement NVARCHAR(MAX)
DECLARE @GuidArgument INT = 1

CREATE TABLE TableNames(
     ID INT,
     TableSchema NVARCHAR(100),
     TableName NVARCHAR(100),
     ColName  NVARCHAR(100)
);


CREATE TABLE ReturnTable(
     TableName NVARCHAR(100),
     ColName  NVARCHAR(100),
     SQL_Statement NVARCHAR(max),
     value nvarchar(max)
);


INSERT INTO TableNames VALUES (1,'dbo','T','val');

CREATE TABLE T(val INT);
INSERT INTO T VALUES (1)

SELECT @TableSchema = TableSchema FROM TableNames WHERE Id = @Counter;
SELECT @TableName = TableName FROM TableNames WHERE Id = @Counter;
SELECT @ColName = ColName FROM TableNames WHERE Id = @Counter;


set @SQL_Statement =  'SELECT ' + @ColName + 
 ' FROM ' + @TableSchema + '.' + @TableName + 
 ' WHERE ' + @ColName + ' = '+ '''''' +  CAST(@GuidArgument AS NVARCHAR(50)) + '''''';


SET @SQL_String =  N'INSERT INTO ReturnTable (TableName,ColName,SQL_Statement,value) 
SELECT '''+ @TableName + ''','''+ @ColName + ''','''+ @SQL_Statement + '''' + ',' + QUOTENAME(@ColName) +
' FROM ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + '' +
' WHERE ' + @ColName + ' = ''' +  CAST(@GuidArgument AS NVARCHAR(50)) + '''';

EXECUTE sp_executesql @SQL_String

sqlfiddle

Note

I would suggest you use clear column after insert into

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

edited Aug 17, 2018 at 11:16

answered Aug 17, 2018 at 9:24

D-Shih

46.5k6 gold badges38 silver badges56 bronze badges

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

5 Comments

D-Shih Over a year ago

@Chyu Wait a moment I got the problem I am writing a solution for it.

D-Shih Over a year ago

@Chyu you can try this. select from table instead of subquery in select dbfiddle.uk/…

Kotkoroid Over a year ago

You are the real MVP! Thanks a lot! <3

D-Shih Over a year ago

@Chyu No problem glad to help :)

Kotkoroid Over a year ago

Can you please update your topic with a solution you have made? I've marked it as an answer.

Collectives™ on Stack Overflow

Dynamic SQL - Insert a variables via INSERT INTO x SELECT statement

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related