Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kaifi » Copy Table Structure Without Data Using 1...
Add to briefcase
12 posts, Page 1 of 2
12»»

Copy Table Structure Without Data Using 1 Line Query Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Kaifi
Posted Thursday, April 22, 2010 1:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 21, 2011 9:29 AM
Points: 9, Visits: 14
Comments posted to this topic are about the item Copy Table Structure Without Data Using 1 Line Query
Post #908357
r5d4
Posted Thursday, April 22, 2010 2:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 11, 2013 7:58 AM
Points: 267, Visits: 662
Sometimes the simplest ideas are the best.
Brilliant script.

r
Post #908400
Atif-ullah Sheikh
Posted Thursday, April 22, 2010 11:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, March 14, 2013 4:15 AM
Points: 3,240, Visits: 4,960
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
Post #909183
Pulivarthi Sasidhar
Posted Thursday, April 22, 2010 11:08 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:30 AM
Points: 870, Visits: 522
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
Post #909187
irogulji
Posted Friday, April 23, 2010 12:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 4:47 PM
Points: 11, Visits: 127
Hi all!
I usually use a bit simpler form of that expression:

select top 0 * into table_tmp from table
Post #909216
Kiran Kumar Sabinkar
Posted Friday, April 23, 2010 12:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 04, 2012 1:37 AM
Points: 64, Visits: 34
How different is the above query from the below on:

select top 0 * into table2 from table1
Post #909220
Landy_Ed
Posted Friday, April 23, 2010 1:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 8:46 AM
Points: 6, Visits: 47
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.
Post #909233
Abrar Ahmad_
Posted Friday, April 23, 2010 4:28 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 18, 2013 5:49 AM
Points: 202, Visits: 1,043
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
Post #909298
Landy_Ed
Posted Friday, April 23, 2010 4:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 26, 2012 8:46 AM
Points: 6, Visits: 47
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.
Post #909302
tamie.mcdonald
Posted Friday, April 23, 2010 8:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:17 AM
Points: 13, Visits: 173
Excellent. I use this method too but for temp tables.
select * into #temp_table from perm_table where 1=2
Post #909499
« Prev Topic | Next Topic »

Add to briefcase
12 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.