copy table structure

Appreciate it if someone can show me how to copy table structure to a new
table without the data using a query.
Thanks.
Beginner..
On Wed, 2 Feb 2005 04:07:06 -0800, GW wrote:

>Appreciate it if someone can show me how to copy table structure to a new
>table without the data using a query.
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
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||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
"Hugo Kornelis" wrote:

> On Wed, 2 Feb 2005 04:07:06 -0800, GW wrote:
>
> 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
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Fri, 11 Mar 2005 13:11:05 -0800, SusanB wrote:

>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
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
(Remove _NO_ and _SPAM_ to get my e-mail address)