on 2013 Oct 18 6:46 AM
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
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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..
One old trick that might help if this is a one off exercise is using Excel to create the statements.
Not elegant but handy on occasion 🙂
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...
User | Count |
---|---|
52 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.