Help with Select - into

[FET]

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.

[David Portas]

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
--

[Serge Rielau]

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

[FET]

"David Portas" <RE****************************@acm.org> wrote in message news:<rd********************@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.