synchronize 2 tables

ymho · #1 (**permalink**) 11-20-07, 10:23

i need to synchronize a slave table from master table daily. i would like to create "timestamp" and compare their values... does it work?

pros & cons

thx

Martijnvs · #2 (**permalink**) 11-21-07, 04:46

If the 2 tables have an identical structure, you can truncate the slave-table, bcp-out the master-table and bcp-in in the slave table.

ymho · #3 (**permalink**) 11-21-07, 10:56

same structure ... but the table size is large(4G data + 3G for 4 indexes + around 20,000,000 records) ... so seems cant drop index, truncate, bcp out & in & recreate indexes in short time,

trvishi · #4 (**permalink**) 11-22-07, 11:32

Another option is to use sqsh. You can do select statements and redirect the output to bcp thro sqsh. A lot faster than inserts.

ymho · #5 (**permalink**) 11-22-07, 11:48

Quote:

Originally Posted by trvishi

Another option is to use sqsh. You can do select statements and redirect the output to bcp thro sqsh. A lot faster than inserts.

can you show me the sample with command?

trvishi · #6 (**permalink**) 11-24-07, 12:24

1> SELECT customer_id, item, SUM(qty)
2> FROM orders
3> GROUP BY customer_id, item
4> \bcp -S SYB_DSS shipping.dbo.order_summary

Check http://www.sqsh.org/sqsh_features.html

You need to download sqsh in your environment if you havent already. Its free GNU based.