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

asked 18 Oct '13, 05:46

M%20G's gravatar image

M G
629253044
accept rate: 42%

edited 18 Oct '13, 08:13

Justin%20Willey's gravatar image

Justin Willey
6.8k111145214


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

permanent link

answered 18 Oct '13, 08:12

Justin%20Willey's gravatar image

Justin Willey
6.8k111145214
accept rate: 20%

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..

(18 Oct '13, 08:38) M G
Replies hidden

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 :)

(18 Oct '13, 09:26) Justin Willey
2

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...

(18 Oct '13, 09:40) Volker Barth

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 ', '

(21 Oct '13, 21:47) gg99
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×103
×29
×28

question asked: 18 Oct '13, 05:46

question was seen: 2,412 times

last updated: 21 Oct '13, 21:47