CODEWITHSUNDEEP
t-sqlvariables
user14912884
user14912884

Reputation: 3

TSQL - How to output the content of a variable into a table?

As described in the title, I'm looking to store the output of a variable into a "non-variable table". Here is the code:

declare @fullpath NVARCHAR(200) = '''Data Source=path\to\folder\'
declare @filename NVARCHAR(50) = 'Country.xlsx'
declare @properties NVARCHAR(50) = ';Extended Properties=Excel 12.0'')'
declare @sheetname NVARCHAR(50) = '...[Country$]'
declare @extraction NVARCHAR(500) =
'SELECT * FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'','+ @fullpath+
                                                            @filename+
                                                            @properties+
                                                            @sheetname

EXEC(@extraction)

This will output the content of the excel file in table format (country_id and country_name). Now, how would one store this output table in another table? For instance:

SELECT * INTO #temp FROM @extraction

Regards,

Upvotes: 0

Views: 67

Answers (1)

Thom A
Thom A

Reputation: 95557

Just use INSERT INTO as you normally would. INSERT INTO allows for it to be followed by an EXEC statement:

CREATE TABLE #Temp ({Column List});

INSERT INTO #Temp ({Column List})
EXEC sys.sp_executsql @extraction;

Seems like the OP doesn't know the definition of the files. This, in truth, is a problem, but you can "get around" it. I would therefore SELECT ... INTO inside the dynamic statement. You'll need to use a global temporary table, or a user table for this though. I use a global temporary table for this example:

DECLARE @extraction nvarchar(MAX) =
N'SELECT * INTO ##TEMP FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'',' + @fullpath + @filename + @properties + @sheetname;
EXEC sys.sp_executesql @SQL;
SELECT *
FROM ##TEMP;
--DROP TABLE ##TEMP;

Upvotes: 2

Related Questions