MS SQL Server: SQL Script to Push data from 1 server to another Server

To insert/push data from one to another db, basically using common insert script : INSERT INTO SELECT statement

SQL INSERT INTO SELECT Syntax

We can copy all columns from one table to another, existing table:

INSERT INTO table2
SELECT * FROM table1;

Or we can copy only the columns we want to into another, existing table:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

In my case:
I want to push data for integration purpose from
Server A: table dbA:Jdata1 and table dbA.Jdata2
to Server B: table dbB.Jdata1 and table dbB.Jdata2

Let say; server ip like this:
Server A: 10.10.1.20
Server B: 10.10.2.25

The script must take place in server which you create the Linked Server . It will look like this;

INSERT INTO [10.10.2.25].[dbB].[Jdata1]
(trx_id, name, amount, recept_no, trx_date)
SELECT (trx_id, name, amount, recept_no, trx_date)
FROM  [10.10.1.20].[dbA].[Jdata1] where trx_date = cast(dateadd(d, -1, GETDATE()) as DATE)

** cast(dateadd(d, -1, GETDATE()) as DATE) refer to yesterday transaction date

It same goes to Jdata2;

INSERT INTO [10.10.2.25].[dbB].[Jdata2]
(trx_id, account_no, amount_cr, amount_dr,trx_desc, trx_date)
SELECT (trx_id, account_no, amount_cr, amount_dr,trx_desc, trx_date)
FROM  [10.10.1.20].[dbA].[Jdata2] where trx_date = cast(dateadd(d, -1, GETDATE()) as DATE)

You can put both script in SQL Job Schedule step to be run daily.

Read more in: http://www.w3schools.com/sql/sql_insert_into_select.asp


.