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

Question

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

score 1 · Accepted Answer · edited Nov 03 '16 at 10:16

1

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)
;

edited Nov 03 '16 at 10:16

Smart003

1,119
2
16
31

answered Nov 03 '16 at 10:13

Mansoor

4,061
1
17
27

Go is a signal for end of batch. So drop statement should be a separate one? – Tharunkumar Reddy Nov 03 '16 at 10:19

score 1 · Answer 2 · answered Nov 03 '16 at 10:15

1

Try this

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)


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
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)
GO

answered Nov 03 '16 at 10:15

de_bug5

61
4

Why that Go solves? Is there any specific reason for it? – Tharunkumar Reddy Nov 03 '16 at 10:16
http://stackoverflow.com/questions/2668529/what-is-a-batch-and-why-is-go-used - i do not know why - but - 'For example, you can't drop a table and re-create the same-named table in a single transaction, at least in Sybase' – Cato Nov 03 '16 at 11:53
1

I asked someone's opinion, and they said that a plan is built up for a batch and that the plan assumes constant table structures within the batch - if you want to change a table structure, you may need a new batch – Cato Nov 03 '16 at 12:12

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

2 Answers2