使用“if Exists”条件生成现有行的插入脚本

来源：互联网发布：ubuntu u盘安装教程编辑：程序博客网时间：2024/06/06 03:34

介绍

数据传输是应用程序编程中最常见的任务之一。如果您在应用程序编程并且支持生产环境，您将很容易理解发送插入脚本在生产中运行，无论是配置条目还是数据更正脚本。

如果您要将数据脚本发送到生产环境，则需要格外小心。即使一个不正确的数据值也会导致完全的混乱。而且，这就是为什么建立这些脚本需要时间。

本文将提供一个实用程序存储过程，以生成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 formatIF 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')     ENDIF 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 formatIF 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')     ENDIF 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基于某些条件（WhereClause）从“ ”表生成所有插入的例子，If Exists在多个列的基础上用“ ”从句生成：

EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _@FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland''', @CheckColList = 'ShipCountry,ShipVia'/* Query will give result in this formatIF 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')     ENDIF 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 formatIF 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')     ENDIF 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 formatIF 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')     ENDIF 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 formatIF 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')     ENDIF 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”太长，就会失败，基本上变量不能处理大数据。我将尝试在未来的版本中解决此问题。
此处鸣谢@Chetan Naithani@千讯工作室

阅读全文

0 0