CODEWITHSUNDEEP
sqlpostgresql
Ben Everard
Ben Everard

Reputation: 13804

SQL script to create insert script

A bit of a vague title, I will explain.

I am writing an SQL script to create an insert statement for each row of a table in my database, purely to be able to apply that data back to another database.

Here is what I have at the moment:

SELECT 'INSERT INTO products (id,name,description) VALUES ('||ID||','''||name||''','''||description||''');' FROM products

And it works great, outputting this:

INSERT INTO products (id,name,description) VALUES (1,'Lorem','Ipsum');
INSERT INTO products (id,name,description) VALUES (2,'Lorem','Ipsum');
INSERT INTO products (id,name,description) VALUES (3,'Lorem','Ipsum');
INSERT INTO products (id,name,description) VALUES (4,'Lorem','Ipsum');

The problem is if one of the fields is empty that row will fail to produce an update script, in the output file the line is just blank. Obviously as there are 20+ fields, some optional this means that hardly any of my scripts are generated.

Is there a way to solve this issue?

Upvotes: 13

Views: 29836

Answers (4)

Adam Gent
Adam Gent

Reputation: 49095

I wrote a python script based on @intgr answer to construct the select statement. It takes comma separated list of columns from stdin (use -).

I wanted to use sqlparse but I couldn't understand how to use that lib.

import fileinput

names = ' '.join(fileinput.input())

ns = [x.strip() for x in names.split(',')]
quoted = ['quote_nullable(' + x + ')' for x in ns]
insert = "SELECT  'INSERT INTO <TABLE> ( " + (', ').join(ns) + " ) VALUES(' || " + (" || ',' || ").join(quoted)  + " || ');' FROM <TABLE>"
print insert

A gist of the script is here: https://gist.github.com/2568047

Upvotes: 1

user80168
user80168

Reputation: 

pg_dump -a -U user1 -t products -f products.copy database1

and then:

psql -U user2 -d database2 -f products.copy

and you're done. It's also safer and faster.

Upvotes: 13

intgr
intgr

Reputation: 20496

Use the quote_nullable() function new in PostgreSQL 8.4. In addition to permitting NULL values, it retains your data types and protects you from Bobby Tables (SQL injections):

SELECT 'INSERT INTO products (id,name,description) VALUES (' ||
quote_nullable(ID) || ',' || quote_nullable(name) || ',' ||
quote_nullable(description) || ');' FROM products;

In older versions, you get the same behavior with coalesce() and quote_literal():

SELECT 'INSERT INTO products (id,name,description) VALUES (' ||
coalesce(quote_literal(ID), 'null') || ',' ||
coalesce(quote_literal(name), 'null') || ',' ||
coalesce(quote_literal(description), 'null') || ',' ||
');' FROM products;

Upvotes: 5

Chris Clark
Chris Clark

Reputation: 4814

In the case of NULL fields you can do something like

Select COALESCE(Name, '') from...

The coalesce function returns the first nonnull value in the list.

For truly blank fields (empty nvarchar for instance) I believe your script above will work.

Upvotes: 5

Related Questions