Construct insert query based on columns from another table variable

Question

apologies if this has been asked but my actual question is actually terribly difficult to query using search.

I have a table variable that I've created which constains the IDs, column names and table names of other tables such that an example row consists of

ID, Column, Table

2, AssetName, dbo.Asset

I now wish to insert into another table variable the results of a query which is composed of that row, for eg:

insert into @table2 .. 'select AssetName from dbo.Asset' [the results, specifically the AssetName column from the Asset table - perhaps 10 rows worth of data]

Please let me know how best to approach this.

HoneyBadger · Accepted Answer · 2016-04-25 14:11:29Z

1

You'll have to do that dynamically. For example, something like (untested):

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = N'INSERT INTO @Table2 SELECT ' + QUOTENAME(Column) + N' FROM ' + QUOTENAME(Table)
FROM   @Table1

EXEC sp_executesql @SQL

I'm not entirely sure if you don't need a global table variable for that (@@Table2).

edited Apr 25, 2016 at 14:11

answered Apr 25, 2016 at 13:48

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

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

2 Comments

Sean Lange Over a year ago

Or use a temp table that is declared prior to the dynamic sql.

notsoobvious Over a year ago

I would note that you had to declare the table variable within the dynamic sql code for it to be recognised but regardless, in spite of my ignorance in this area this clearly seems like the way.

Collectives™ on Stack Overflow

Construct insert query based on columns from another table variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related