Insert results of execute(@QueryString) into a table while adding an extra column

Question

I am writing a stored procedure that dynamically creates a SQL string, @SQLQuery. After I create this query, I need to execute the query and insert it into a table in the database while adding another column that specifies a unique ID for this particular insert. (Context: It is possible in this application that multiple groupings of data will be inserted into this table and I need to be able to differientiate between groupings at a later date. )

This issue is similar to this question except I am using Microsoft SQL Server 2008 instead of mysql. I have tried the solution there:

INSERT INTO data_table_name
EXECUTE(@SQLQuery), @SomeID

but MS SQL Server 2008 doesn't like that syntax.

Any ideas on how to do this in SQL Server 2008?

rs. · Accepted Answer · 2012-11-09 19:20:08Z

3

You can store query result in table variable and then read from that with extra column and write to final table

DECLARE @temp table (col1 int, col2 varchar(10), ....)

INSERT INTO @temp
EXEC(@SQLQuery)

INSERT INTO data_table_name
SELECT *, @SomeID FROM @temp

You can also append @SomeID in your dynamic sql string.

Example:

SET @SQLQuery = 'SELECT *,' + @SomeID + ' FROM ' + @tableNameVar

and then do this

INSERT INTO data_table_name
EXECUTE(@SQLQuery)

edited Nov 9, 2012 at 19:20

answered Nov 9, 2012 at 19:14

rs.

27.6k13 gold badges71 silver badges93 bronze badges

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

1 Comment

Brandon Fannin Over a year ago

I had an answer faster than I'm even allowed to accept an answer. Awesome. This works perfectly. Thanks!

Angelo · Accepted Answer · 2012-11-09 19:23:44Z

0

Since you mentioned you are doing this in a Stored Procedure, what I would suggest is for you to:

Execute the @SQLQuery first;
Upon successful execution of the @SQLQuery, insert the @SQLQuery to the table with the unique ID.

i.e.

EXEC sp_executesql @SQLQuery, @Param 

IF @@ERROR <> 0
  BEGIN
    INSERT INTO TableA(Query)
    VALUES(@SQLQuery)
  END

You're better off designing TableA such that the ID will be an Identity so that a unique sequential ID is generated when you insert a record into that table.

answered Nov 9, 2012 at 19:23

Angelo

1,6663 gold badges22 silver badges41 bronze badges

1 Comment

rs. Over a year ago

he wants single id value for set for values returned from query.

Collectives™ on Stack Overflow

Insert results of execute(@QueryString) into a table while adding an extra column

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related