自动处理SQL Server 2005表格数据

时间:2009-11-15 12:15:54  来源:第二电脑网  作者:第二电脑网

  第二电脑网导读:相同。更糟糕的是,大量的数据包含重要的或无用的空格或者两者都有。在我的朋友第一次用手工做完这项工作后,他很快发现手工处理实在是一件令人厌烦的事情。他在想是否存在一个自动处理数据的方法,问题是他不能预先知道表格有多少列或者每列的列名。幸运的是,自动处理数据表数据是有可能的。在SQL Server 2005的INFORMATION_SCHEMA模式中有大量的有用的系统视图,本...
  正文:

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

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

在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。

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语句,你需要做的就是收集这个输出然后作为一个查询运行语句块。----www.002pc.com

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))

在上面的例子中,我只对一个数据表进行处理,但是如果我想在一个模式中处理所有数据表,我只要简单的改一下WHERE条件语句的table_name用Schema_Name来代替,请看列表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))

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

另一个视图INFORMATION_SCHEMA.Tables可以帮助我们解决上述问题。我只需要在上面的查询中添加存在量词,请看列表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')

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

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

"自动处理SQL Server 2005表格数据"由第二电脑网原创提供,转载请注明:http://www.002pc.com/master/College/Database/SQLServer/2009-01-07/8118.html


关键字:

关于《自动处理SQL Server 2005表格数据》文章的评论

站内搜索: 高级搜索

热门搜索: Windows style 系统 tr IP QQ CPU 安装 function 注册 if td