Moving results to Temp table
-
Hi everyone
Can anyone offer a quick and easy way to move the result of a select to a temp table ?
This is my select.
select * from XX
where tff_id ='108'
and tap_from_dt >'2006-07-01 00:00:00:000'
Basically want the result from this select moved to a temp table ?
cheers
-
Insert into #tempTable (col1, col2, id)
Select col1, col2, id FROM XX
where tff_id ='108'
and tap_from_dt >'2006-07-01 00:00:00:000'
-
DECLARE #YourTempTable (colA varchar(20), colB varchar(20), colC varchar(20))
INSERT INTO #YourTempTable
select colA, colB, colC from XX
where tff_id ='108'
and tap_from_dt >'2006-07-01 00:00:00:000'
There is no "i" in team, but idiot has two. -
DECLARE #YourTempTable
.Never seen that code before
. -
Sorry, that's the advanced, SQL Server 2008 syntax.
There is no "i" in team, but idiot has two. -
Great to know.
Here's the 2000 / 2005 table variable way
DECLARE @YourTempTable table (colA varchar(20), colB varchar(20), colC varchar(20))
INSERT INTO @YourTempTable
select colA, colB, colC from XX
where tff_id ='108'
and tap_from_dt >'2006-07-01 00:00:00:000'
-
If you want all columns of your query into a temp table
select colA, colB, colC
INTO #Temp
FROM XX
WHERE tff_id = '108' and tap_from_dt > '07/01/2006'
-
Yes, but doesn't SELECT...INTO lock the database for the duration of the select? That's why I always create the table first, then insert into it.
There is no "i" in team, but idiot has two. -
>>doesn't SELECT...INTO lock the database
Database lock ?
Creation of a table requires locks on the system tables that contain the table/column definitions. Pages in tempdb's sysobjects and syscolumns may be locked during a SELECT INTO operation. The impact of this on any given system depends on many variables.
-
Yup... that's why I hate taking the risk... just create the temp table or temp variable and us the insert select statements we provided... no possible headaches this way
. -
I don't think there is any difference in using INSERT INTO #Temp ( ) SELECT
and SELECT ... INTO #Temp FROM
SELECT ....INTO created the table by itself instead of the user create the table.
-
tempdb.sbo.sysobjects and tempdb.dbo.syscolumns will be locked for the whole time of the insert and no other temp objects will be able to be created. If a high transaction environement, that can quickly escalate to big problems.
-
Another problem with using Select Into is that the column definitions are based on the result set and not the underlying structure. So for example, if you have a varchar(500) column with only 2 characters in each row, the new row will be created with a much smaller lengh. The real problem is that if all values are null, it will try to create the field with a size of 0 causing the query to fail.
A workaround for this is to do the Select Into using criteria that returns 0 rows. This will cause it to look at the underlying table structures to build the new table.
select colA, colB, colC
INTO #Temp
FROM XX
WHERE 1 = 2
INSERT INTO #Temp (colA, colB, colC )
select colA, colB, colC
FROM XX
WHERE tff_id = '108' and tap_from_dt > '07/01/2006'
Another advantage to doing it this way (Where 1 = 2) is that the creation of the new table is virtually instaneous as SQL Server does not wait for the recordset to be returned before completing the table creation.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply