December 14, 2012 at 3:26 am
Hi
I am planing to transfer data between two databases by a querry and incremently when requried can any one give me idea to do so
thanks
with regards
pradeep
December 14, 2012 at 3:30 am
Do you have any timestamp columns or something which identitifes when rows where updated, created etc?
December 14, 2012 at 3:32 am
Yes i have identity column in the table
December 14, 2012 at 3:33 am
But do you have anything which tells you when a row has been updated, or do you not bother about shipping over the updated rows?
December 14, 2012 at 3:36 am
We r having last modified date column also
December 14, 2012 at 3:41 am
Then you need to do a two stage approch, load new data using left outer join, then update anything where the data modified dont match.
For new rows
INSERT INTO DestinationDB.DestinationSchema.DestinationTable
SELECT
Source.Col1,
....
....
....
FROM
SourceDB.SourceSchema.SourceTable Source
LEFT OUTER JOIN
DestinationDB.DestinationSchema.DestinationTable Destination
ON
Source.IdentityColumn = Destination.IdentityColumn
WHERE
Destination.IdentityColumn IS NULL
For updates
UPDATE
Destination
SET
Destination.Col = Source.Col,
....
....
....
FROM
DestinationDB.DestinationSchema.DestinationTable Destination
INNER JOIN
SourceDB.SourceSchema.SourceTable Source
ON
Destination.IdentityCol = Source.IdentityCol
WHERE
Destination.DateModifiedCol <> Source.DateModifiedCol
Repeat for all tables that need transfering
If you deal with deleted data as well, just reverse the first query into a delete query instead of an insert query
December 14, 2012 at 3:49 am
When data is in different sql server instances
December 14, 2012 at 3:55 am
Use linked servers
December 14, 2012 at 3:59 am
if we need to transfer data from different tables using querry
December 14, 2012 at 4:03 am
Use linked servers
Either create a Link on destination to the source, or a Link on the source to the destination.
Then just change the srcipts to match.
If link done on the source, then the destination changes to DestinationServer.DestinationDB.DestinationSchema.DestinationTable
If link done on the destination the the source changes to SourceServer.SourceDB.SourceSchema.SourceTable
December 14, 2012 at 4:13 am
shall we use ssis package for this
December 14, 2012 at 4:16 am
Yeah you could look at the merge transformation data task to do what you need, but will have to ensure you handle the updates correctly.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply