Ygthkb

Exploding Numbers

What if you do not believe in the project benefits?

Buying a "Used" Router

Is there a way to pause a running process on Linux systems and resume later?

Why Third 'Reich'? Why is 'reich' not translated when 'third' is? What is the English synonym of reich?

Identical projects by students at two different colleges: still plagiarism?

How can I use a Module anonymously as the function for /@?

STM32 PWM problem

Is it possible to detect 100% of SQLi with a simple regex?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Why are `&array` and `array` pointing to the same address?

Ramanujan's radical and how we define an infinite nested radical

Why would you use 2 alternate layout buttons instead of 1, when only one can be selected at once

Are encryption algorithms with fixed-point free permutations inherently flawed?

Isn't a semicolon (';') needed after a function declaration in C++?

Does changing "sa" password require a SQL restart (in mixed mode)?

Boss asked me to sign a resignation paper without a date on it along with my new contract

Taking an academic pseudonym?

What happens if you declare more than $10,000 at the US border?

Short story where Earth is given a racist governor who likes species of a certain color

How does the income of your target audience matter for logo design?

What is formjacking?

How can I differentiate duration vs starting time

How to write painful torture scenes without being over-the-top

Is it “legal” to CREATE and DROP #SomeTable more than once?

SQL Server 'ignores' drop table commandTemp table lives after being explicitly droppedDoes SELECT INTO Reserve the #Object Name in TempDB Prior to Runtime?SQL Server Storage sql_variantType conversion in expression may affect “CardinalityEstimate” in query plan choice?Disk Space Considerations for Rebuilding a large Clustered Primary Key IndexOracle GoldenGate add trandata errorsShrink database or DB files not reducing size even after dropping biggest tableQuery distinct emails not logged in within a period of timeSQL 2005 Unused proceduresWhy am I getting “Snapshot isolation transaction aborted due to update conflict”?Investigating errors from strange queryINSERT/SELECT xml column from one table to another

7

I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this:

CREATE TABLE #WidgetSetting 

(

    WidgetID bigint not null,

    Name nvarchar(100) not null,

    Value nvarchar(max) not null,

    CreateDate datetime not null

)



INSERT VALUES



MERGE TABLES



DROP TABLE #WidgetSetting

But now SSMS is complaining that the object already exists by the next time the CREATE TABLE fires. What gives?

I think it's obvious I'm going to have to declare the table once at the beginning of the script, truncate instead of drop, but it's frustrating, naturally, to not be able to just drop the table and use the same name again.

sql-server sql-server-2008-r2 t-sql ssms

share|improve this question

edited May 23 '13 at 1:46

Aaron Bertrand♦

151k18289489

asked May 21 '13 at 0:34

jcolebrand♦jcolebrand

5,02843364

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  As Aaron commented below, if this is the route you've taken, the easiest fix would likely be to drop the #TABLE at the very end of the script and simply TRUNCATE it in the steps between. That's assuming it's not possible to just redesign the script to work differently in the first place. :)
  
  – Kahn
  May 21 '13 at 8:40
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  That's actually what I did to resolve it. I was just confused by the behavior. He explained the why which is what I wanted more so than the how to fix it.
  
  – jcolebrand♦
  May 21 '13 at 15:47
  

  
  
  
  

add a comment | 

7

I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this:

CREATE TABLE #WidgetSetting 

(

    WidgetID bigint not null,

    Name nvarchar(100) not null,

    Value nvarchar(max) not null,

    CreateDate datetime not null

)



INSERT VALUES



MERGE TABLES



DROP TABLE #WidgetSetting

But now SSMS is complaining that the object already exists by the next time the CREATE TABLE fires. What gives?

I think it's obvious I'm going to have to declare the table once at the beginning of the script, truncate instead of drop, but it's frustrating, naturally, to not be able to just drop the table and use the same name again.

sql-server sql-server-2008-r2 t-sql ssms

share|improve this question

edited May 23 '13 at 1:46

Aaron Bertrand♦

151k18289489

asked May 21 '13 at 0:34

jcolebrand♦jcolebrand

5,02843364

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  As Aaron commented below, if this is the route you've taken, the easiest fix would likely be to drop the #TABLE at the very end of the script and simply TRUNCATE it in the steps between. That's assuming it's not possible to just redesign the script to work differently in the first place. :)
  
  – Kahn
  May 21 '13 at 8:40
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  That's actually what I did to resolve it. I was just confused by the behavior. He explained the why which is what I wanted more so than the how to fix it.
  
  – jcolebrand♦
  May 21 '13 at 15:47
  

  
  
  
  

