duplicate ids after insert

[toufik]

Hi,
I'm trying to create an archiving script, using the folowing query for all
database tables
insert into table2 select * from table1 (table1, table2 are 2 tables having
exactly the same definition)

The rpoblem is that the rows are,'t deleted from the table TABLE2, so the
next time the user runs the script he'll find some rows already existing in
TABLE2, so he'll have an error.

How can I handle this without to know exacly what are the IDs or the
structure, juste to say "if the row (the unique key) exists skip it, but
continue with the other rows"

Thanks.

 

[Robert Porter]

You would need to do something like this: INSERT INTO table2 SELECT * FROM table1 WHERE (PK_TABLE1 not in (SELECT PK_TABLE2)) Messy and syntactically not quite correct, can't remember the exact syntax, but in essence you are skipping the selection of records from table 1 that already exist in table2 using a sub query. It gets a little trickier if you also need to update changed records that exist in table1 and table2. Then I would suggest using another column in table1 to track when the record gets changed, and reset the column during your copy operation. hth nntp://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet/<[email protected]>

Hi,
I'm trying to create an archiving script, using the following query for all
database tables
insert into table2 select * from table1 (table1, table2 are 2 tables having
exactly the same definition)

The rpoblem is that the rows are,'t deleted from the table TABLE2, so the
next time the user runs the script he'll find some rows already existing in
TABLE2, so he'll have an error.

How can I handle this without to know exacly what are the IDs or the
structure, juste to say "if the row (the unique key) exists skip it, but
continue with the other rows"

Thanks.



[microsoft.public.dotnet.framework.adonet]

 

[toufik]

Thanks my friend,
with your solution, i'me supposed to know the primary keys of my table, But
I dn't like to do all this because I'll do the script for all tables tof my
DB,

I was thinking that there is an option somewhere to say "skip if already
exists..".

 

[Elton Wang]

Hi there,

Following query gives all records in table1 without dup in
table2:

Select T1.* From table1 T1 Left Join table2 T2 On
T1.p_key = T2.p_key
Where T2.p_key Is Null

Hope it helps,

Elton Wang
(e-mail address removed)

 

[Peter Huang [MSFT]]

Hi

I agree with Robert's suggestion.
But I think the primary key or related information is necessary for us to
detect if a record is already exist.
If you still have any concern, please feel free to post here.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.