Copy data from Source Sql Server to another destination Sql Server DB Table

Question

Copy data from Source Sql Server to another destination Sql Server DB Table

RCRock

Right there with Babe

Points: 718
More actions
March 26, 2021 at 3:06 pm

#3862437

Hi,
I want to copy from source sql server table to destination sql server table.
Requirement:
Source Sql Server EU551 and DB Table tblEMP
Destination Sql Server EU661 and DB Table tblEMP
Both source and destination db table tblEMP have same schema.
I want to check if source table has the data for current day and destination db table has no rows for the current day then only insert the data for current day else not.
for example tblEMP have date column (DateLoad)
Can someone please assist with Sql script?
I don't want to use Import and export wizard and if solution is without linked server then much preferred. Thank You.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

Are you talking about INSERTS only, or also UPDATES and (maybe) DELETES?

SSIS can probably do it (depending on your answer to the above and the existence of columns such as DateCreated, DateModified and IsDeleted on tblEMP).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Bredon SSC Journeyman Points: 88 More actions · Answer 2

Hello RCRock.

Think you can achieve this using OPENROWSET. Be warned that it can be tricky configuring the connection strings. The Microsoft documentation has a pile of different examples that should help. See https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver15

Best of luck.

Bredon

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 3

Linked servers will be the easiest way. Then you just add the server name as a prefix:

SELECT *
  FROM [ServerName].[DatabaseName].[SchemaName].[TableName]

https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-ver15

frederico_fonseca SSCoach Points: 16167 More actions · Answer 4

although going through the route of linked server best practices would mean that the linked server would not be named as the server name but rather as a logical name - so same name could be used both in dev/tst/prod - and would also mean that if the linked server changes its name only the linked server details need to be changed without affecting any code.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Best practice would be to first import the data from the source server into a staging table. (This can be done through BCP, SSIS, or by linked server.) Never attempt joins across linked servers for any real-world volume of data.

We could give you an example of testing the staging table against the target, if you first answer this question. When you say "current day" do you mean GETDATE() (today's date)? Or do you mean the date of whatever rows you are trying to insert?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 6

frederico_fonseca wrote:

although going through the route of linked server best practices would mean that the linked server would not be named as the server name but rather as a logical name - so same name could be used both in dev/tst/prod - and would also mean that if the linked server changes its name only the linked server details need to be changed without affecting any code.

Or just create synonyms on the current database for the tables on the linked server.