Generate CREATE TABLE script from DataTable structure

Question

Post reply

Generate CREATE TABLE script from DataTable structure

pop_gabr1el

SSC Veteran

Points: 211
More actions
April 14, 2008 at 5:08 am

#379796

Hi,
I have a select statement for moving data to another server not linked.The problem is that the destination table does not exist and must be created.How do i obtain the structure of the table resulted from the select without creating the table temporary in the source database, so i can create it on the other server using C#?
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

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

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]

try this ...

Select * Into {New Table Name} From {Old Table Name}

This will create new table with the data and structure of old table. And if you want only structure then...

Select * Into {New Table Name} From {Old Table Name} Where {1 = 2}

confirm on this.

Mahesh

[/font]

MH-09-AM-8694

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

You could use replication for this but I suspect you're trying to build a "Gold Set" or something similar one table at a time. You could use SELET/INTO which will build the column schema and copy the data, but across Linked Servers, you'll loose all other column attributes such as IDENTITY, etc and, of course, it won't copy any keys or indexes.

If you want to maintain all of that in the copy, you'll need to build a script that will build the CREATE statement for all of that... not something that most folks would be willing to do for free on a forum....

HOWEVER... DTS or SSIS will do it for you...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pop_gabr1el SSC Veteran Points: 211 More actions · Answer 3

Mahesh Bote (4/14/2008)
[font="Verdana"]
try this ...
Select * Into {New Table Name} From {Old Table Name}
This will create new table with the data and structure of old table. And if you want only structure then...
Select * Into {New Table Name} From {Old Table Name} Where {1 = 2}
confirm on this.
Mahesh
[/font]

I knew about this solution, but I was asking for one that doesn't modify the database.

Jeff Moden (4/14/2008)
You could use replication for this but I suspect you're trying to build a "Gold Set" or something similar one table at a time. You could use SELET/INTO which will build the column schema and copy the data, but across Linked Servers, you'll loose all other column attributes such as IDENTITY, etc and, of course, it won't copy any keys or indexes.
If you want to maintain all of that in the copy, you'll need to build a script that will build the CREATE statement for all of that... not something that most folks would be willing to do for free on a forum....
HOWEVER... DTS or SSIS will do it for you...

Thanks, I'll look into it.My temporary solution is to create the schema on source server(not linked servers), get the create script and delete the temp table, not sure if there are any issues i should be concerned, perhaps you could tell me.

Thank you

Edit: One issue I found was scripting Identity, which i had to remove

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Temp table???? What temp table????

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pop_gabr1el SSC Veteran Points: 211 More actions · Answer 5

Jeff Moden (4/14/2008)
Temp table???? What temp table????

By temp table i meant the one created by select..into..where 1=0 statement ran on source server, which has the only use for me to get the schema.Sorry i said temp table, i know it got you confusded 🙂

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Heh... ok... thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)