扫一扫
关注微信公众号

SQL Server 2008批量删除及插入的方法
2009-05-21   

目前的批量删除和批量更新,很是烦人,解决方案无非三种:

  XML,SQL自定义函数split,和CLR实现split。这几种都比较烦人,代码很多,维护麻烦,很不爽。

  现在SQL Server 2008新增的一个功能,我也不知道中文名怎么翻译,暂且叫他表参数吧。

  大家可以看看示例:

  这个就是用户定义的表类型:

  然后给他定义一个类型:

Code
  -- ================================
  -- Create User-defined Table Type
  -- ================================
  USE Test
  GO
  -- Create the data type
  CREATE TYPE dbo.MyType AS TABLE
  (
  col1 int NOT NULL,
  col2 varchar(20) NULL,
  col3 datetime NULL,
  PRIMARY KEY (col1)
  )
  GO

这个是操作 表类型的脚本:

Code
  DECLARE @MyTable MyType
  INSERT INTO @MyTable(col1,col2,col3)
  VALUES (1,'abc','1/1/2000'),
  (2,'def','1/1/2001'),
  (3,'ghi','1/1/2002'),
  (4,'jkl','1/1/2003'),
  (5,'mno','1/1/2004')
  SELECT * FROM @MyTable

  下面演示如何将表参数作为一个存储过程参数传递,以及ADO.NET的代码

  sql部分:

Code
  USE [Test]
  GO
  CREATE TABLE [dbo].[MyTable] (
  [col1] [int] NOT NULL PRIMARY KEY,
  [col2] [varchar](20) NULL,
  [col3] [datetime] NULL,
  [UserID] [varchar] (20) NOT NULL
  )
  GO
  CREATE PROC usp_AddRowsToMyTable @MyTableParam MyType READONLY,
  @UserID varchar(20) AS
  INSERT INTO MyTable([col1],[col2],[col3],[UserID])
  SELECT [col1],[col2],[col3],@UserID
  FROM @MyTableParam
  GO

  如何在sql中调用此存储过程:

Code
  DECLARE @MyTable MyType
  INSERT INTO @MyTable(col1,col2,col3)
  VALUES (1,'abc','1/1/2000'),
  (2,'def','1/1/2001'),
  (3,'ghi','1/1/2002'),
  (4,'jkl','1/1/2003'),
  (5,'mno','1/1/2004')
  EXEC usp_AddRowsToMyTable @MyTableParam = @MyTable, @UserID = 'Kathi'
  SELECT * FROM MyTable

  其中还涉及到一个权限问题,需要执行以下代码:

Code
  GRANT EXECUTE ON TYPE::dbo.MyType TO TestUser;

  从.net app那调用此存储过程:

Code
  'Create a local table
  Dim table As New DataTable("temp")
  Dim col1 As New DataColumn("col1", System.Type.GetType("System.Int32"))
  Dim col2 As New DataColumn("col2", System.Type.GetType("System.String"))
  Dim col3 As New DataColumn("col3", System.Type.GetType("System.DateTime"))
  table.Columns.Add(col1)
  table.Columns.Add(col2)
  table.Columns.Add(col3)
  'Populate the table
  For i As Integer = 20 To 30
  Dim vals(2) As Object
  vals(0) = i
  vals(1) = Chr(i + 90)
  vals(2) = System.DateTime.Now
  table.Rows.Add(vals)
  Next

Code
  'Create a command object that calls the stored proc
  Dim command As New SqlCommand("usp_AddRowsToMyTable", conn)
  command.CommandType = CommandType.StoredProcedure
  'Create a parameter using the new type
  Dim param As SqlParameter = command.Parameters.Add("@MyTableParam", SqlDbType.Structured)
  command.Parameters.AddWithValue("@UserID", "Kathi")

Code
  'Set the value of the parameter
  param.Value = table
  'Execute the query
  command.ExecuteNonQuery()

  可以看到,生成的表类型的组成情况,并且居然可以给表类型建立索引,呵呵

热词搜索:

上一篇:SQL Server 2008中如何设置数据库审核规范(2)
下一篇:部署虚拟局域网 做好VLAN交换机的选购

分享到: 收藏
评论内容