copy two tables with new id?

4 Aug 2006 6:45 PM

jkarpago

Hi:
I have two tables and I want to copy some rows of origin table to
destination table but these rows have to change its id to a new one.
the problem is that the program that uses the tables insert the id, so
the sql table does not insert the id automatically.
I tried with:
INSERT INTO destination
                      (id, name,address)
SELECT     id, name,address
FROM         origin where addres ='NY'

but the ids finally are bad cause it copy the old id.
Is there any way to set the id in this query to the next id in the
destination table?

Thanks for your help

4 Aug 2006 7:04 PM

David Portas

jkarpago wrote:
Show quote

> Hi:
> I have two tables and I want to copy some rows of origin table to
> destination table but these rows have to change its id to a new one.
> the problem is that the program that uses the tables insert the id, so
> the sql table does not insert the id automatically.
> I tried with:
> INSERT INTO destination
>                       (id, name,address)
> SELECT     id, name,address
> FROM         origin where addres ='NY'
>
> but the ids finally are bad cause it copy the old id.
> Is there any way to set the id in this query to the next id in the
> destination table?
>
> Thanks for your help

Assuming ID is an IDENTITY columns, do this:

INSERT INTO destination (name,address)
SELECT name,address
FROM origin
WHERE addres ='NY' ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

4 Aug 2006 8:23 PM

Hari Prasad

If you have not defined an identity property to column ID in destination
table then, first thing is to define an identity property into the
destination table and after that you can use the below command

INSERT INTO destination
                      (name,address)
SELECT     name,address
FROM         origin where addres ='NY'

Now a new incremental sequence number will be inseted into destination table
based on the INCR you provided while creating the destination table

Thanks
Hari
SQL Server MVP
Show quote

"jkarpago" wrote:

> Hi:
> I have two tables and I want to copy some rows of origin table to
> destination table but these rows have to change its id to a new one.
> the problem is that the program that uses the tables insert the id, so
> the sql table does not insert the id automatically.
> I tried with:
> INSERT INTO destination
>                       (id, name,address)
> SELECT     id, name,address
> FROM         origin where addres ='NY'
>
> but the ids finally are bad cause it copy the old id.
> Is there any way to set the id in this query to the next id in the
> destination table?
>
> Thanks for your help
>
>

5 Aug 2006 9:30 AM

jkarpago

The main problem is that I cannot define an identity column cause if I
do this all the users of the program will get an error when they work
because the program assign the identity itself in the id column, which
is the column I want to change when I copy the records.

The thing is something like:

Origin table
id      name    address
1       john       NY
2       mary      LA
3       michael NY
4       kate       NY

Destination table
id      name     address
1       philip     LA
2       charles CHI
3       sharon   NY
4       tom       LA
5       bryan     NY

so the result I want to get would be:
id      name     address
1       philip     LA
2       charles CHI
3       sharon   NY
4       tom       LA
5       bryan     NY

6       john       NY
7       michael NY
8       kate       NY

but the id is not autoincrement in this table.

5 Aug 2006 3:18 PM

--CELKO--

What is the natural key for this data? Since you do not know that rows
are not records, you might not have any idea what a key is. Most
mailing lists have one or they generate a hashed artificial key from
the data -- look at the top line of the labels on your magazine
subscription.

5 Aug 2006 7:02 PM

Tom Cooper

Note that "mary LA" is in your sample origin table, but not in your sample
result, I assume that is just a typo.

Something like the following (as always, carefully test any update before
actually applying it to your data):

Insert Destination(id, name, address)
Select o.id + x.CurrentMax,
o.name,
o.address
From Origin o
Cross Join
(Select Max(id) As CurrentMax From Destination d) x

Tom

Show quote

"jkarpago" <jkarp***@gmail.com> wrote in message
news:1154770204.883246.190310@s13g2000cwa.googlegroups.com...
> The main problem is that I cannot define an identity column cause if I
> do this all the users of the program will get an error when they work
> because the program assign the identity itself in the id column, which
> is the column I want to change when I copy the records.
>
> The thing is something like:
>
> Origin table
> id      name    address
> 1       john       NY
> 2       mary      LA
> 3       michael NY
> 4       kate       NY
>
> Destination table
> id      name     address
> 1       philip     LA
> 2       charles CHI
> 3       sharon   NY
> 4       tom       LA
> 5       bryan     NY
>
>
> so the result I want to get would be:
> id      name     address
> 1       philip     LA
> 2       charles CHI
> 3       sharon   NY
> 4       tom       LA
> 5       bryan     NY
>
> 6       john       NY
> 7       michael NY
> 8       kate       NY
>
> but the id is not autoincrement in this table.
>