August 26, 2009 at 1:47 pm
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.
August 26, 2009 at 1:55 pm
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
August 27, 2009 at 1:16 pm
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?
August 27, 2009 at 1:22 pm
Try this and see if it works
select INFOR.TRAIN.IMSV7.EMPLOYEE.EMPID
from INFOR.TRAIN.IMSV7.EMPLOYEE
August 27, 2009 at 1:27 pm
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.
August 27, 2009 at 1:33 pm
Nevermind, I got it:
SELECT EMPLOYID,FRSTNAME,LASTNAME FROM UPR00100
LEFT JOIN INFOR.TRAIN.IMSV7.EMPLOYEE ON UPR00100.EMPLOYID = EMPLOYEE.EMPID
August 27, 2009 at 2:32 pm
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..
August 27, 2009 at 2:35 pm
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!!!
August 27, 2009 at 2:45 pm
Good stuff, glad it worked for you!
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply