table copy

> 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" wrote in message

>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)
>
 >> Stay informed about: table copy 

>>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" wrote in message
> >
> > >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.
 >> Stay informed about: table copy 

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.
 >> Stay informed about: table copy