insert with two database

[lupien1]

I need to insert the data include in table1 from database1 in the table1 of database2. table1 in both database are identical and have 35 columns and the 2 first column are primary key. Can someone can tell me the query to do the job.

The query must validate value to avoid primary key constraint.

Thank you.

 

[NoCoolHandle]

Use a trigger...
eg

Create Trigger Tr_t1Insrt
on Table1 for insert
as
if  not exists (select * from db2.dbo.table2 where field in (select field from inserted))
insert into DB2.Dbo.table2 (col1,col2,col3....) 
select * from inserted

 

[NoCoolHandle]

Forgot to check my syntax

Create Trigger Tr_t1Insrt
on Table1 for insert
as
if  not exists (select * from db2.dbo.table2 where field in (select field from inserted))
[blue]begin[/blue]
insert into DB2.Dbo.table2 (col1,col2,col3....) 
select * from inserted
[blue]end[/blue]

The other should have worked, but it is aways better to be explicit.

Also

special note the inserted table is only available to the trigger (it is very special)and only contains the data that was being inserted into the table that the trigger is based on.

THerefore you can check data against it and use it for insertts etc...


HTH

Rob