May 6, 2010 at 9:08 am
I'm looking for some answers as to why I cannot do this:
IF 1 <> 0
BEGIN
Select * INTO #myTemp1 From TableA
END
ELSE
BEGIN
Select * INTO #myTemp1 From TableB
END
I get this error: There is already an object named '#myTemp1' in the database.[/color]
So then I tried this:
@sql NVarChar(MAX) = ''
IF 1 <> 0
BEGIN
Select @sql = 'Select * INTO #myTemp1 From TableA'
END
ELSE
BEGIN
Select @sql = 'Select * INTO #myTemp1 From TableB'
END
EXEC(@sql)
This runs but when I try to query #myTemp1 I get nothing back.
Basically what I am trying to accomplish is to take a copy of a row in a table (some unknown columns), update a few known columns and then insert it back into the real table.
May 6, 2010 at 9:32 am
Why copy it? Is the logic that complex?
Couldn't you copy the row back into the table directly, changing the columns?
insert TableA
select ColA+1
, ColB * 2
from TableA
where ColA = 4
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 6, 2010 at 9:33 am
I dont see a drop table section to drop the temp table on completion so if you have run this in testing and then you are trying to run it again then the table already exists in tempdb.
I would try running a drop table on your temp table and then try to run it again.
you can also avoid using you tempdb by using a table variable.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 6, 2010 at 9:36 am
Steve Jones - Editor (5/6/2010)
Why copy it? Is the logic that complex?Couldn't you copy the row back into the table directly, changing the columns?
insert TableA
select ColA+1
, ColB * 2
from TableA
where ColA = 4
Yes, its that complex. There is data versioning built into the software we are using so I have to replicate the correct data and update the new data. Otherwise the newest version will show all the other columns as null.
May 6, 2010 at 9:39 am
Dan.Humphries (5/6/2010)
I dont see a drop table section to drop the temp table on completion so if you have run this in testing and then you are trying to run it again then the table already exists in tempdb.I would try running a drop table on your temp table and then try to run it again.
you can also avoid using you tempdb by using a table variable.
Tried that like this:
IF 1 <> 0
BEGIN
Select * INTO #myTemp1 From TableA
Drop Table #myTemp1
END
ELSE
BEGIN
Select * INTO #myTemp1 From TableB
Drop Table #myTemp1
END
Still no workie...It gives me the error when I try to verify the stored procedure. I know logically this will run fine but I cannot commit the changes because it sees this as an error.
May 6, 2010 at 9:44 am
I would try running the dro table seperately. If it is truly not in the tempdb then you will get an error stating the table does not exist but If it runs successfully then that temp table was still in memory for some reason. If it got created in some variation of your code then it would not get deleted with yout code becuase the drop proceeds the creation. You might also try usings an IF exists statement so you can drop the table before creation if it is found in the tempdb.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 6, 2010 at 9:47 am
Dan.Humphries (5/6/2010)
I would try running the dro table seperately. If it is truly not in the tempdb then you will get an error stating the table does not exist but If it runs successfully then that temp table was still in memory for some reason. If it got created in some variation of your code then it would not get deleted with yout code becuase the drop proceeds the creation. You might also try usings an IF exists statement so you can drop the table before creation if it is found in the tempdb.
When I change it to this:
IF 1 <> 0
BEGIN
Select * INTO #myTemp1 From TableA
Drop Table #myTemp1
END
It runs without error. But since I need there are 2 different tables that store the data. I have to check 1 if the other does not exist. For example: there is a base table that contains the original data then there is a table with versions of the modified base table data.
The logic is saying: If the version-ed table exists do this else do this. When removing the 2nd Select Into statement it works but then I can't get the data from the alternate table.
May 6, 2010 at 9:52 am
I think you might want to do this instead:
DROP TABLE dbo.mytable
go
CREATE TABLE mytable( col1 int, colb varchar(20))
GO
INSERT MyTable SELECT 1, 'a'
INSERT dbo.mytable SELECT 2, 'b'
GO
DECLARE @tbl TABLE ( cola int, colb varchar(20))
IF 1 <> 0
BEGIN
INSERT @tbl SELECT * FROM dbo.mytable
END
ELSE
begin
INSERT @tbl SELECT * FROM dbo.mytable
END
SELECT * FROM @tbl
That seems to work for me.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 6, 2010 at 10:04 am
Steve Jones - Editor (5/6/2010)
I think you might want to do this instead:
DROP TABLE dbo.mytable
go
CREATE TABLE mytable( col1 int, colb varchar(20))
GO
INSERT MyTable SELECT 1, 'a'
INSERT dbo.mytable SELECT 2, 'b'
GO
DECLARE @tbl TABLE ( cola int, colb varchar(20))
IF 1 <> 0
BEGIN
INSERT @tbl SELECT * FROM dbo.mytable
END
ELSE
begin
INSERT @tbl SELECT * FROM dbo.mytable
END
SELECT * FROM @tbl
That seems to work for me.
The problem is that I am working with an unknown amount of columns. In the version table they can take away or add columns at their leisure.
The only way I could use the above example was if there was a way to create a temp tables structure based off of another tables structure. If anyone knows how to do this I'd be inclined to do it this way instead.
May 6, 2010 at 10:56 am
IF 1 <> 0
BEGIN
Select @sql = 'Select * INTO #myTemp1 From TableA'
END
ELSE
BEGIN
Select @sql = 'Select * INTO #myTemp1 From TableB'
END
EXEC(@sql)
This runs but when I try to query #myTemp1 I get nothing back.
The reason this works but you are unable to query the temp table is that it is created with the context of the EXEC(@sql), and the temp table is dropped when the EXEC statement returns. That is how temp tables work.
May 6, 2010 at 12:41 pm
Perhaps you want to lay out more of what you are doing. If there is an unknown number of columns, how do you apply the logic to update them and add them back? If it's only some columns from the row, then apply the logic to those columns in the table var, and do the insert back to the main table, joining with the original row for other columns.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 13, 2010 at 5:36 pm
I am trying to do a very similar thing with several SELECT INTO 's using the same temp table name, because of the possibility of differing source data structures.
I then want to process the data from the temp table without regard to the source data.
The "already exists" error happens on the second SELECT INTO temp occurrence in the script even though it is under a different IF / ELSE condition. SO the temp table doesn't really already exist at all. It's as if the compiler doesn't care that it's conditional scope is different, it was just reference earlier in the script.
I've struggled with this for years and end up giving up and just using a different temp table name, but then having to DUPLICATE my followup processing for each IF / Else condition and temp table. I've tried using GOTO labels and that doesn't work either.
There much be some easier work around !
May 18, 2010 at 2:09 pm
IF OBJECT_ID(N'tempdb..#myTemp1', N'U') IS NOT NULL
DROP TABLE #myTemp1
IF 1 <> 0
BEGIN
EXEC sp_executesql N'Select * INTO #myTemp1 From TableA'
END
ELSE
BEGIN
EXEC sp_executesql N'Select * INTO #myTemp1 From TableB'
END
May 19, 2010 at 9:22 am
you could do it dynamically, but not sure about the logic issue that Steve brought up.
declare @vs_sql nvarchar(1000)
IF 1 <> 0
BEGIN
set @vs_sql = '
Select ''A'' field1 INTO #myTemp1
select * from #mytemp1'
END
ELSE
BEGIN
set @vs_sql = '
Select ''B'' field1 INTO #myTemp1
select * from #mytemp1'
END
set @vs_sql = @vs_sql + ' Put logic here'
exec sp_Executesql @vs_sql
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply