AS400 and SQL

[dvannoy]

I have a question. I am pulling data from a AS400 using VB. I want to be able to append that data into a table on my SQL server. how can i do this?? also if the records already exist i dont want to append that data..I currently am doing this in access be am now moving to SQL.

any help would be appreciated DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[cstrong]

Hey Dvannoy,

You can create a Linked Server to an AS400 or another way I have done this is to create a DTS object that contains an ActiveX Object using VBScript to connect to the AS400 and pull data into the tables on our SQL Server.

Clive

 

[dvannoy]

OK, thanks for the info.

once I create the DTS object i will then use my ADO code to connect right? how would I append the data. I am a little new to SQL server. the DTS object is in sql?? or VB??

Thanks DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[Dilyias]

What do you mean by append data? Insert / update rows? I've worked with VB apps that import/export data TO/FROM AS400 and SQL Server 7.

You are aware of SQL commands like

INSERT INTO MyTable (MyTableID, column1,column3) VALUES((uniqueid from as400),'my name','blah')

We actually had a vendor come in and write a program, it did something like this:

Import the AS400 data into CSV's.
Run a DTS to copy the data to a temp DB
Run a VB program that copied the data from the temp DB to the production DB (inserting/updating)

We are working on getting rid of the whole DTS/CSV part and having it all in one VB program. Depends on how you want to do this. Is it a one time deal or an ongoing daily/etc... import?

 

[cstrong]

Hi,

To give you an example of how our DTS works...

For our ordering system, we download all of the day's current orders from the system. This takes about 1-2 minutes to download. This is downloaded into a tempory table within the database.

We then insert into the main order table only the rows that do not already exist. Once we have applied all of the new orders, we truncate the temporary table.

Thats just a brief snapshot of how it works as the whole scheduled job is quite complex and detailed...

Clive