Help with Select - into

Hi everybody,

I have two tables 'tab1' and 'tab2'. 'tab2' contains the same columns
as 'tab1'. 'tab2' does NOT contain any of the constraints of 'tab1',
just the fields. When I create this 'tab2' table using CREATE TABLE,
it gets created fine. Then I use a stored procedure which has a SELECT
INTO statement to copy all data from 'tab1' into 'tab2'. Now If I want
to append more data to 'tab2', I find that it tells me IDENTITY INSERT
on 'tab2' SHOULD BE SET TO ON. I have not defined any identity columns
in the CREATE TABLE, but after executing the SELECT INTO, I found that
it made one column the IDENTITY. Why is this so and How do I just copy
the data from 'tab1' without the frills ? All i want is a dump of one
table into another. How do I do this with an SQL query ?

Thanks in advance.

Best Regards.If you do SELECT INTO it will copy all the columns, including the IDENTITY
property but not including any CHECK, UNIQUE, FK or PK constraints. SELECT
INTO will fail if the target table already exists.

To re-create the table without the identity column:

CREATE TABLE Tab1 (X INTEGER NOT NULL, Y INTEGER NOT NULL, ...)

INSERT INTO Tab2 (X,Y,...)
SELECT X,Y,...
FROM Tab1

--
David Portas
----
Please reply only to the newsgroup
--|||You could try to confuse SQL Server by adding a bogus expression to the
identity column's select to break the connection.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<rdadnS_XYP16OyGi4p2dnA@.giganews.com>...
> If you do SELECT INTO it will copy all the columns, including the IDENTITY
> property but not including any CHECK, UNIQUE, FK or PK constraints. SELECT
> INTO will fail if the target table already exists.
> To re-create the table without the identity column:
> CREATE TABLE Tab1 (X INTEGER NOT NULL, Y INTEGER NOT NULL, ...)
> INSERT INTO Tab2 (X,Y,...)
> SELECT X,Y,...
> FROM Tab1

Thank you for your help. It worked.

Best Regards.