add a comment | 

I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this:

CREATE TABLE #WidgetSetting 

(

    WidgetID bigint not null,

    Name nvarchar(100) not null,

    Value nvarchar(max) not null,

    CreateDate datetime not null

)



INSERT VALUES



MERGE TABLES



DROP TABLE #WidgetSetting

But now SSMS is complaining that the object already exists by the next time the CREATE TABLE fires. What gives?

I think it's obvious I'm going to have to declare the table once at the beginning of the script, truncate instead of drop, but it's frustrating, naturally, to not be able to just drop the table and use the same name again.

sql-server sql-server-2008-r2 t-sql ssms

share|improve this question

edited May 23 '13 at 1:46

Aaron Bertrand♦

151k18289489

asked May 21 '13 at 0:34

jcolebrand♦jcolebrand

5,02843364

CREATE TABLE #WidgetSetting 

(

    WidgetID bigint not null,

    Name nvarchar(100) not null,

    Value nvarchar(max) not null,

    CreateDate datetime not null

)



INSERT VALUES



MERGE TABLES



DROP TABLE #WidgetSetting

share|improve this question

edited May 23 '13 at 1:46

Aaron Bertrand♦

151k18289489

asked May 21 '13 at 0:34

jcolebrand♦jcolebrand

5,02843364

share|improve this question

edited May 23 '13 at 1:46

Aaron Bertrand♦

151k18289489

asked May 21 '13 at 0:34

jcolebrand♦jcolebrand

5,02843364

edited May 23 '13 at 1:46

Aaron Bertrand♦

151k18289489

edited May 23 '13 at 1:46

Aaron Bertrand♦

151k18289489

asked May 21 '13 at 0:34

jcolebrand♦jcolebrand

5,02843364

asked May 21 '13 at 0:34

jcolebrand♦jcolebrand

5,02843364

1 Answer
1

active

oldest

votes

10

No, the parser won't let you create the same #temp table twice in the same batch (and this has nothing to do with SSMS). It doesn't even matter if only one copy of the #temp table could ever be created; for example, in the following conditional logic, which to humans could obviously only ever execute one branch, SQL Server can't see that:

IF 1 = 1

BEGIN

  CREATE TABLE #x(i INT);

  DROP TABLE #x;

END

ELSE

BEGIN

  CREATE TABLE #x(j INT);

  DROP TABLE #x;

END

Msg 2714, Level 16, State 1, Line 8

There is already an object named '#x' in the database.

And to prove it's not SSMS complaining at compile time:



DECLARE @sql NVARCHAR(MAX) = N'IF 1 = 1

BEGIN

  CREATE TABLE #x(i INT);

  DROP TABLE #x;

END

ELSE

BEGIN

  CREATE TABLE #x(j INT);

  DROP TABLE #x;

END';



EXEC sp_executesql @sql;

Yields the exact same error.

The fix, of course, is to re-use the same #temp table instead of dropping, use a different #temp table each time, or don't use #temp tables in the first place.

This is not something you should ever expect SQL Server to handle better. In other words, get used to whatever workaround you decide on.

See also this related answer on Stack Overflow that gives an alternate explanation:

• Can I recreate a temp table after dropping it?

share|improve this answer

edited 1 hour ago

answered May 21 '13 at 0:53

Aaron Bertrand♦Aaron Bertrand

151k18289489

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f42657%2fis-it-legal-to-create-and-drop-sometable-more-than-once%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

10

No, the parser won't let you create the same #temp table twice in the same batch (and this has nothing to do with SSMS). It doesn't even matter if only one copy of the #temp table could ever be created; for example, in the following conditional logic, which to humans could obviously only ever execute one branch, SQL Server can't see that:

IF 1 = 1

BEGIN

  CREATE TABLE #x(i INT);

  DROP TABLE #x;

END

ELSE

BEGIN

  CREATE TABLE #x(j INT);

  DROP TABLE #x;

END

Msg 2714, Level 16, State 1, Line 8

There is already an object named '#x' in the database.

And to prove it's not SSMS complaining at compile time:



DECLARE @sql NVARCHAR(MAX) = N'IF 1 = 1

BEGIN

  CREATE TABLE #x(i INT);

  DROP TABLE #x;

END

ELSE

BEGIN

  CREATE TABLE #x(j INT);

  DROP TABLE #x;

