April 3, 2015 at 1:07 am
Hi ,
I want to create and drop the global temporary table in same statement.
I am using below command but I am getting below error
Msg 2714, Level 16, State 6, Line 11
There is already an object named '##Staging_Temp' in the database.
if object_id('Datastaging..##Staging_Temp') is not null
begin
drop table ##Staging_Temp
end
CREATE TABLE ##Staging_Temp(
[COL001] [varchar](4000) NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[LoadDate] [datetime] NOT NULL,
CONSTRAINT [PK_BBx_STAGING_Id] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Please suggest me the correct code
Regards,
Vipin Jha
April 3, 2015 at 1:27 am
Quick point, the temporary table resides in the tempdb, change the db part of the object_id function's parameter to point to it.
😎
/* Any User Database */
USE Test;
GO
/* The temporary table resides in tempdb*/
if object_id('tempdb..##Staging_Temp') is not null
begin
drop table ##Staging_Temp
end
CREATE TABLE ##Staging_Temp(
[COL001] [varchar](4000) NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[LoadDate] [datetime] NOT NULL,
CONSTRAINT [PK_BBx_STAGING_Id] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply