1

Topic: drop table does not fulfill...

Wanted to write so:
create table #T (ID int)
drop table #T
create table #T (ID int, A char (10))
drop table #T
Swears that
There is already an object named ' #T ' in the database.
And why the first DROP does not fulfill?

2

Re: drop table does not fulfill...

It appears there is a restriction:
DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch

3

Re: drop table does not fulfill...

create table #T (ID int)
go
drop table #T
go
create table #T (ID int, A char (10))
go
drop table #T
go

4

Re: drop table does not fulfill...

AlexanP wrote:

It appears there is a restriction:
DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch

it is strange, whence it? It is possible in one , it is checked for a second.

5

Re: drop table does not fulfill...

https://docs.microsoft.com/ru-ru/sql/t- … tements-go
GO designates the end of packet Transact-SQL of the instruction

6

Re: drop table does not fulfill...

We read help and it is not guessed.
CREATE TABLE...
If within one stored procedure or a packet forms more than one temporary table, it different names should be appropriated.

7

Re: drop table does not fulfill...

alexeyvg wrote:

it is passed...
Strange, whence it? It is possible in one , it is checked for a second.

For temporal is not present.

wrote:

If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

If I am not mistaken, DB ENGINE  creation/announcement  objects / variables first of all, and for it is not present  after creation

8

Re: drop table does not fulfill...

TaPaK wrote:

it is passed...
For temporal is not present.

Eeee, how it?
Certainly, it is possible to create the table, and then it to delete in one .

create table #t (id int)
drop table #t

9

Re: drop table does not fulfill...

alexeyvg wrote:

it is passed...
, how it?
Certainly, it is possible to create the table, and then it to delete in one .

create table #t (id int)
drop table #t

Do not read a subject, at once answer
Speech about

create table #t (id int)
drop table #t
create table #t (id int)

10

Re: drop table does not fulfill...

TaPaK wrote:

it is passed...
Do not read a subject, at once answer
Speech about

create table #t (id int)
drop table #t
create table #t (id int)

I not about a subject, I about was specific a phrase (whence that) which surprised me:

alexeyvg wrote:

it is passed...
Strange, whence it? It is possible in one , it is checked for a second.

11

Re: drop table does not fulfill...

create table #T (ID int)
if @a = 1
drop table #T
create table #T (ID int, A char (10))
drop table #T

What to do in this case? Restriction quite natural.

12

Re: drop table does not fulfill...

alexeyvg;

DROP TABLE and CREATE TABLE

Well make DROP and CREATE instead of as you write CREATE and DROP ^)

13

Re: drop table does not fulfill...

Kolosov wrote:

create table #T (ID int)
if @a = 1
drop table #T
create table #T (ID int, A char (10))
drop table #T

What to do in this case? Restriction quite natural.

Anything, it is necessary not to write simply so.
Here it is important not to mix 2 concepts - visibility of the identifier the compiler and creation / temporary table removal
create table #T for the compiler there is a table declaring in , and it will be visible it even to its real creation (and, by itself, after its removal), for example, so:

if 1=0
select * from #t
create table #t (id int)
drop table #t
if 1=0
select * from #t

Accordingly, then it becomes clear, why it is impossible  to create in the same (to declare for the compiler) the table the second time.

if 1=0
select * from #t - what of tables means???
create table #t (id int)
drop table #t
create table #t (id varchar)
drop table #t
if 1=0
select * from #t - and here too???

14

Re: drop table does not fulfill...

TaPaK wrote:

alexeyvg;

DROP TABLE and CREATE TABLE

Well make DROP and CREATE instead of as you write CREATE and DROP ^)

Well so I about it and I write.
In that phrase which has confused me the instructions on sequence are not visible, can, the phrase is taken out of context?
There it is simply written that these 2  should not be written in one .

15

Re: drop table does not fulfill...

Phrase from the documentation
https://msdn.microsoft.com/ru-ru/library/ms173790 (v=sql.120)
DROP TABLE (Transact-SQL)
Important
DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.
Important!
Instructions DROP TABLE and CREATE TABLE cannot be fulfilled for one table in one packet. Otherwise there can be an unforeseen error.

16

Re: drop table does not fulfill...

AlexanP;
It not the requirement, and the sensible recommendation.

17

Re: drop table does not fulfill...

AlexanP wrote:

Important
DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.
Important!
Instructions DROP TABLE and CREATE TABLE cannot be fulfilled for one table in one packet. Otherwise there can be an unforeseen error.

Not absolutely truly smile

create procedure dbo.test_tmptales
as begin
if object_id (' tempdb..#test_from_prc ', ' U ') is not null
drop table #test_from_prc
create table #test_from_prc (i int null)
if object_id (' tempdb..#test_from_prc ', ' U ') is not null
drop table #test_from_prc
end
GO
drop procedure dbo.test_tmptales
GO

18

Re: drop table does not fulfill...

Maxx wrote:

it is not absolutely true smile

Yes; in general as the simple instruction for beginners descends, but as an exhaustive explanation of mechanisms of the syntax analyzer , resolutions of names, rules of visibility/existence of temporary tables looks poorly.
And even as the simple instruction for a beginner looks incorrectly, would write "Instruction CREATE TABLE it is impossible to put after CREATE TABLE for one table in one " is better that would be incorrect, apparently from your example, but all the same it is more correct, than now.

19

Re: drop table does not fulfill...

alexeyvg wrote:

would write "Instruction CREATE TABLE it is impossible to put after CREATE TABLE for one table in one "

is better that is "Instruction CREATE TABLE cannot be put after DROP TABLE for one table in one "

20

Re: drop table does not fulfill...

alexeyvg;
, but  all  in a help, and  occupies 1 minute of a spelling of the code. If the HARDWARE is real  as as - that direction of driving to it produced... Further there is nothing more valuably self-training.