Copy data to different server

Question

Post reply

Copy data to different server

Viewing 9 posts - 16 through 23 (of 23 total)

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

mohammed.a SSC Veteran Points: 246 More actions · Answer 1

How about writting a trigger to maintain new temp kind of table on same server and same database.

On nightly basis use BCP to port data to different server.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Technically the trigger and bcp option would work. The solution of using a scheduled stored proc is superior to the trigger. As for the SSIS scheduled job - we have SSIS on a standard install of SQL where we have the jobs scheduled that run the SSIS packages nightly for any ETL stuff we do.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 3

I was trying to just query this info to see my results before I did the insert. Both of my servers are linked together through the sp_linkedserver, so I wrote my query like this:

SELECT EMPLOYID,FRSTNAME,LASTNAME FROM UPR00100

LEFT JOIN INFOR.TRAIN.IMSV7.EMPLOYEE ON UPR00100.EMPLOYID = INFOR.TRAIN.IMSV7.EMPLOYEE.EMPID

WHERE INFOR.TRAIN.IMSV7.EMPLOYEE.EMPID IS NULL

I'm getting a error that says:

Msg 4104, Level 16, State1, Line 1

The multi-part identifier "INFOR.TRAIN.IMSV7.EMPLOYEE.EMPID" could not be bound.

Any suggestions?

mohammed.a SSC Veteran Points: 246 More actions · Answer 4

Try this and see if it works

select INFOR.TRAIN.IMSV7.EMPLOYEE.EMPID

from INFOR.TRAIN.IMSV7.EMPLOYEE

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 5

I get the same error when I try:

SELECT INFOR.TRAIN.IMSV7.EMPLOYEE.EMPID

FROM INFOR.TRAIN.IMSV7.EMPLOYEE

However, if I try:

SELECT EMPID

FROM INFOR.TRAIN.IMSV7.EMPLOYEE

That works.

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 6

Nevermind, I got it:

SELECT EMPLOYID,FRSTNAME,LASTNAME FROM UPR00100

LEFT JOIN INFOR.TRAIN.IMSV7.EMPLOYEE ON UPR00100.EMPLOYID = EMPLOYEE.EMPID

kramaswamy SSCoach Points: 18181 More actions · Answer 7

When you're dealing with linked servers, I'd suggest you use this approach:

SELECT T1.Col1, T1.Col2, T1.Col3, T2.Col1, T2.Col2

FROM Server.Database.Schema.Table T1

JOIN Server.Database.Schema.Table T2 ON T1.Col1 = T2.Col1

So, for your query, it would be:

SELECT T1.EMPLOYID, T1.FRSTNAME, T1.LASTNAME

FROM UPR00100 T1

LEFT JOIN INFOR.TRAIN.IMSV7.EMPLOYEE T2 ON T1.EMPLOYID = T2.EMPID

WHERE T2.EMPID IS NULL

You don't need to specify the T1. if the column is specific to that table, but it couldn't hurt, and it makes for clearer code. You also should probably use more readable names than T1, T2, etc..

jordon.shaw Hall of Fame Points: 3571 More actions · Answer 8

Just so everybody knows, this is what I ended up doing.

I linked the servers together, using the system stored procedure of sp_addlinkedserver. Once they were linked, SQL looked at them as being on the same server, even though they were really two different servers. So, I did what was suggested and wrote a stored procedured that queried the one table and joined to the other table and returned the employees that were showing NULL in my destination table, meaning they weren't there, then I did the Insert statement against them. Put this in a SQL Scheduled Job and it works perfectly. In case you're needing it, here is my code, even though I pulled a little more data than I orginally had planned, here is what I did:

INSERT INTO INFOR.TRAIN.IMSV7.EMPLOYEE (EMPID, EMPFIRST, EMPLAST, RATE)

SELECT UPR00100.EMPLOYID,FRSTNAME,LASTNAME,PAYRTAMT FROM UPR00100

LEFT JOIN INFOR.TRAIN.IMSV7.EMPLOYEE ON UPR00100.EMPLOYID = EMPLOYEE.EMPID

LEFT JOIN dbo.UPR00400 ON dbo.UPR00100.EMPLOYID = dbo.UPR00400.EMPLOYID

WHERE UPR00100.INACTIVE = '0'

AND UPR00100.USERDEF2 = '001'

AND UPR00400.PAYRCORD = '001'

AND DEPRTMNT != 'ADMCIP'

AND DEPRTMNT != 'ADMIN'

AND DEPRTMNT != 'BILLIN'

AND DEPRTMNT != 'C10'

AND DEPRTMNT != 'COBG'

AND DEPRTMNT != 'COURT'

AND DEPRTMNT != 'DISAB'

AND DEPRTMNT != 'FINANC'

AND DEPRTMNT != 'HR'

AND DEPRTMNT != 'LEGAL'

AND DEPRTMNT != 'MIT'

AND DEPRTMNT != 'PURCH'

AND DEPRTMNT != 'RECORD'

AND DEPRTMNT != 'SCHOOL'

AND DEPRTMNT != 'TOC'

AND EMPLOYEE.EMPID IS NULL

I know, alot of And statements, huh?

Anyway, just wanted to share with everybody!!! Thank you all for your help!!!

kramaswamy SSCoach Points: 18181 More actions · Answer 9

kramaswamy

SSCoach

Points: 18181

August 27, 2009 at 2:45 pm

#1045895

Good stuff, glad it worked for you!