question

Chris_R avatar image
Chris_R asked

Moving iSeries tables to SQL Server

Hello

I'm looking for a way to dynamically import approximately 11000 tables from an iSeries database to initially populate a SQL Server. I need to pull in Column Headings as well as data. I've come up with an approach to create all tables as .csv and have successfully imported a table although manually. I'm looking for some guidance on automating this from the SQL side. I've done some research but have yet to find a way to pull these tables in (Bulk Import only brings data?).

Any help, comments ideas are greatly appreciated.

Thanks

EDIT - I have been able to place the created .csv tables on a Network drive which takes the iSeries out of the equation. My question would now be is there a way for the SQL Server to create and load many tables from csv files that are located on a network drive?

sql servertablesimportcreate databaseapproach
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Chris_R avatar image
Chris_R answered

@ThomasRushton thanks for the reply. If I'm reading that correctly it looks as though the table name needs to be specified. "To create new_table from a remote source table, specify the source table using a four-part name in the form linked_server.catalog.schema.object in the FROM clause of the SELECT statement"

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·

Yes. If you can create a linked server to your iSeries using eg an ODBC driver, then you should be able to directly query data from there, pulling it straight into your SQL Server database using the SELECT...INTO...FROM... syntax.

Not having an iSeries to test with, though, I can't do more than theorise... sorry about that!

0 Likes 0 ·
Chris_R avatar image Chris_R commented ·

@ThomasRushtonThanks, I appreciate the response. I don't think that will work for what I need to accomplish.

I'll keep digging! :)

0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ Chris_R commented ·

Good luck! And let us know how you get on.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.