SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Copy Table Structure Without Data Using 1 Line Query


Copy Table Structure Without Data Using 1 Line Query

Author
Message
Kaifi
Kaifi
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 14
Comments posted to this topic are about the item Copy Table Structure Without Data Using 1 Line Query
r5d4
r5d4
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 821
Sometimes the simplest ideas are the best.
Brilliant script.

r
Atif-ullah Sheikh
Atif-ullah Sheikh
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4138 Visits: 5201
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 here


Pulivarthi Sasidhar
Pulivarthi Sasidhar
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1827 Visits: 1024
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
irogulji
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 236
Hi all!
I usually use a bit simpler form of that expression:

select top 0 * into table_tmp from table
Kiran Kumar Sabinkar
Kiran Kumar Sabinkar
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 44
How different is the above query from the below on:

select top 0 * into table2 from table1
Landy_Ed
Landy_Ed
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 55
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_
Abrar Ahmad_
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 1298
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





Landy_Ed
Landy_Ed
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 55
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.
tamie.mcdonald
tamie.mcdonald
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 227
Excellent. I use this method too but for temp tables.
select * into #temp_table from perm_table where 1=2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search