June 27, 2012 at 10:22 am
As part of a fairly involved project, I need to create a temporary table that has columns named after the values within a column of another table.
I've tried using dynamic SQL but it goes out of scope as it executes, so I can't see the resultant table.
I have provided a simplified example of the problem below.
The aim of this code is to provide a temporary table (or a table that is unique within the scope of this query) that has three columns - Values_1, Values_2 and Values_3 (because of the values of 'Column_Name' within #Names). Then I will store the corresponding values from the table '#Values' into that.
In 'real life' the table '#Values' can have any number of columns and the number required from that is also variable, hence the fun I'm having.
CREATE TABLE #Names(
IDINT IDENTITY PRIMARY KEY,
Column_NameVarChar(100),
ValueINTDEFAULT NULL
);
GO
CREATE TABLE #Values(
IDINT IDENTITY PRIMARY KEY,
Values_1INT,
Values_2INT,
Values_3INT,
Values_4INT
);
GO
INSERT INTO #Names(Column_Name)
VALUES('Values_1'),
('Values_2'),
('Values_3');
INSERT INTO #Values(Values_1, Values_2, Values_3)
VALUES(10, 20, 30);
SELECT * FROM #Names;
--SELECT * FROM #Values;
DROP TABLE #Names;
DROP TABLE #Values;
June 27, 2012 at 10:47 am
select .. into doesn't work?
June 28, 2012 at 2:12 am
Steve Jones - SSC Editor (6/27/2012)
select .. into doesn't work?
No, because I'm using dynamic SQL it goes out of scope:
declare @sql varchar(max);
set @sql = 'SELECT ''1'' AS [A],''2'' AS INTO #TEMP33;'
exec(@sql);
select * from #temp33
Results in :
(1 row(s) affected)
Msg 208, Level 16, State 0, Line 6
Invalid object name '#temp33'
The problem exists because (in the example I posted at the start) the table '#Names' can have any number of values for 'Column_Name' across the customers that we have, and I'm trying to create a temporary table with columns based upon the variable number of names.
Now, I could use '##' to make it a global temporary table, but then I'd have problems if two users run the code:
Msg 2714, Level 16, State 6, Line 1
There is already an object named '##TEMP33' in the database.
The other option is to create a global temporary table with a GUID or suchlike within the name and just make sure I drop the table.
This situation has arisen because our software can alter a certain number of tables (and associated views). We keep information on all aspects of property (houses, apartments, garages, streets, gardens, sheds and so on), but because there are some things that can be unique to certain customers we have the ability within the system to add features as dictated by the users, through the GUI. These 'special' (for wont of a better word) tables are then altered by the interface to store these new columns. The software quieries these columns with 'apparent' ease (I know what goes on behind the scenes!) but this is the first time we have had a requirement to do such a thing through SQL, to this degree.
I'll probably go with a global temp table and a GUID as part of the name. I may also make the date part of the name too, so I can sweep for any that may be left behind on a regular basis.
Thanks for taking the time Steve. At the least it made me think in a slightly different direction, which is appreciated.
June 28, 2012 at 2:30 pm
You are welcome, and sorry I didn't test that. had a few other things going and just dropped a note. I was more thinking since you have the table, use a select .. into from the table, not worrying about creating your own.
I think your global table might be the best idea. I was trying to think is some type of table var might work as well, but that fails also.
July 2, 2012 at 11:49 am
BrainDonor (6/28/2012)
Steve Jones - SSC Editor (6/27/2012)
select .. into doesn't work?No, because I'm using dynamic SQL it goes out of scope:
declare @sql varchar(max);set @sql = 'SELECT ''1'' AS [A],''2'' AS INTO #TEMP33;'
exec(@sql);
select * from #temp33
Results in :
(1 row(s) affected)Msg 208, Level 16, State 0, Line 6
Invalid object name '#temp33'
The problem exists because (in the example I posted at the start) the table '#Names' can have any number of values for 'Column_Name' across the customers that we have, and I'm trying to create a temporary table with columns based upon the variable number of names.
Now, I could use '##' to make it a global temporary table, but then I'd have problems if two users run the code:
Msg 2714, Level 16, State 6, Line 1There is already an object named '##TEMP33' in the database.
The other option is to create a global temporary table with a GUID or suchlike within the name and just make sure I drop the table.
This situation has arisen because our software can alter a certain number of tables (and associated views). We keep information on all aspects of property (houses, apartments, garages, streets, gardens, sheds and so on), but because there are some things that can be unique to certain customers we have the ability within the system to add features as dictated by the users, through the GUI. These 'special' (for wont of a better word) tables are then altered by the interface to store these new columns. The software quieries these columns with 'apparent' ease (I know what goes on behind the scenes!) but this is the first time we have had a requirement to do such a thing through SQL, to this degree.
I'll probably go with a global temp table and a GUID as part of the name. I may also make the date part of the name too, so I can sweep for any that may be left behind on a regular basis.
Thanks for taking the time Steve. At the least it made me think in a slightly different direction, which is appreciated.
Maybe you can create the table first. Something like this:
If object_id('tempdb..#Location') is not null drop table #Location
Create table #Location(
pklocation int, fkid int, fktype char(1), address1 varchar(50), address2 varchar(50),
city Varchar(50),state varchar(2), zip varchar(9), phone varchar(10), fax varchar(10),
primarylocation tinyint, website char(1), userlup varchar(20), datelup datetime,
note varchar(255), fkAddresstype int)
set @sqlquery =
'insert into #Location
select
pkLocation= pkAddress
,fkID= fkGroupRatingID
,fkType= GroupRatingType
,Address1= Address1
,Address2= Address2
,City= City
,[State]= [State]
,Zip= Zip
,Phone= Phone
,Fax= Fax
,PrimaryLocation= case when GroupRatingMailingAddress = 1 then 1 else 0 end
,WebSite= ''''
,UserLup= UserLup
,DateLup= DateLup
,Note= MemoText
,fkAddressType= 0
from ' + @server + '.Accounts.dbo.Address
where PrimaryAddress <> 2
and fkGroupRatingID = ' + convert(varchar,@pkEmployer) +
' and GroupRatingType = ''e'''
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply