CODEWITHSUNDEEP
sql-serversql-server-2008
kalls
kalls

Reputation: 2855

SQL Server - Schedule Jobs Getting data from one database to another

I am planning to schedule a job on one my database and which basically runs at 5:00 PM everyday.

Database 1 : TestOne TableName: MyTable

Database 2 : TestTwo TableName: MyTable

My schedule job resides in TestOne and Inserts into MyTable and the values has to come from TestTwo.MyTable.

Is there any example that's available that does something similar? Is this as simple as getting an user from TestOne.MyTable access rights on TestTwo.MyTable?

I appreciate your inputs.

Upvotes: 0

Views: 3628

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21756

Some simple scenarios (assuming - i can do the truncate):

truncate table TestOne.dbo.MyTable
insert TestOne.dbo.MyTable(fieldslist)
select fieldslist from TestTwo.dbo.MyTable

OR

using the merge (more like the incremental, but needs an unique key):

MERGE TestOne.dbo.MyTable T
USING (SELECT fieldslist from TestTwo.dbo.MyTable) S
ON T.UniqueKey = S.UniqueKey
WHEN MATCHED THEN UPDATE SET
  field1=S.field1,
  ...
WHEN NOT MATCHED THEN 
    insert (fieldslist)
    VALUES(fieldslist)
WHEN NOT MATCHED BY SOURCE DELETE;

Upvotes: 0

Christian Specht
Christian Specht

Reputation: 36421

The user needs to have enough permissions on both databases, of course (read from TestTwo.MyTable / write to TestOne.MyTable).

The only other thing you need is the correct syntax to access one database from the other.
If the job resides in TestOne, the query has to look like this (out of my head, untested):

use TestOne

insert into MyTable (Column1, Column2, ...)
select Column1, Column2, ...
from TestTwo.dbo.MyTable

The important thing when accessing other databases is the order: Database.Schema.Table
In my example (TestTwo.dbo.MyTable) I used the default schema dbo - you need to change that in my query if your table has a different schema.

Upvotes: 1

Related Questions