copy table structure -- sql server mseq

Hugo Kornelis
2/2/2005 10:09:32 PM

[quoted text, click to view]

Hi GW,

You could use

SELECT *
INTO NewTable
FROM OldTable
WHERE 1 = 0

This will copy the columns and datatypes; it won't copy defaults,
constraints, properties and such.

However, I'd be interested to know why you want to do this. What is the
goal?

Best, Hugo
--

SusanB
3/11/2005 1:11:05 PM

Hi,
I can tell you why I want to copy a table without the data: I want to create
a staging table to receive records from another source. A procedure will then
read the records from the staging table and insert (or update if exists)
record into original table. Is there a better way than you recommended in
this post to do this?
Susan

[quoted text, click to view]

Hugo Kornelis
3/12/2005 10:59:35 PM

[quoted text, click to view]

Hi Susan,

For a staging table, you typically want either a table with the same
structure but without most of the constraints, or (depending on the
quality of the imported data) a table with a different structure (e.g.
varchar or char for most columns, siince they need to be validated
before being converted to the correct datatype). Based on your question,
I assume that the quality of the imported data is high enough to warrant
the use of a copy of the table structure.

The best way, IMO, is to pull a copy of the CREATE TABLE script from
your source control system, change the name, remove the unneeded
constraints, then file it in your source control and run it.

Second best alternative would be a script that starts with a query like
I posted and then uses ALTER TABLE commands to add the constraints that
you do need for this staging table.

Best, Hugo
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server mseq

sql server mseq : copy table structure