table copy

[GTi]

I have two identical tables with one IDENTITY column and several other
columns.
I have tested the COPY * INTO table2 FROM table1 WHERE xx
but it requers that table2 does not exist.
TABLE2 is a history database of TABLE1
How can I copy row(s) from table1 to table2 without conflict with
IDENTITY columns using SQL query.
Or must I do this from a program (C# .NET)

[Dan Guzman]

How can I copy row(s) from table1 to table2 without conflict with

IDENTITY columns using SQL query.

If you want to let SQL Server assign new identity values during the insert,
simply specify a column list and omit the identity column. For example:

INSERT INTO dbo.table2(MyColumn)
SELECT MyColumn
FROM dbo.table1

If you want to include the identity column values from the source table,
specify IDENTITY_INSERT and include the identity column in the column list:

SET IDENTITY_INSERT dbo.table2 ON

INSERT INTO dbo.table2(MyIdentityColumn, MyColumn)
SELECT MyIdentityColumn, MyColumn
FROM dbo.table1

--
Hope this helps.

Dan Guzman
SQL Server MVP
"GTi" <tu****@gmail.comwrote in message
news:11**********************@c28g2000cwb.googlegr oups.com...

>I have two identical tables with one IDENTITY column and several other
columns.
I have tested the COPY * INTO table2 FROM table1 WHERE xx
but it requers that table2 does not exist.
TABLE2 is a history database of TABLE1
How can I copy row(s) from table1 to table2 without conflict with
IDENTITY columns using SQL query.
Or must I do this from a program (C# .NET)

[GTi]

Dan Guzman wrote:

How can I copy row(s) from table1 to table2 without conflict with
IDENTITY columns using SQL query.

If you want to let SQL Server assign new identity values during the insert,
simply specify a column list and omit the identity column. For example:

INSERT INTO dbo.table2(MyColumn)
SELECT MyColumn
FROM dbo.table1

If you want to include the identity column values from the source table,
specify IDENTITY_INSERT and include the identity column in the column list:

SET IDENTITY_INSERT dbo.table2 ON

INSERT INTO dbo.table2(MyIdentityColumn, MyColumn)
SELECT MyIdentityColumn, MyColumn
FROM dbo.table1

--
Hope this helps.

Dan Guzman
SQL Server MVP
"GTi" <tu****@gmail.comwrote in message
news:11**********************@c28g2000cwb.googlegr oups.com...

I have two identical tables with one IDENTITY column and several other
columns.
I have tested the COPY * INTO table2 FROM table1 WHERE xx
but it requers that table2 does not exist.
TABLE2 is a history database of TABLE1
How can I copy row(s) from table1 to table2 without conflict with
IDENTITY columns using SQL query.
Or must I do this from a program (C# .NET)

Hope this helps.

Not directly, but u pointet me the directons.
My main problem is that I don't have full control over the colums, only
the basic ones.
Users may add new columns "on the run".
But as I now see it I do it in my c# .NET program. Retrieving all the
columns names and build the query you give me.

Thanks.

[Madhivanan]

>>Users may add new columns "on the run".

Why?
That doesnt sound a good design

Madhivanan
GTi wrote:

Dan Guzman wrote:

How can I copy row(s) from table1 to table2 without conflict with
IDENTITY columns using SQL query.

If you want to let SQL Server assign new identity values during the insert,
simply specify a column list and omit the identity column. For example:

INSERT INTO dbo.table2(MyColumn)
SELECT MyColumn
FROM dbo.table1

If you want to include the identity column values from the source table,
specify IDENTITY_INSERT and include the identity column in the column list:

SET IDENTITY_INSERT dbo.table2 ON

INSERT INTO dbo.table2(MyIdentityColumn, MyColumn)
SELECT MyIdentityColumn, MyColumn
FROM dbo.table1

--
Hope this helps.

Dan Guzman
SQL Server MVP
"GTi" <tu****@gmail.comwrote in message
news:11**********************@c28g2000cwb.googlegr oups.com...

>I have two identical tables with one IDENTITY column and several other
columns.
I have tested the COPY * INTO table2 FROM table1 WHERE xx
but it requers that table2 does not exist.
TABLE2 is a history database of TABLE1
How can I copy row(s) from table1 to table2 without conflict with
IDENTITY columns using SQL query.
Or must I do this from a program (C# .NET)
>

Hope this helps.

Not directly, but u pointet me the directons.
My main problem is that I don't have full control over the colums, only
the basic ones.
Users may add new columns "on the run".
But as I now see it I do it in my c# .NET program. Retrieving all the
columns names and build the query you give me.

Thanks.

[GTi]

Madhivanan wrote:

>Users may add new columns "on the run".

Why?
That doesnt sound a good design

Madhivanan

After you have designed your tables and it have been deployed for some
time, have you never added more columns after updates?
Have you never wanted one more column on a 3. party table?
I always design my programs and tables so I or the customer can add
extra columns without program failure.