65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 SQL Server 从表数据生成 Insert 或 Update 语句

Bruce Chapman DNN
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.69/5 (24投票s)

2006年10月3日

CPOL

8分钟阅读

viewsIcon

323755

downloadIcon

3937

使用 SQL 脚本从包含数据的表中生成插入或更新脚本。

Source Code Output

引言

数据的转移是每个开发人员和 DBA 都需要的要求。无论是构建（或重建！）数据库、为发布迁移数据，还是仅仅创建测试数据，我至少总是会打开一个新的查询页面并输入“insert into ....”。我知道 SQL Server 2005 有很棒的小组件可以根据表创建插入语句，但我一直需要的是一种方法来根据不同数据库中现有表的数据生成更新或插入语句。有时，当只有一两行数据时，DTS / BCP 导入/导出作业会显得过于繁琐，或者由于安全限制您无法将文件传送到服务器附近。也许您就是喜欢做困难的事情。

您需要的是一个 T-SQL 脚本，该脚本可以读取表的内容，然后生成更新/插入语句，以便将这些数据插入到另一个服务器和数据库中的同一表中。您可以在任何有查询窗口的地方运行此脚本，而无需创建存储过程或安装任何程序。

背景

我一直都有针对特定表的简短脚本，但应朋友的要求，我决定认真对待并创建一个“一刀切”的 SQL 生成器，该生成器将为源表中的每一行生成一行 SQL，当在目标数据库上运行时，可以完美地复制数据。我知道市面上有一些工具可以做到这一点，但这个工具快速且非常容易使用。它还可以生成特定文本行的语句，或者仅仅为源表中的所有行生成语句。我认为这是一个非常有用的脚本，可以放在“我的 SQL 助手”文件夹中。

使用代码

这类项目的首要关注点是系统表。我一直很喜欢系统表，它们是这样一种奇特的情况：您正在使用的产品的结构由产品内的元数据描述。就像反射和其他自描述元数据一样，如果您想得太久，可能会有些“鸡生蛋，蛋生鸡”的感觉。

您需要做的第一件事是获取目标表中列的列表以及每个列的数据类型。这可以通过一段相当简单的 SQL 来完成。

SELECT so.name, sc.name, st.name, sc.length
, Case when sc.status = 0x80 then 'Y' else 'N' END as IsIdent
, ColOrder
FROM sysobjects so
INNER JOIN syscolumns sc
  ON so.id= sc.id 
INNER JOIN systypes st
  ON sc.xtype = st.xusertype 
WHERE so.Name = 'SourceTableName'
ORDER BY ColOrder

这将为您提供源表中的列列表，顺序与数据库中的顺序相同。这是提取数据的基本结构。带有 Case 语句的棘手列会检查 Status 二进制列，并根据该列是否为标识列生成 Y/N - 我们稍后会讨论这一点。

提取信息的问题在于我们正在处理循环集 - 这在 SQL Server 中（至少在 .NET 之前）很难做到。为了整理所需的结果，将创建一个临时表。这个临时表将包含脚本的实际输出。另一个临时表被创建为每列数据的暂存点。

create table #output (Line varChar(4000), LineOrder int)
-- Holds the output for the script

create table #ColumnValues (ColName varChar(250), ColOrder int, RowNumber int
                           ,ColValue varchar(4000), ColType varchar(50)) 
 -- Holds the values for each column/row combination

工作算法

基本算法是

for each column in the source table

  if the column is not an identity column

    insert into the #ColumnValues table the column name 
           and the value from the source table
  end if
end for


for each row in the #ColumnValues table
   while each column in the table belonged 
   to the same row in the source table
   concatenate the update/insert statement 
   together into a string variable

   end while

   at the end of each column set for a row

   if an insert statement desired

     create the insert statement specific text

   else
    create the update statement specific text

   end if
end for


select all rows from the #Output table

当翻译成 T-SQL 时，这个基本算法有一些复杂的部分 - 最概念上困难的是从表中的特定列中选择值。问题在于您需要动态 SQL - 这没问题 - 但您需要将结果存储到某种变量中。您不能使用本地声明的 T-SQL @ 变量，因为它们不在动态 SQL 的作用域内。这就是为什么改用临时表的原因。这个临时表可以在动态 SQL 中使用，如下所示：

