Insert New Records from one Table into Another 2

[Kliot]

I have two tables, Table A and Table B with the same data in them. When new records are added to table B I want to run a query that will add those new records into table A. So basically when records in table B don't exist in table A I want to add them to table A.

How do I go about doing this?

Thanks

 

[jbenson001]

The best thing to do in my opinion would to me to get both tables in sync. Then, add a trigger to tableB that will insert into tableA any row inserted into tableB.

 

[Kliot]

That would be ideal but I'm working with two differnet databases connected over an vpn connection. I was thinking it would be more reliable to verify the vpn connection is open then run a query to sync the tables.

 

[jbenson001]

Ahhh well you didn't specify all the information in your original post.

Here is an example on how to compare 2 tables.

create table #table1 (id int)
create table #table2 (id int)

insert into #table1
select 1 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 9 union all
select 10 

insert into #table2
select 1 union all
select 2 union all
select 4 union all
select 5 union all
select 6 union all
select 8 union all
select 9 union all
select 10 

select t2.id as MissingInTable1,t1.id as MissingInTable2 from 
#table1 t1 full outer join #table2 t2 on t1.id =t2.id
where t1.id is null or t2.id is null

You may have problems doing this over a VPN connection. If you have a linked server, that would help.

 

[Kliot]

Thanks, I'll give it a try

 

[Kliot]

Worked Perfect, thanks again

 

[jbenson001]

Glad to help!

 

[hmckillop]

Have a look at replication which is built for just the scenario you have depicted. Running any query is then a manual job and requires either code outside of the database to run e.g. a small vb app, or human intervention.
Replication can be setup to check for the connections and execute as and when its available.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[Kliot]

Hmmmm, Replication, like so many things about Sql Server I wasn't familiar with it. It looks like just what I need.

Thanks for the tip