Need help accessing and loading a temp table
-
I'm trying to dynamically create a temp table, basing the column names on another table. It appears to be working (???) but I can't select or insert any data into the table that was just built?
Here is a test of what I'm trying to do.
IF OBJECT_ID('tempdb..#source_table', 'U') IS NOT NULL DROP TABLE #source_table;
IF OBJECT_ID('tempdb..#new_table', 'U') IS NOT NULL DROP TABLE #new_table;
DECLARE @sql NVARCHAR(MAX);
DECLARE @cols NVARCHAR(MAX);
DECLARE @insert_sql NVARCHAR(MAX);
/* create test table */
CREATE TABLE #source_table (
id INT,
column_name VARCHAR(100),
value INT
);
INSERT INTO #source_table (id, column_name, value) VALUES
(1, 'ColumnA', 10),
(2, 'ColumnB', 20),
(3, 'ColumnA', 30),
(4, 'ColumnC', 40),
(5, 'ColumnB', 50);
/* create a list of column names */
SELECT @cols = STRING_AGG(QUOTENAME(column_name), ' int , ')
FROM (SELECT DISTINCT column_name FROM #source_table) AS cols;
/* create sql to create table */
SET @sql = 'CREATE TABLE #new_table (' + @cols + ' int);';
/* create table */
EXEC sp_executesql @sql;
Insert Into #new_table ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) values (1,2,3,4);
Select * From #new_table;Can anyone give me some ideas?
Thank you,m
-
I think that the temp table is scoped to the subprocess spawned by the EXEC statement.
If you concat the INSERT and SELECT statements to your @sql variable, with semicolon delimiters, it should all work.
-
This works using a global temporary table:
IF OBJECT_ID('tempdb..#source_table', 'U') IS NOT NULL DROP TABLE #source_table;
IF OBJECT_ID('tempdb..##new_table', 'U') IS NOT NULL DROP TABLE ##new_table;
DECLARE @sql NVARCHAR(MAX);
DECLARE @cols NVARCHAR(MAX);
DECLARE @insert_sql NVARCHAR(MAX);
/* create test table */
CREATE TABLE #source_table (
id INT,
column_name VARCHAR(100),
value INT
);
INSERT INTO #source_table (id, column_name, value) VALUES
(1, 'ColumnA', 10),
(2, 'ColumnB', 20),
(3, 'ColumnA', 30),
(4, 'ColumnC', 40),
(5, 'ColumnB', 50);
/* create a list of column names */
SELECT @cols = STRING_AGG(QUOTENAME(column_name), ' int , ')
FROM (SELECT DISTINCT column_name FROM #source_table) AS cols;
/* create sql to create table */
SET @sql = 'CREATE TABLE ##new_table (' + @cols + ' int);';
/* create table */
EXEC sp_executesql @sql;
Insert Into ##new_table ([ColumnA], [ColumnB], [ColumnC]) values (1,2,3);
Select * From ##new_table;Or this works where the temporary table is accessed from within the scope of sp_executesql
IF OBJECT_ID('tempdb..#source_table', 'U') IS NOT NULL DROP TABLE #source_table;
DECLARE @sql NVARCHAR(MAX);
DECLARE @cols NVARCHAR(MAX);
DECLARE @insert_sql NVARCHAR(MAX);
/* create test table */
CREATE TABLE #source_table (
id INT,
column_name VARCHAR(100),
value INT
);
INSERT INTO #source_table (id, column_name, value) VALUES
(1, 'ColumnA', 10),
(2, 'ColumnB', 20),
(3, 'ColumnA', 30),
(4, 'ColumnC', 40),
(5, 'ColumnB', 50);
/* create a list of column names */
SELECT @cols = STRING_AGG(QUOTENAME(column_name), ' int , ')
FROM (SELECT DISTINCT column_name FROM #source_table) AS cols;
PRINT 'CREATE TABLE #new_table (' + @cols + ' int);'
/* create sql to create table */
SET @sql = 'CREATE TABLE #new_table (' + @cols + ' int);
Insert Into #new_table ([ColumnA], [ColumnB], [ColumnC]) values (1,2,3);
Select * From #new_table;'
/* create table */
EXEC sp_executesql @sql; -
if a global temp table works, that would be fantastic. When, how does a global temp table get dropped.
P.S. I'm thinking of putting this code into a report, which can be run on demand.
-
Be careful with global temp tables.
Multiple SPIDs could end up tripping over each other, as they can all see the global temp table.
- Ken at work wrote:
if a global temp table works, that would be fantastic. When, how does a global temp table get dropped.
P.S. I'm thinking of putting this code into a report, which can be run on demand.
Global temporary tables are stored in tempdb, just like local temporary tables. However, unlike local temp tables, they are accessible from any session. A global temp table remains available until it is explicitly dropped or the SQL Server instance is restarted.
-
How could I incorporate this into a report?
Can I add this to the bottom of the query?
IF OBJECT_ID('tempdb..#source_table', 'U') IS NOT NULL DROP TABLE #source_table;
IF OBJECT_ID('tempdb..##new_table', 'U') IS NOT NULL DROP TABLE ##new_table; - Ken at work wrote:
How could I incorporate this into a report?
Can I add this to the bottom of the query?
IF OBJECT_ID('tempdb..#source_table', 'U') IS NOT NULL DROP TABLE #source_table;
IF OBJECT_ID('tempdb..##new_table', 'U') IS NOT NULL DROP TABLE ##new_table;
In SQL Server 2016 you can write
DROP TABLE IF EXIST #source_table, ##new_table;
But #source_table won't exist if you created with sp_executesql and try to drop it outside the scope of sp_executesql
-
WOW!!!
Thank you, I think this give me a lot to work on. Now my next task is to dynamically load the table with the data without having to manually build this:
INSERT INTO ##new_table ([ColumnA], [ColumnB], [ColumnC])
SELECT
MAX(CASE WHEN column_name = 'ColumnA' THEN value END),
MAX(CASE WHEN column_name = 'ColumnB' THEN value END),
MAX(CASE WHEN column_name = 'ColumnC' THEN value END)
FROM source_table;What this is really supposed to do is making a dynamic pivot table.
- Ken at work wrote:
What this is really supposed to do is making a dynamic pivot table.
This might be of interest:
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
-
Try something like this
if object_id('tempdb..#temp') is not null
drop table #temp;
GO
create table #temp
(
date datetime,
category varchar(3),
amount money
);
insert into #temp(date, category, amount) values ('1/1/2012', 'ABC', 1000.00);
insert into #temp(date, category, amount) values ('2/1/2012', 'DEF', 500.00);
insert into #temp(date, category, amount) values ('2/1/2012', 'GHI', 800.00);
insert into #temp(date, category, amount) values ('2/10/2012', 'DEF', 700.00);
insert into #temp(date, category, amount) values ('3/1/2012', 'ABC', 1100.00);
--select * from #temp;
--=======================================================================
DECLARE
@pvtCols NVARCHAR(MAX)
, @Query NVARCHAR(MAX)
, @Debug BIT = 0; /* Chnage this to 1 to print the SQL for debugging */
SET @pvtCols = STUFF( (SELECT DISTINCT ',' + QUOTENAME(c.category)
FROM #temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '');
----------------------------
-- Using CROSS-TAB Report --
----------------------------
SET @Query = 'SELECT t.date ';
SELECT @Query += '
, MAX(CASE WHEN t.category = ''' + c.category + ''' THEN t.amount ELSE NULL END) AS ' + QUOTENAME(c.category)
FROM #temp AS c
GROUP BY c.category
ORDER BY c.category;
SET @Query += '
FROM #temp as t
GROUP BY date;';
IF (@Debug = 1) PRINT (@Query);
EXEC sp_executesql @Query;
drop table #temp;
GO -
Great, I'll give that a read. Fingers crossed.
Thank you for all of your help everyone,
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply