May 30, 2008 at 7:15 am
Hi All,
I have a requirement where in i need to insert data from 3 different tables in to a temporary table.
example:
if @var = a
select * into #temp from dbo.A
else if @var = b
select * into #temp from dbo.B
else
select * into #temp from dbo.C
however, while the SP is compiled, it throws an error saying that the object #temp already exists in the database on the second and third condition.
Can anybody let me know how to achieve the output?
Thanks in advance
May 30, 2008 at 7:25 am
INSERT INTO #temp
SELECT * FROM dbo.A WHERE @var = 'A'
UNION
SELECT * FROM dbo.B WHERE @var = 'B'
UNION
SELECT * FROM dbo.C WHERE @var NOT IN ('A','B')
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 30, 2008 at 7:43 am
but the problem is the table might have lot of columns and i want to create the table on the fly.
depending on the parameter passed, the temp table need to be populated from main table.
May 30, 2008 at 7:45 am
Is the structure of the temporary table the same in each case? If it is explicitly define the temp table and then do Insert Into instead of select into.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 30, 2008 at 8:49 am
when you do a (select * into #table), doesn't it lock the tempdb the entire time of the select staments because the tempdb is locked during the creation of a temp table?
Better to 'create #table' then 'insert table select *'
??
May 30, 2008 at 10:17 am
bcronce (5/30/2008)
when you do a (select * into #table), doesn't it lock the tempdb the entire time of the select staments because the tempdb is locked during the creation of a temp table?Better to 'create #table' then 'insert table select *'
??
That was a bug in 6.5 - I don't believe that's been true since 7.0. On the up side - SELECT INTO tends to be a LOT faster than a traditional INSERT statement.
I've used SELECT...INTO a fair amount. I have yet to see it cause issues with tempdb.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 30, 2008 at 7:12 pm
Matt Miller (5/30/2008)
bcronce (5/30/2008)
when you do a (select * into #table), doesn't it lock the tempdb the entire time of the select staments because the tempdb is locked during the creation of a temp table?Better to 'create #table' then 'insert table select *'
??
That was a bug in 6.5 - I don't believe that's been true since 7.0. On the up side - SELECT INTO tends to be a LOT faster than a traditional INSERT statement.
I've used SELECT...INTO a fair amount. I have yet to see it cause issues with tempdb.
Correct... it was a bug in 6.5 and has been long since repaired.
Others will tell you that, in SQL Server 2000, it will place a lock on the SysObjects table of TempDB while it is being built preventing any new objects from being built until the SELECT/INTO completes... I believe that would also be correct so you do have to be a bit careful with how long the SELECT/INTO takes.
In SQL Server 2005, those problems don't appear to exist.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply