Buddi Buddi - 1 month ago 7
SQL Question

Temp Table creation is throwing error in same session even dropping and recreating also

I have below code. In this i am dropping and re-creating a same temp table in same session. But table is not getting dropped while creating it second time. It's throwing an error.Can any one help me with reason? Thanks in advance.

IF OBJECT_ID('TEMPDB..#Table1','U') IS NOT NULL
BEGIN
DROP TABLE #Table1
END
CREATE TABLE #Table1
([seller_name] varchar(3), [id_seller] int, [id_buyer] int)


INSERT INTO #Table1
([seller_name], [id_seller], [id_buyer])
VALUES
('abc', 1005, 1006),
('ddd', 1009, 1186),
('ccc', 1006, 1001),
('ccc', 1006, 1002),
('ddd', 1009, 1006),
('tyu', 1001, 1186)



GO

IF OBJECT_ID('TEMPDB..#Table1','U') IS NOT NULL --Not entering into `IF` block
BEGIN
DROP TABLE #Table1
END
CREATE TABLE #Table1
([seller_name] varchar(3), [id_seller] int, [id_buyer] int, [id_buyr] int)


INSERT INTO #Table1
([seller_name], [id_seller], [id_buyer],[id_buyr])
VALUES
('abc', 1005,8,1006),
('ddd', 1009,8,1186),
('ccc', 1006,8,1001),
('ccc', 1006,8,1002),
('ddd', 1009,8,1006),
('tyu', 1001,8,1186)


go

Answer

Add GO statement at the second time you create table.It's works

IF OBJECT_ID('TEMPDB..#Table1','U') IS NOT NULL
BEGIN
DROP TABLE #Table1
END
GO
CREATE TABLE #Table1
    ([seller_name] varchar(3), [id_seller] int, [id_buyer] int, [id_buyr] int)


INSERT INTO #Table1
    ([seller_name], [id_seller], [id_buyer],[id_buyr])
VALUES
    ('abc', 1005,8,1006),
    ('ddd', 1009,8,1186),
    ('ccc', 1006,8,1001),
    ('ccc', 1006,8,1002),
    ('ddd', 1009,8,1006),
    ('tyu', 1001,8,1186)
;