creating empty table using SELECT...INTO 1

[NathanGriffiths]

The BOL help for SELECT tells me:

"You can use SELECT...INTO to create an identical table definition (different table name) with no data by having a FALSE condition in the WHERE clause."

but can anyone tell me what the actual syntax for a statement like this would be?

thanks,

Nathan

 

[AWithers]

I think it goes something like this

select sourcetable.* into newtablename
from sourcetable
where sourcetable.field ='non-matching criteria'

Andy

 

[gregsimpson]

Try some of the following.

WHERE 3 < 2

WHERE 0 = 1

THESE WORK IN DB2. I haven't tried them in SQL SERVER.

 

[smnetto]

Hi, just put a condition that will never be true in the where clause. For example, i have a table (tb_users) with a cd_user column. I know that every user has a cd_user code greater than 0, so i can replicate my structure with this transact-sql:

select * into tb_users2 from tb_users where cd_user=0

The tb_users 2 will be created with no records.

Sergio M. Netto.

 

[NathanGriffiths]

Thanks guys, that works (I used where 0=1)

Nathan