Export Data as Insert Statements

[bud]

Hi,

Is it possible to export data from a table into a script in the form of insert statements? This is an nice feature in MySQL. I would like to have backup scripts that I can use to easily reload dimension tables.

Thanks!!!

[JimKnicely]

Hi Bud,

There isn't an easy way to export data as insert statements. But you really don't have to do that if you want a backup of the data. You can simply save the data as a csv (where the character is a pipe symbol by default) file and later load it with the copy command.

Check out this link to see if it helps:

http://www.vertica-forums.com/viewtopic.php?f=10&t=150

[bud]

Thank you for responding to my question, Jim. The method of extracting the data into a flat file and then loading it later via the copy command will work for us.

You said "There isn't an easy way to export data as insert statements.", so what's the hard way

[JimKnicely]

Here is one hard way:

Code: Select all

dbadmin=> SELECT * FROM test.insert_test;
 c1 |  c2   |     c3
----+-------+------------
  3 | Josh  | 2005-01-01
  2 | Jane  | 1998-02-14
  1 | Jim   | 1982-09-19
  4 | Helen | 2012-08-25
(4 rows)

Code: Select all

[dbadmin@vertica01 ~]$ vsql -At -c "SELECT '\! vsql -At -F \"'',''\" -c \"SELECT ''INSERT INTO ' || table_schema || '.' || table_name || ' VALUES ('' c1, *, '');'' c2 FROM ' || table_schema || '.' || table_name || ';\" | sed -e \"s/('',/(/\" | sed \"s/,'')/)/\"' FROM tables WHERE table_schema = 'test' AND table_name = 'insert_test';" | vsql
INSERT INTO test.insert_test VALUES ('2','Jane','1998-02-14');
INSERT INTO test.insert_test VALUES ('3','Josh','2005-01-01');
INSERT INTO test.insert_test VALUES ('1','Jim','1982-09-19');
INSERT INTO test.insert_test VALUES ('4','Helen','2012-08-25');

This is just a super simple example. You'd have to worry about data that has apostrophes in it and deal with those...