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