How to put the EXEC SQL) (@ the result set (dynamic) column is inserted into a temporary table? Mainly column is dynamic, there is no way to establish good table structure first, please help, and see what way, thank you!

CodePudding user response:

The number of columns indefinite words need to use the select * it INTO a temporary table, try the global temporary table

 DECLARE @ SQL NVARCHAR (200)='select the as 1 a, 2 b as' 

EXEC (' select * into # # temp from t '(' + SQL + @')) 

SELECT * from # # temp 

DROP TABLE # # temp 

CodePudding user response:

Good thinking the moderators of February,
It is important to note, however, if it is in the program calls, concurrent invocations will have problems,
Because global temporary tables are common, if concurrent calls with name conflicts will appear,
So the global temporary table name best is random,

CodePudding user response:

Directly SELECT INTO rode the exec (@ SQL) and then from rode the temporary table inserted INTO the official table, finally delete rode a temporary table,

CodePudding user response:

I also encountered this problem, various methods are tried, can't, how will the EXEC SQL) (@ the result set (dynamic) column is inserted into the list, and then to deal with these data?

refer to the original poster jbhou response:

how EXEC SQL) (@ the result set (dynamic) column is inserted into a temporary table? Mainly column is dynamic, there is no way to establish good table structure first, please help, and see what way, thank you!

CodePudding user response:

The exec (' select * into # # test from the test where 1 & lt;> 1 ')
Insert into # # test
The exec (' select * from the test ')

Update # # test
The set col1=2

Select * from # # test