cancel
Showing results for 
Search instead for 
Did you mean: 

Generate Script for Table Data

Former Member
7,244

Is there anyway to generate sql insert script for the data in a table in SQL Anywhere? I found the functionality for this in sybase central but it only created a script containing about 100 inserts, and that is not enough.

All good ideas are welcome

Accepted Solutions (0)

Answers (1)

Answers (1)

justin_willey
Participant

You don't say which version of SQL Anywhere you are using (select @@version will tell you if you aren't sure).

From v 11.0.0 onwards you can select a series of rows from the Result tab in Sybase Central, and right click to get an option to "Generate" Insert statements to the clipboard. Is this what you are doing?

There isn't anything in the docs to suggest a limitation, but the clipboard won't be of unlimited capacity. Are you selecting all the rows you want? It isn't limited to 100 - I've just managed 400 fine.

This is the only feature that I know of that generates INSERT statements. If you are just looking for ways to transfer data from one table to another, then I would look at the UNLOAD command. Unload can create either LOAD TABLE or INPUT statements as required. Using a SQL statement gives you the advantage of being able to create a reproducible script for the process, rather than relying on the operator getting it right every time. See UNLOAD syntax

Former Member
0 Kudos

it is SQL Anywhere 12.

Yes that is what I am doing, I have experimented some more with sybase central and I have managed the same amount, but I need alot more than that.

No I can not use UNLOAD it would have been so much simpler. Also "insert into select" would also work fine between tables, but that is not what I need to do.

well I guess I have to create a procedure or something which generates the inserts I need...written to a file..

justin_willey
Participant
0 Kudos

One old trick that might help if this is a one off exercise is using Excel to create the statements.

  • Unload the data to a csv file
  • Open in Excel, checking that everything has correctly parsed into columns
  • Create a formula concatenating a SQL statement together with & operators eg ="INSERT TO ATable (...) values('" & A1 & "', etc
  • Drag copy the formula down for all rows
  • Copy the column of statements into a text file

Not elegant but handy on occasion 🙂

VolkerBarth
Contributor

In a similar fashion, I often code SQL such as

SELECT 'INSERT INTO ATABLE VALUES(' || col1 || ', ' || col2 || ... ');'
FROM ATABLE
ORDER BY col1;

in DBISQL and use OUTPUT to a file (with appropriate delimeters). Not elegant, either, but worthwhile - and personally, I surely prefer that over a "copy a huge result set by hand" method. Just my 2 cents...

g_g_99
Participant
0 Kudos

Volker Barth has the great and best idea! for string column one may wish to ensure there is not collision with the desired delimiter seq like ', '