exec ('insert into #ColumnValues (ColName, ColOrder, ColValue, ColType)

select ''' + @colName + ''', ' + @ColOrder + '
      , Convert(nvarchar(4000),' + @colName + ') , ''' + @colType +''' 
from ' + @tabName + ' order by ' + @SortCol + ' ' + '
declare @counter int
set @counter = 0 ' + '
update #ColumnValues Set @Counter = RowNumber = @Counter + (' + @numCols + ' * 10)
where ColName = ''' + @colName + '''' )

这段看起来有点吓人的代码做了几件事情。@colName 变量取自游标循环，它只是源表中列的名称（@colType 和 @tabName 也是如此）。代码一次处理表中的一列 - 就像学习阅读一样 - 从左到右，然后移到下一行，依此类推… insert 语句获取从源表特定列读取的值，并将从源表读取的值插入到临时表中（#columnValues）。下一行声明一个计数器，然后逐步分配源表的 RowNumber - 这是为了将同一行的所有列保持在一起 - 否则，结果会乱七八糟，不同列的值会与错误的 `primary key` 匹配 - 就像棋盘游戏意外被撞倒，所有棋子都在错误的位置一样。最后的 update 语句使用了非常晦涩的 Set @value = Column = @Value + number 语法 - 这是 SQL Server 的一个隐藏秘密，它允许您为表中的每一行更新一个运行计数器。您可以尝试弄清楚它是如何工作的，或者接受它就是这样然后继续。

行计数器稍后在从 #ColumnValues 表中的值创建 insert/update 语句时使用。它用作循环开关，因此代码知道何时第一列结束以及下一列何时开始。这是因为原始表的结构

row1 : col1 col2 col3 col4 
row2 : col1 col2 col3 col4

现在在表中表示为

row1 col1
row1 col2
row1 col3
row1 col4
row2 col1
row2 col2
etc....

在每列之后，都会在循环后检查行计数器（@RowNumber），以确定我们是否已切换行 - 即，从行 1 切换到行 2。每次从表中读取行切换时，insert/update 语句就会完成，然后开始新的语句。每次完成一个 insert/update 语句后，它都会插入到结果表中。脚本完成后，所有语句都将从临时表中读取，结果将为您提供一个漂亮的 insert 语句列表，然后您可以将其执行到您选择的服务器/数据库中（当然，前提是该数据库中存在完全相同的表定义）。

要运行脚本，只需在 Query Editor 或 SQL Server Manager 中加载它。将 @tab 变量编辑为源表的名称，并将“INSERT”或“UPDATE”键入 @StatementType 变量。如果您希望执行更新语句或执行单行插入语句，请为此表的主键列输入其值（目前，这仅适用于单列主键）以及主键列的名称。

Source Code Example

上面的示例显示了准备运行的脚本 - 用于名为“PaymentStatusTest”的表的全部行插入语句。

这是“PaymentStatusTest”表的结构

Example Table Column list

执行脚本，然后将查询结果窗口中的结果复制到您选择的位置。将 Query Editor 运行为“文本”输出很有帮助。

关注点

此脚本可以轻松扩展以处理更复杂的情况，例如生成表的更新语句列表（尽管删除所有/插入可能更好？），或处理多个主键，或跨主键的部分匹配。我还考虑过让脚本自动检测主键，但这增加了很多复杂性，但带来的好处却很小。

任何经常使用 SQL Server 的人都应该了解系统表中内置元数据的优缺点 - 只要稍加调整、一些横向思维和巧妙的脚本，几乎没有什么事情是无法完成的。您也可以将其转换为存储过程，但我更喜欢 SQL 脚本的即时编辑和继续风格。

更新

  • 2007/4/5：在下载源中添加了一个第二个脚本，它增加了一个额外的步骤，可以整理 insert/update 语句并将它们写出来，以便每行输出一个 SQL 语句。这在为整个表生成大量语句时更有用。还根据收到的反馈添加了对 varchar、char 和 text 字段的支持。
  • 2007/11/13：我修复了我自己发现的以及在评论区报告的一些错误。这些包括：指定表所有者的能力、最后一行被附加到倒数第二行、不处理数据中的 ' 字符。我还添加了允许插入语句到具有身份列的表的功能，并添加了指定 'where' 子句以生成表子集数据的插入/更新语句的能力。我还添加了一个小的（已注释掉的）部分，它将生成一个进度报告，以便您知道脚本仍在为非常大的表工作。只需取消注释该部分，它每 5000 行就会报告一次。我曾经连续运行了 3 个小时 - 幸运的是，我没有 DBA 可以汇报！感谢所有提供反馈并帮助我改进脚本的人。
© . All rights reserved.