Get SQL insert statements from existing table
Last Post 27 Nov 2012 02:57 PM by Jim_IT. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Joseph Kennedy
Advanced Member
Advanced Member
Posts:398
Avatar

--
21 Nov 2012 10:19 AM

Does anyone know of a way to generate the insert statements (with or without the create table) from an existing file?

I could have sworn that it could be done with navigator. 

To clarify. I have an iSeries table. I want that data in mysql

An export was done but we're having trouble with the import. So I want to generate the insert statements from the existing table. It doesn't matter what method.

I swear that I've done this before but Hurricane sandy has my notes trapped 170 ft above the ground.

Joe

Craig R. Lockhart
Veteran Member
Veteran Member
Posts:1167
Avatar

--
21 Nov 2012 01:07 PM

This for creating a table via Ops Nav, but will give your fields in an editor:

  • Get into Databases
  • click on your schema
  • select Tables
  • right click on your Table
  • select "Generate SQL ..." from the pop up box
  • click Generate  (I would also select the "Open in Run SQL Scripts" radio button)
  • Enjoy!

That last step is important!  (lol)

HTH

Tommy Holden
Senior Member
Senior Member
Posts:2833
Avatar

--
21 Nov 2012 01:09 PM

not sure exactly what you are after but maybe this is what you are after?

INSERT INTO MYTABLE (SELECT * FROM OTHERTABLE)

 
Craig R. Lockhart
Veteran Member
Veteran Member
Posts:1167
Avatar

--
21 Nov 2012 01:15 PM
You may also try playing with "Data" then "Import..." from right clicking a table in Ops Nav.  I have not done it and do not know if it will give an option to generate an SQL statement, but it looks like it may worth the time to determine if that is true.
JJ Dahlheimer
Advanced Member
Advanced Member
Posts:626

--
21 Nov 2012 01:26 PM
I looked at the data options and could not find one that exported it as insert statements. only comma delimited and fixed length.
Lynne Noll
Senior Member
Senior Member
Posts:6567

--
27 Nov 2012 01:55 PM
... CPYTOIMPF will make the data in a given file into a delimited file, and then you can ftp over the delimited file, and import as delimited. I did a quick search, and people import delimited data into MYSQL with C Shell scripts that use commands available as part of the MYSQL environment. I'll leave you to the internet as I am not much of a C Shell person.
Jim_IT
Basic Member
Basic Member
Posts:217

--
27 Nov 2012 02:57 PM
SquirrelSQL has this functionality.

Create Data Script generates insert into statements for all the data.

Jim
You are not authorized to post a reply.

Acceptable Use Policy