Copy Table Structure Without Data Using 1 Line Query

Question

Post reply

Copy Table Structure Without Data Using 1 Line Query

Kaifi

SSC Journeyman

Points: 79
More actions
April 22, 2010 at 1:21 am

#386761

Comments posted to this topic are about the item Copy Table Structure Without Data Using 1 Line Query

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

r5d4 SSCrazy Points: 2522 More actions · Answer 1

Sometimes the simplest ideas are the best.

Brilliant script.

r

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 2

Very Simple. I usually go with this strategy.

Good to share things like this. They can be very helpful to many.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 3

hi,

Create a table.....

CREATE TABLE [dbo].[Employee](

[eid] [int] NULL,

[fname] [varchar](100) NULL,

[lname] [nvarchar](200) NULL

)

Insert some data

Then execute the following Query

Select * into tbl_test from employee where 1=0

The above query will copy only Table not data...

regards,

Chowdary

irogulji SSC Journeyman Points: 96 More actions · Answer 4

Hi all!

I usually use a bit simpler form of that expression:

select top 0 * into table_tmp from table

Kiran Kumar Sabinkar SSC Enthusiast Points: 114 More actions · Answer 5

How different is the above query from the below on:

select top 0 * into table2 from table1

Landy_Ed SSC Veteran Points: 228 More actions · Answer 6

Thing to bear in mind with this is the new table is written to the default filegroup, does not recreate the indexes and reseeds any identity specification back to the default state on the table.

Abrar Ahmad_ SSCarpal Tunnel Points: 4244 More actions · Answer 7

[font="Verdana"]

Landy_Ed (4/23/2010)
Thing to bear in mind with this is the new table is written to the default filegroup, does not recreate the indexes and reseeds any identity specification back to the default state on the table.

Notice IDENTITY and INDEX !!

CREATE TABLE TPOOP (

ID INT IDENTITY(1,1) UNIQUE NOT NULL,

REDO NUMERIC(8,3) NULL

);

GO

SELECT * INTO TPOOP2 FROM TPOOP

GO

SELECT * FROM TPOOP

SELECT * FROM TPOOP2

GO

EXEC sp_help 'TPOOP'

GO

EXEC sp_help 'TPOOP2'

GO

[/font]

Landy_Ed SSC Veteran Points: 228 More actions · Answer 8

use tempdb

go

create table test (var1 int identity(1,1) constraint pk_test primary key clustered, var2 varchar(100))

go

create index ix_test on test(var2)

go

insert test(var2) select 'a'

go

select * into test2 from test where 1 = 2

go

sp_help 'test'

go

sp_help 'test2'

go

print 'bite me'

edit : Just checked for version behaviour differences, on both sql 2000 and 2008, the PK is lost along with the index, the identity is reseeded. I've not bothered to demonstrate filegroup behaviour because there's not much point. Personally, I only use such an approach mid-batch to create temp objects, not persistent ones.

tamie.mcdonald SSC Veteran Points: 227 More actions · Answer 9

Excellent. I use this method too but for temp tables.

select * into #temp_table from perm_table where 1=2

skumar.klm SSC Journeyman Points: 85 More actions · Answer 10

There is an issue held to copy table for this way .Key and identity are lost.Only use the query to use take Backup ony.

Jorge Font Newbie Points: 3 More actions · Answer 11

is not this better?

SELECT TOP 0 * INTO [New_Table1] FROM [Table1]

GO