用于创建插入脚本的 SQL 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1820650/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL script to create insert script
提问by Ben Everard
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.
我正在编写一个 SQL 脚本来为我的数据库中表的每一行创建一个插入语句，纯粹是为了能够将该数据应用回另一个数据库。
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.
问题是如果其中一个字段为空，该行将无法生成更新脚本，在输出文件中该行只是空白。显然，由于有 20 多个字段，有些是可选的，这意味着我几乎没有生成任何脚本。
Is there a way to solve this issue?
有没有办法解决这个问题？
采纳答案by Chris Clark
In the case of NULL fields you can do something like
在 NULL 字段的情况下，您可以执行以下操作
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.
对于真正的空白字段（例如空 nvarchar），我相信您上面的脚本会起作用。
回答by Ben Everard
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.
你就完成了。它也更安全、更快。
回答by intgr
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):
使用quote_nullable()PostgreSQL 8.4 中的 new 函数。除了允许 NULL 值外，它还保留您的数据类型并保护您免受 Bobby 表（SQL 注入）的影响：
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():
在旧版本中，您可以使用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;
回答by Adam Gent
I wrote a python script based on @intgr answer to construct the select statement. It takes comma separated list of columns from stdin (use -).
我写了一个基于@intgr 答案的python 脚本来构造select 语句。它从标准输入中获取逗号分隔的列列表（使用 -）。
I wanted to use sqlparsebut I couldn't understand how to use that lib.
我想使用sqlparse但我无法理解如何使用该库。
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
脚本的要点在这里：https: //gist.github.com/2568047
