help on execute table variable into one table

Question

help on execute table variable into one table

DBA.k

Ten Centuries

Points: 1113
More actions
June 17, 2021 at 2:31 pm

#3897484

I have created table variable as below , just want to that result into table as last line is not working please help on that,
Select * into table_temp from execute (@query) how to make sure this query like select * into table_2 from table_1

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ', ' + QUOTENAME(Finding+'_'+ltrim(rtrim(Units)))
from Vitals2Y
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--Select @cols
set @query = 'SELECT PatientID,
LastName,
FirstName,
MRN,
DOB,
SSN,
SexDE,
EncounterDTTM,
PerformedDTTM,
ProviderCode,
ProviderLastName,
ProviderFirstName,
ProviderNPI, ' + @cols + '
FROM
(
select PatientID,
LastName,
FirstName,
MRN,
DOB,
SSN,
SexDE,
EncounterDTTM,
PerformedDTTM,
ProviderCode,
ProviderLastName,
ProviderFirstName,
ProviderNPI, Finding+''_''+ltrim(rtrim(Units)) as Finding, Value
from Vitals2Y --where PatientID=''1226738''
) x
pivot
(
min(Value)
for Finding in (' + @cols + ')
) p
ORDER BY PatientID
'
execute (@query)
Select * into table_temp from execute (@query)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

Change your dynamic SELECT into a dynamic INSERT:

/* instead of: */
set @query = 'SELECT PatientID, LastName, FirstName, .....'

/* use: */
set @query = '
  INSERT temp_table(PatientID, LastName,FirstName, ......
  SELECT PatientID, LastName, FirstName, ..... '

Eddie Wuerch
MCM: SQL

DBA.k Ten Centuries Points: 1113 More actions · Answer 2

Thank you for you reply, but columns will dynamic from pivot.. So I would like to create table like select * into table_bak from table