介绍
数据传输是应用程序编程中最常见的任务之一。如果您在应用程序编程并且支持生产环境,您将很容易理解发送插入脚本在生产中运行,无论是配置条目还是数据更正脚本。
如果您要将数据脚本发送到生产环境,则需要格外小心。即使一个不正确的数据值也会导致完全的混乱。而且,这就是为什么建立这些脚本需要时间。
本文将提供一个实用程序存储过程,以生成insert
很多灵活性。您将能够生成脚本:
- 从任何你想要的桌子上
- 根据条件
- 使用
If Exists
基于您提供的列的“ ”子句 - 包括或排除标识列值
- 根据您的输入排除列
背景
像你一样,我也经历了这些痛苦。虽然有一些免费的选项可用,如SQL Server的“生成脚本”工具,以及一些在互联网上发表的文章/选项。但是,我需要一些工具/实用程序来获得更多的生成插入命令。基本上,我需要对现有的工具/实用程序进行以下补充:
- 我应该能够根据任何条件生成脚本,例如我们可以根据条件选择任意数量的行。
- 我应该能够用“
If Exists
”子句生成脚本,因为我不确定数据是否已经存在于产品中。 - 我可能希望排除排除标识列值。
- 我可能想排除一些列(例如 - 系统列可能是默认的)
使用代码
注意:对于我们所有的例子,我们使用“Northwind”数据库。
若要使用存储过程(usp_CreateInserts
)生成插入,请按照下列步骤操作:
- 从这篇文章下载附件。
- 打开SSMS并在数据库中运行usp_CreateInserts.SQL。
从“ Orders
”表生成所有插入的例子:
EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', @FromAndWhere = 'FROM Orders'
/* Query will give result in this format
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE 1=1)
BEGIN
INSERT INTO [Orders] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],_
[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],[ShipPostalCode],_
[ShipCountry])VALUES(10248,'VINET',5,'Jul 4 1996 12:00:00:000AM',_
'Aug 1 1996 12:00:00:000AM','Jul 16 1996 12:00:00:000AM',3,32.3800,_
'Vins et alcools Chevalier','59 rue de l''Abbaye','Reims',NULL,'51100','France')
END
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE 1=1)
BEGIN
INSERT INTO [Orders] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],_
[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],[ShipPostalCode],_
[ShipCountry])VALUES(10249,'TOMSP',6,'Jul 5 1996 12:00:00:000AM',_
'Aug 16 1996 12:00:00:000AM','Jul 10 1996 12:00:00:000AM',1,11.6100,_
'Toms Spezialitäten','Luisenstr. 48','Münster',NULL,'44087','Germany')
END
.
.
.
*/
Orders
根据某些条件(“ Where
子句”)从“ ”表生成所有插入的示例:
EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _
@FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland'''
/* Query will give result in this format
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipCountry]='Germany')
BEGIN
INSERT INTO [Orders] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
[ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],_
[ShipPostalCode],[ShipCountry])VALUES(10249,'TOMSP',6,'Jul 5 1996 12:00:00:000AM',_
'Aug 16 1996 12:00:00:000AM','Jul 10 1996 12:00:00:000AM',1,11.6100,_
'Toms Spezialitäten','Luisenstr. 48','Münster',NULL,'44087','Germany')
END
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipCountry]='France')
BEGIN
INSERT INTO [Orders] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
[ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],_
[ShipPostalCode],[ShipCountry])VALUES(10248,'VINET',5,'Jul 4 1996 12:00:00:000AM',_
'Aug 1 1996 12:00:00:000AM','Jul 16 1996 12:00:00:000AM',3,32.3800,_
'Vins et alcools Chevalier','59 rue de l''Abbaye','Reims',NULL,'51100','France')
END
.
.
.
*/
Orders
基于某些条件(Where
Clause)从“ ”表生成所有插入的例子,If Exists
在多个列的基础上用“ ”从句生成:
EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _
@FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland''', @CheckColList = 'ShipCountry,ShipVia'
/* Query will give result in this format
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipVia]=2 AND [ShipCountry]='Switzerland')
BEGIN
INSERT INTO [Orders] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
[ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],_
[ShipPostalCode],[ShipCountry])VALUES(10254,'CHOPS',5,'Jul 11 1996 12:00:00:000AM',_
'Aug 8 1996 12:00:00:000AM','Jul 23 1996 12:00:00:000AM',2,22.9800,_
'Chop-suey Chinese','Hauptstr. 31','Bern',NULL,'3012','Switzerland')
END
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipVia]=3 AND [ShipCountry]='Switzerland')
BEGIN
INSERT INTO [Orders] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
[ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],_
[ShipPostalCode],[ShipCountry])VALUES(10255,'RICSU',9,'Jul 12 1996 12:00:00:000AM',_
'Aug 9 1996 12:00:00:000AM','Jul 15 1996 12:00:00:000AM',3,148.3300,_
'Richter Supermarkt','Starenweg 5','Genève',NULL,'1204','Switzerland')
END
.
.
.
*/
从“ Orders
”表生成标识列生成所有插入的示例:
EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _
@FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland''', _
@CheckColList = 'ShipCountry,ShipVia',@OmitIdentity =1
/* Query will give result in this format
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipVia]=2 AND [ShipCountry]='Switzerland')
BEGIN
INSERT INTO [Orders] ([CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
[ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],_
[ShipRegion],[ShipPostalCode],[ShipCountry])VALUES('CHOPS',5,_
'Jul 11 1996 12:00:00:000AM','Aug 8 1996 12:00:00:000AM',_
'Jul 23 1996 12:00:00:000AM',2,22.9800,'Chop-suey Chinese','Hauptstr. 31',_
'Bern',NULL,'3012','Switzerland')
END
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipVia]=3 AND [ShipCountry]='Switzerland')
BEGIN
INSERT INTO [Orders] ([CustomerID],[EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],_
[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],_
[ShipPostalCode],[ShipCountry])VALUES('RICSU',9,'Jul 12 1996 12:00:00:000AM',_
'Aug 9 1996 12:00:00:000AM','Jul 15 1996 12:00:00:000AM',3,148.3300,_
'Richter Supermarkt','Starenweg 5','Genève',NULL,'1204','Switzerland')
END
.
.
.
*/
从“ Orders
”表生成顶部“n”插入的例子:
EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _
@FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland''', _
@CheckColList = 'ShipCountry,ShipVia',@Top = 10
从“ Orders
”表生成所有插入的例子留下一些列。在这个例子中,我们排除了“ OrderId
”和“ EmployeeID
”列:
EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _
@FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland''', _
@CheckColList = 'ShipCountry,ShipVia',@ExcludeColList = '''OrderID'',''CustomerID'''
/* Query will give result in this format
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipVia]=2 AND [ShipCountry]='Switzerland')
BEGIN
INSERT INTO [Orders] ([EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],_
[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],_
[ShipPostalCode],[ShipCountry])VALUES(5,'Jul 11 1996 12:00:00:000AM',_
'Aug 8 1996 12:00:00:000AM','Jul 23 1996 12:00:00:000AM',2,22.9800,_
'Chop-suey Chinese','Hauptstr. 31','Bern',NULL,'3012','Switzerland')
END
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipVia]=3 AND [ShipCountry]='Switzerland')
BEGIN
INSERT INTO [Orders] ([EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],[ShipVia],_
[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],[ShipPostalCode],_
[ShipCountry])VALUES(9,'Jul 12 1996 12:00:00:000AM','Aug 9 1996 12:00:00:000AM',_
'Jul 15 1996 12:00:00:000AM',3,148.3300,'Richter Supermarkt','Starenweg 5',_
'Genève',NULL,'1204','Switzerland')
END
.
.
.
从“ Orders
”表中生成所有具有所有者名称的插入对象的示例:
EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _
@FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland''', _
@CheckColList = 'ShipCountry,ShipVia',@Owner = 'dbo'
/* Query will give result in this format
IF NOT EXISTS(SELECT TOP 1 1 FROM [dbo].[Orders] WHERE [ShipVia]=2 AND [ShipCountry]='Switzerland')
BEGIN
INSERT INTO [[dbo].[Orders]] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
[ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],_
[ShipRegion],[ShipPostalCode],[ShipCountry])VALUES(10254,'CHOPS',5,_
'Jul 11 1996 12:00:00:000AM','Aug 8 1996 12:00:00:000AM',_
'Jul 23 1996 12:00:00:000AM',2,22.9800,'Chop-suey Chinese','Hauptstr. 31',_
'Bern',NULL,'3012','Switzerland')
END
IF NOT EXISTS(SELECT TOP 1 1 FROM [dbo].[Orders] WHERE [ShipVia]=3 AND [ShipCountry]='Switzerland')
BEGIN
INSERT INTO [[dbo].[Orders]] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
[ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],_
[ShipRegion],[ShipPostalCode],[ShipCountry])VALUES(10255,'RICSU',9,_
'Jul 12 1996 12:00:00:000AM','Aug 9 1996 12:00:00:000AM',_
'Jul 15 1996 12:00:00:000AM',3,148.3300,'Richter Supermarkt','Starenweg 5',_
'Genève',NULL,'1204','Switzerland')
END
.
.
.
*/
兴趣点
- 如果“
@CheckColList
”太长,就会失败,基本上变量不能处理大数据。我将尝试在未来的版本中解决此问题。 - 此处鸣谢@