Hello all, I found this forum to 1

[eselhassan]

Hello all,

I found this forum to be very helpfull, so I'm back with more questions. I'm querying AS400 machine by linking it to SQL Server just fine.

I would like however, rather than getting the data display in the results pane of Query Analyzer I would like to automatically insert the data into an existing sql table. Can someone give me a hand on where to start?

I'm thinking to create a stored procedure that query the AS400 and update my sql table nightly.

My current query is a simple one:

SELECT * FROM OpenQuery(MyServer, 'SELECT field1, field2 FROM LIBRARY1/FILE1').

Thanks for all the help I can get.

 

[twifosp]

IF you just want to replace the data then yea, you can make a stored proc to basically do:

INSERT INTO tablename
SELECT * FROM OpenQuery(MyServer, 'SELECT field1, field2 FROM LIBRARY1/FILE1')

If you want to do incremental updates and truncates, you'll want to learn how to create DTS packages... search for DTS in books online and enterprise manager.

 

[eselhassan]

I would like to execute an UPDATE statement rather than the INSERT statement. That way I can update my SQL table quickly as changes occur to the AS400 file. Thanks in advance.

I tried the DTS method, but I got to the point where I still have to build a query to transfer data.

I rather just do an update query now and work on my DTS short comings later. Thanks

 

[JamesLean]

So you want to update your SQL table with data from your AS400? Something like this:

UPDATE t1
SET col2 = t2.col2,
  col3 = t2.col3
FROM sql_table t1
  JOIN OPENQUERY(MyServer, 'SELECT idcol, col2, col3 FROM LIBRARY1/FILE1') t2
    ON t1.idcol = t2.idcol
WHERE t1.idcol = 123

Obviously you only need the WHERE clause if you want to update a certain row. If you want to update the entire table then leave it off.

--James