Copy Table Structure Without Data Using 1 Line Query

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

  • Sometimes the simplest ideas are the best.

    Brilliant script.

    r

  • 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]

  • 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

  • Hi all!

    I usually use a bit simpler form of that expression:

    select top 0 * into table_tmp from table

  • How different is the above query from the below on:

    select top 0 * into table2 from table1

  • 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.

  • [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]

  • 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'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    insert test(var2) select 'a'

    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.

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

    select * into #temp_table from perm_table where 1=2

  • 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.

  • is not this better?

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

    GO

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

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