END';



EXEC sp_executesql @sql;

Yields the exact same error.

The fix, of course, is to re-use the same #temp table instead of dropping, use a different #temp table each time, or don't use #temp tables in the first place.

This is not something you should ever expect SQL Server to handle better. In other words, get used to whatever workaround you decide on.

See also this related answer on Stack Overflow that gives an alternate explanation:

• Can I recreate a temp table after dropping it?

share|improve this answer

edited 1 hour ago

answered May 21 '13 at 0:53

Aaron Bertrand♦Aaron Bertrand

151k18289489

add a comment | 

10

No, the parser won't let you create the same #temp table twice in the same batch (and this has nothing to do with SSMS). It doesn't even matter if only one copy of the #temp table could ever be created; for example, in the following conditional logic, which to humans could obviously only ever execute one branch, SQL Server can't see that:

IF 1 = 1

BEGIN

  CREATE TABLE #x(i INT);

  DROP TABLE #x;

END

ELSE

BEGIN

  CREATE TABLE #x(j INT);

  DROP TABLE #x;

END

Msg 2714, Level 16, State 1, Line 8

There is already an object named '#x' in the database.

And to prove it's not SSMS complaining at compile time:



DECLARE @sql NVARCHAR(MAX) = N'IF 1 = 1

BEGIN

  CREATE TABLE #x(i INT);

  DROP TABLE #x;

END

ELSE

BEGIN

  CREATE TABLE #x(j INT);

  DROP TABLE #x;

END';



EXEC sp_executesql @sql;

Yields the exact same error.

The fix, of course, is to re-use the same #temp table instead of dropping, use a different #temp table each time, or don't use #temp tables in the first place.

This is not something you should ever expect SQL Server to handle better. In other words, get used to whatever workaround you decide on.

See also this related answer on Stack Overflow that gives an alternate explanation:

• Can I recreate a temp table after dropping it?

share|improve this answer

edited 1 hour ago

answered May 21 '13 at 0:53

Aaron Bertrand♦Aaron Bertrand

151k18289489

add a comment | 

No, the parser won't let you create the same #temp table twice in the same batch (and this has nothing to do with SSMS). It doesn't even matter if only one copy of the #temp table could ever be created; for example, in the following conditional logic, which to humans could obviously only ever execute one branch, SQL Server can't see that:

IF 1 = 1

BEGIN

  CREATE TABLE #x(i INT);

  DROP TABLE #x;

END

ELSE

BEGIN

  CREATE TABLE #x(j INT);

  DROP TABLE #x;

END

Msg 2714, Level 16, State 1, Line 8

There is already an object named '#x' in the database.

And to prove it's not SSMS complaining at compile time:



DECLARE @sql NVARCHAR(MAX) = N'IF 1 = 1

BEGIN

  CREATE TABLE #x(i INT);

  DROP TABLE #x;

END

ELSE

BEGIN

  CREATE TABLE #x(j INT);

  DROP TABLE #x;

END';



EXEC sp_executesql @sql;

Yields the exact same error.

The fix, of course, is to re-use the same #temp table instead of dropping, use a different #temp table each time, or don't use #temp tables in the first place.

This is not something you should ever expect SQL Server to handle better. In other words, get used to whatever workaround you decide on.

See also this related answer on Stack Overflow that gives an alternate explanation:

• Can I recreate a temp table after dropping it?

share|improve this answer

edited 1 hour ago

answered May 21 '13 at 0:53

Aaron Bertrand♦Aaron Bertrand

151k18289489

IF 1 = 1

BEGIN

  CREATE TABLE #x(i INT);

  DROP TABLE #x;

END

ELSE

BEGIN

  CREATE TABLE #x(j INT);

  DROP TABLE #x;

END

And to prove it's not SSMS complaining at compile time:



DECLARE @sql NVARCHAR(MAX) = N'IF 1 = 1

BEGIN

  CREATE TABLE #x(i INT);

  DROP TABLE #x;

END

ELSE

BEGIN

  CREATE TABLE #x(j INT);

  DROP TABLE #x;

END';



EXEC sp_executesql @sql;

share|improve this answer

edited 1 hour ago

answered May 21 '13 at 0:53

Aaron Bertrand♦Aaron Bertrand

151k18289489

answered May 21 '13 at 0:53

Aaron Bertrand♦Aaron Bertrand

151k18289489

answered May 21 '13 at 0:53

Aaron Bertrand♦Aaron Bertrand

151k18289489