Select and Insert with INNER JOIN?

[Honyo]

Hi

I have now tried 25 different ways to do this but have finally given up. If anyone can help, then I would greatly appreciate it.

I have a Production SQL server (SQLD) in Denver
I have a Production SQL server (SQLV) in Vancouver
I have a new SQL server (SQLNew)also in Vancouver (different machine then SQLV)

All three these machine have a database called AVS.
AVS has 2 main tables in it (Transaction_Info and Trans_Feature_Info)

I Need to create a stored procedure that will run from the SQLNew server, go out to SQLD and pull the last hours records. These records will then be inserted into the SQLNew AVS table. I then need it to do the same for SQLV.
The sp must also take duplicates into account.

What I am looking for at the end of the day is a SQL server (SQLNew) that a current copy (within an hour) of all the information on the other 2 servers (SLQD and SQLV).

JamesLean helped with a basic Select and Insert but I need something more complicated.

Can anyone help here?

Regards
Honyo

 

[JayKusch]

Need more info on the point ... "The sp must also take duplicates into account. ". Are we looking for dupes just w/in the 1 hour set or are we looking for dupes that span the whole table?

Thanks

J. Kusch

 

[sunila7]

I think you should use replication to do this. here is a link to snapshot replication on msdn

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/repltypes_627i.asp

Hope this helps

Sunil

 

[Honyo]

I need to make sure that the files I copy (insert) will not create a duplicate record.

Regards
Honyo

 

[Honyo]

Hi

I thought of replication but it is too bandwidth intensive?

Regards
Honyo

 

[sunila7]

REplication will have an impact on your network resources. have a look at this article on msdn

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/repl.asp

Sunil