September 26, 2014 at 6:48 am
I want to use the same temporary table in a stored procedure where I insert different data in function of my parameter. But I have this error:'There is already an object named '##iscr' in the database'.
Why ? Is it not possible to use a temporary table in this way? I need to have a table that contains a different set of data and use it after.
This is my stored procedure:
CREATE PROCEDURE MyProc
@cod_fondo varchar(4)
AS
BEGIN
if @cod_fondo='2100'
begin
if object_id('tempdb..##iscr','U') is not null drop table ##iscr
select *
into ##iscr
from _Iscritto where idiscritto=1
end
else if @cod_fondo='2103'
begin
if object_id('tempdb..##iscr','U') is not null drop table ##iscr
select *
into ##iscr
from _Iscritto where idiscritto=2
end
END
GO
September 26, 2014 at 6:57 am
either drop the table once at the very top, don't use two different statements.
also, you could modify the query to be a single one with a case statement:
CREATE PROCEDURE MyProc @cod_fondo VARCHAR(4)
AS
BEGIN
IF Object_id('tempdb..##iscr', 'U') IS NOT NULL
DROP TABLE ##iscr
SELECT *
INTO ##iscr
FROM _Iscritto
WHERE idiscritto = CASE
WHEN @cod_fondo = '2100' THEN 1
WHEN @cod_fondo = '2103' THEN 2
END
END
Lowell
September 26, 2014 at 7:15 am
Not relevant to your question, but I would also be interested to know why you are using a global temp table (##) rather than a local one (#)?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 26, 2014 at 8:18 am
My stored procedure is not so simple.I have a lot of values for that parameter and I have to do different things for every case.I can not use the same query for all of them, so I have to use different query for every value of my parameter (the query are very different). How can I use the insert in the same table without error?
September 26, 2014 at 8:22 am
antonela (9/26/2014)
My stored procedure is not so simple.I have a lot of values for that parameter and I have to do different things for every case.I can not use the same query for all of them, so I have to use different query for every value of my parameter (the query are very different). How can I use the insert in the same table without error?
Without seeing your code, it's impossible to tell.
Multiple inserts to the same temp table will not generate an error.
Can you demonstrate your problem with some code? You'll get a better response.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 26, 2014 at 9:49 am
Like this?
IF OBJECT_ID('tempdb..#iscr','U') IS NOT NULL DROP TABLE ##iscr
SELECT * INTO #iscr FROM _Iscritto WHERE 0 = 1
IF @cod_fondo = '2100'
BEGIN
INSERT INTO #iscr (<<column list>>)
SELECT <<column list>> FROM _Iscritto WHERE idiscritto = 1
END
ELSE IF @cod_fondo='2103'
BEGIN
INSERT INTO #iscr (<<column list>>)
SELECT <<column list>> FROM _Iscritto WHERE idiscritto = 2
END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 29, 2014 at 8:34 am
I wanted to avoid 'insert into #table select * from table'... and only use 'select * into #table from table' simply because is too fast...
But I think that I'll use the first one (with the second one I have the error that the table already exists in database even if I put a conditional split for the parameter).
September 29, 2014 at 11:11 am
antonela (9/29/2014)
I wanted to avoid 'insert into #table select * from table'... and only use 'select * into #table from table' simply because is too fast...But I think that I'll use the first one (with the second one I have the error that the table already exists in database even if I put a conditional split for the parameter).
If this is production-quality code that you are writing, you should be avoiding SELECT ... INTO completely, in my opinion. You may run into problems with IDENTITY columns if you do not.
Instead, create the temp table with exactly the layout you require and then INSERT to it, using explicitly named column lists. NOT SELECT *.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 29, 2014 at 11:15 am
Phil Parkin (9/29/2014)
antonela (9/29/2014)
I wanted to avoid 'insert into #table select * from table'... and only use 'select * into #table from table' simply because is too fast...But I think that I'll use the first one (with the second one I have the error that the table already exists in database even if I put a conditional split for the parameter).
If this is production-quality code that you are writing, you should be avoiding SELECT ... INTO completely, in my opinion. You may run into problems with IDENTITY columns if you do not.
Instead, create the temp table with exactly the layout you require and then INSERT to it, using explicitly named column lists. NOT SELECT *.
I couldn't agree more. Only grab the columns you really need and skip the rest.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply