Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Copy Table Structure Without Data Using 1 Line Query Expand / Collapse
Author
Message
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
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: Tuesday, October 14, 2014 10:59 AM
Points: 267, Visits: 689
Sometimes the simplest ideas are the best.
Brilliant script.

r
Post #908400
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: Monday, October 27, 2014 9:23 AM
Points: 3,241, Visits: 5,003
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
Posted Thursday, April 22, 2010 11:08 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 26, 2014 4:11 AM
Points: 1,063, Visits: 714
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
Posted Friday, April 23, 2010 12:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 28, Visits: 190
Hi all!
I usually use a bit simpler form of that expression:

select top 0 * into table_tmp from table
Post #909216
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: Friday, September 26, 2014 6:16 AM
Points: 64, Visits: 39
How different is the above query from the below on:

select top 0 * into table2 from table1
Post #909220
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, April 7, 2014 7:58 AM
Points: 6, Visits: 54
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
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: Tuesday, October 14, 2014 7:49 AM
Points: 212, Visits: 1,117
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
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, April 7, 2014 7:58 AM
Points: 6, Visits: 54
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
Posted Friday, April 23, 2010 8:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:18 AM
Points: 14, Visits: 198
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»»

Permissions Expand / Collapse