如何自动处理SQL Server 2005表格数据-技术开发专区

如何自动处理SQL Server 2005表格数据

作者：小强编辑： nancy 2008-01-03 15:59

【IT168技术文档】
最近我的一个朋友也是同事面临一个有趣的问题，他每个月从客户那里收到大量的数据，这些数据必须先经过处理才能使用，数据表包含成千上万行，并且表结构可能每个月都不相同。更糟糕的是，大量的数据包含重要的或无用的空格或者两者都有。

在我的朋友第一次用手工做完这项工作后，他很快发现手工处理实在是一件令人厌烦的事情。他在想是否存在一个自动处理数据的方法，问题是他不能预先知道表格有多少列或者每列的列名。幸运的是，自动处理数据表数据是有可能的。

在SQL Server 2005的INFORMATION_SCHEMA模式中有大量的有用的系统视图，本文感兴趣的视图是INFORMATION_SCHAMA.COLUMNS。

所有的系统视图在你查看时都是隐藏的，所以你不能简单地右击然后到查询窗体查看脚本。微软是故意这样做的，以防止对内部结构的改变。如果你对系统表有所了解，你可以猜测这些视图是如何构建的，但是，在本文中内部结构对我们并不重要。

然而，你可以获得大量关于表格中每一列的信息，下面是一些有趣的信息：

Table_Catalog：数据库名
Table_Schema：模式
Table_Name：表名
Column_Name：列名
Ordinal_Position：列数
Column_Default：默认值
Is_Nullable：有无数据
Data_Type：指明该列的数据类型
查询任何给定表的列数，你可以用类似以下代码：

USE AdventureWorksSELECT*FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME ='ProductDescription'

在你的数据库中试用这个查询命令，替换它的名称和感兴趣的数据表。运行它查看输出结果。

现在，我将用SQL产生UPDATE语句用来处理数据表中的所有数据。我通过收集感兴趣的数据表中每一列的文字描述来实现。我将忽略和我们的需要不相关的数据类型，而集中于char和varchar这两种类型，请参考列表A。这个命令的输出是一系列和出现在列表B中的类似UPDATE语句，你需要做的就是收集这个输出然后作为一个查询运行语句块。

在上面的例子中，我只对一个数据表进行处理，但是如果我想在一个模式中处理所有数据表，我只要简单的改一下WHERE条件语句的table_name用Schema_Name来代替，请看列表C。查看命令的输出结果请看列表D。

这揭示了一个小问题，当用命名模式表示时要包含一个视图，这使得当已经更新了视图所关联的数据表后，在试图更新视图变得毫无意义。所以我需要找一种方法略过视图。但是仅仅查看INFORMATION_SCHEMA.COLUMS视图,并没有方法避免这个问题，因为该视图不能从不同的数据表区分开来。

另一个视图INFORMATION_SCHEMA.Tables可以帮助我们解决上述问题。我只需要在上面的查询中添加存在量词，请看列表E，这个版本的查询从列表中去除了视图来进行处理。

我的同事现在很高兴，因为这一解决方法很简单并且灵活好用。只要改变where条件语句，就可以对一个表或所有表用一个模式，甚至可以用于数据库中的所有表。

正如你看到的，SQL Server2005使得处理数据表和列信息比在SQL Server 2000中更简单，我还演示了用SQL如何产生SQL代码。现在你知道如何将一件繁重的任务变成轻而易举的小事了。

列表A：

USE AdventureWorks
SELECT 'Update '+ Table_Name +' SET '+ Column_Name +' = Ltrim(RTrim( '+ Column_Name +'))'
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME ='Contact'
AND Data_Type IN('char','varchar','nchar','nvarchar')

列表B：

Update Contact SET Title =Ltrim(RTrim( Title))
Update Contact SET FirstName =Ltrim(RTrim( FirstName))
Update Contact SET MiddleName =Ltrim(RTrim( MiddleName))
Update Contact SET LastName =Ltrim(RTrim( LastName))
Update Contact SET Suffix =Ltrim(RTrim( Suffix))
Update Contact SET EmailAddress =Ltrim(RTrim( EmailAddress))
Update Contact SET Phone =Ltrim(RTrim( Phone))
Update Contact SET PasswordHash =Ltrim(RTrim( PasswordHash))
Update Contact SET PasswordSalt =Ltrim(RTrim( PasswordSalt))

列表C：

USE AdventureWorks
SELECT 'Update '+ Table_Name +' SET '+ Column_Name +' = Ltrim(RTrim( '+ Column_Name +'))'
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_SCHEMA ='Production'
AND Data_Type IN('char','varchar','nchar','nvarchar')

列表D：

Update Assemblies SET AssemblyName =Ltrim(Trim( AssemblyName))
Update Components SET ComponentName =Ltrim(Trim( ComponentName))
Update Assemblies_And_Components_v1 SET AssemblyName =Ltrim(Trim( AssemblyName))
Update Assemblies_And_Components_v1 SET ComponentName =Ltrim(Trim( ComponentName))

列表E：

USE AdventureWorks
SELECT 'Update '+ Table_Name +' SET '+ Column_Name +' = Ltrim(Trim( '+ Column_Name +'))'
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_SCHEMA ='Production'
AND Data_Type IN('char','varchar','nchar','nvarchar')
and EXISTS(SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES T
WHERE C.Table_Name = T.Table_Name AND Table_Type <>'VIEW')