Update Query in Linked Server

[eselhassan]

I'm currently using OPENQUERY to move data form AS400 to SQL. INSERT is what I use and it is working just fine. However I'm just curious on how UPDATE query syntax is written for similar puropse. Any headsup is appreciated. Thanks

 

[JamesLean]

Basically you should be able to use OPENQUERY anywhere you can use an ordinary table name. Have a go and if you need help please post more details/example query.

--James

 

[eselhassan]

The SQL table is one that keeps all employees even those retire, terminated, etc.. The AS400 file that holds same data only has current employees. I want to do an update to the SQL table ... which means appending only the new employees from the AS400 file. Here is some code

UPDATE sqlEmp
SET EmpID = SEEN,
    Fname = SEFNM,
    Lname = SELNM,
    Add = SESTR1,
    Status = SESTAT
FROM (SELECT * FROM OPENQUERY(AS400Svr, 'select SEEN, SEFNM, SELNM, SESTR1, SESTAT from my400Lib/my400File'))

Is there a better way than this? thanks

 

[JamesLean]

If you only want to add the rows that aren't already in the SQL table you need to do an INSERT rather than an UPDATE, something like this:

INSERT sql_table (id, c1, c2, c3)
SELECT t1.*
FROM OPENQUERY(AS400Svr, 'SELECT id, c1, c2, c3 FROM my400Lib/my400File') t1
  LEFT JOIN sql_table t2 ON t1.id = t2.id
WHERE t2.id IS NULL

--James

 

[eselhassan]

Question for James, t1 and t2 are those sql tables? are they temp tales then or what? Thanks

 

[JamesLean]

t1 and t2 are just the aliases I have assigned to the two tables (t1 is the AS400 table and t2 is the sql table).

You could just as easily call them bob and fred!

--James