Sql的基础知识技巧,server批量插入与更新两种解

转自

.游标方式
 DECLARE @Data NVARCHAR(max)
 SET @Data='1,tanw;2,keenboy'   --Id,Name
 
 DECLARE @dataItem NVARCHAR(100)
 DECLARE data_cursor CURSOR FOR (SELECT * FROM split(@Data,';'))
 OPEN data_cursor
 FETCH NEXT FROM data_cursor INTO @dataItem   
 WHILE @@FETCH_STATUS=0
 BEGIN
 DECLARE @Id INT
 DECLARE @Name NVARCHAR(50)
 
 DECLARE dataItem_cursor CURSOR FOR (SELECT * FROM split(@dataItem,','))
 OPEN dataItem_cursor   
 FETCH NEXT FROM dataItem_cursor INTO @Id
 FETCH NEXT FROM dataItem_cursor INTO @Name
 CLOSE dataItem_cursor
 DEALLOCATE dataItem_cursor
 
 /*
   在这里做逻辑处理,插入或更新操作 ...
 */
 END
 
 CLOSE data_cursor
 DEALLOCATE data_cursor

三、技巧
1、1=1,1=2 的使用,在 SQL 语句组合时用的较多
“where 1=1” 是表示选择全部
“where 1=2”全部不选,
如:
if @strWhere !=''
begin set @strSQL = 'select count(*) as Total from [' @tblName '] where ' @strWhere
end
else
begin
set @strSQL = 'select count(*) as Total from [' @tblName ']'
end

1.游标方式

 

我们可以直接写成
set @strSQL = 'select count(*) as Total from [' @tblName ']
where 1=1 ' @strWhere

  

 

2、收缩数据库
--重建索引 DBCC REINDEX DBCC INDEXDEFRAG
--收缩数据和日志 DBCC SHRINKDB DBCC SHRINKFILE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE InsertCheckItemCategory
-- Add the parameters for the stored procedure here
@levelCode INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

.While方式
 DECLARE @Data NVARCHAR(max)
 SET @Data='tanw,keenboy'   --Id,Name
 
 DECLARE @Temp TABLE
 (
    Id INT IDENTITY(1,1),
    Name  NVARCHAR(50)
 )
 DECLARE @Id INT
 DECLARE @Name NVARCHAR(50)
 DECLARE @Results NVARCHAR(MAX) SET @Results=''
 INSERT INTO @Temp SELECT (SELECT * FROM split(@Data,';'))
 
 WHILE EXISTS(SELECT * FROM @Temp)
 BEGIN
     SELECT TOP 1 @Id=Id,@Name=Name from @Temp
     DELETE FROM @Temp where [id] = @Id
     SET @Results=@Results @Name ','
    
     /*
    
         在这里做逻辑处理,插入或更新操作 ...
    
     */
 END
 SELECT @Results

3、压缩数据库 dbcc shrinkdatabase(dbname)

DECLARE @newCategoryId UNIQUEIDENTIFIER
DECLARE @oldCategoryId NVARCHAR(50)
DECLARE @childLevelCode INT
SET @childLevelCode=@levelCode 1

 

4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname' go

DECLARE data_cursor CURSOR
FOR
( SELECT CheckItemCategory.Id AS newCategoryId ,
AppCheckItem.Id AS oldCategoryId
FROM CheckItemCategory
LEFT JOIN AppCheckItem ON CheckItemCategory.Descr = AppCheckItem.[Text]
WHERE LevelCode = @levelCode
) --使用游标的对象(跟据需要填入select文)
OPEN data_cursor --打开游标
FETCH NEXT FROM data_cursor INTO @newCategoryId, @oldCategoryId --将游标向下移1行,获取的数据放入之前定义的变量@id,@name中
WHILE @@fetch_status = 0 --判断是否成功获取数据
BEGIN
--进行相应处理(跟据需要填入SQL文)
INSERT INTO dbo.CheckItemCategory
( Id ,
Descr ,
OrderNo ,
LevelCode ,
ParentId ,
CreatedTime ,
UpdatedTime ,
IsDeleted
)
SELECT NEWID() ,
AppCheckItem.[Text] AS Descr ,
ROW_NUMBER() OVER(ORDER BY Id ASC) AS RowNumber,
@childLevelCode ,
@newCategoryId ,
GETDATE() ,
GETDATE() ,
0
FROM AppCheckItem
WHERE ParentId = @oldCategoryId AND Title=1
FETCH NEXT FROM data_cursor INTO @newCategoryId,
@oldCategoryId --将游标向下移1行
END

 

5、检查备份集 RESTORE VERIFYONLY from disk='E:dvbbs.bak'

CLOSE data_cursor --关闭游标
DEALLOCATE data_cursor
END
GO

 

6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO

    2.While方式

 //---------下面的方式比较适合----------//

7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

    复制代码代码如下:

 

USE tablename -- 要操作的数据库名
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(M)

    DECLARE @Data NVARCHAR(max)

 

Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' db_name() ' LOG is ' CONVERT(VARCHAR(30),@OriginalSize) ' 8K pages or ' CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)

    SET @Data='tanw,keenboy' --Id,Name

 

DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' db_name() ' WITH TRUNCATE_ONLY'

    DECLARE @Temp TABLE

BCP方式:

DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has
not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @Logical FileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans

    (

/// <summary>

SELECT @Counter = @Counter 1
END
EXEC (@TruncLog)
END
ELECT 'Final Size of ' db_name() ' LOG is '
CONVERT(VARCHAR(30),size) ' 8K pages or ' CONVERT(VARCHAR(30),(size*8/1024)) 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF

    Id INT IDENTITY(1,1),

/// 大批量插入数据(2000每批次)

8、说明:更改某个表
exec sp_changeobjectowner 'tablename','dbo'

    Name NVARCHAR(50)

/// 已采用整体事物控制

9、存储更改全部表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name
as NVARCHAR(128)
DECLARE @Owner
as NVARCHAR(128)
DECLARE @OwnerName
as NVARCHAR(128)

    )

/// </summary>

DECLARE curObject CURSOR FOR
select 'Name'= name, 'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0) BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner '.' rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner

    DECLARE @Id INT

/// <param name="connString">数据库链接字符串</param>

FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO

    DECLARE @Name NVARCHAR(50)

/// <param name="tableName">数据库服务器上目标表名</param>

10、SQL SERVER 中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i 1
end
案例:
有如下表,要求就裱中所有沒有及格的成績,在每次增長 0.1 的基础上,使他们刚好及格:
Name score
Zhangshan 80
Lishi 59
Wangwu 50
Songquan 69

    DECLARE @Results NVARCHAR(MAX) SET @Results=''

/// <param name="dt">含有和目标数据库表结构完全一致(所包含的字段名完全一致即可)的DataTable</param>

while((select min(score) from tb_table)<60)
begin
update tb_table set score =score*1.01
where score<60
if (select min(score) from tb_table)>60
break
else
continue
end

    INSERT INTO @Temp SELECT (SELECT * FROM split(@Data,';'))

public static void BulkCopy(``string connString, ``string tableName, DataTable dt)

    WHILE EXISTS(SELECT * FROM @Temp)

{

    BEGIN

``using (SqlConnection conn = ``new SqlConnection(connString))

    SELECT TOP 1 @Id=Id,@Name=Name from @Temp

``{

    DELETE FROM @Temp where [id] = @Id

``conn.Open();

    SET @Results=@Results @Name ','

 

    www.jb51.net

``using (SqlTransaction transaction = conn.BeginTransaction())

    /*

``{

    在这里做逻辑处理,插入或更新操作 ...

``using (SqlBulkCopy bulkCopy = ``new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))

    */

``{

    END

``bulkCopy.BatchSize = 2000;

    SELECT @Results

``bulkCopy.BulkCopyTimeout = _CommandTimeOut;

    如果是简单单表批量插入操作的,上面方法大可不必要

``bulkCopy.DestinationTableName = tableName;

 

``try

``{

``foreach (DataColumn col ``in dt.Columns)

``{

``bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);

 

``}

``bulkCopy.WriteToServer(dt);

``transaction.Commit();

``}

``catch (Exception ex)

``{

``transaction.Rollback();

``throw ex;

``}

``finally

``{

``conn.Close();

``}

``}

``}

``}

}

 

 

 

 

 

SqlDataAdapter:

/// <summary>

/// 批量更新数据(每批次5000)

/// </summary>

/// <param name="connString">数据库链接字符串</param>

/// <param name="table"></param>

public static void Update(``string connString, DataTable table)

{

``SqlConnection conn = ``new SqlConnection(connString);

``SqlCommand comm = conn.CreateCommand();

``comm.CommandTimeout = _CommandTimeOut;

``comm.CommandType = CommandType.Text;

``SqlDataAdapter adapter = ``new SqlDataAdapter(comm);

``SqlCommandBuilder commandBulider = ``new SqlCommandBuilder(adapter);

``commandBulider.ConflictOption = ConflictOption.OverwriteChanges;

``try

``{

``conn.Open();

``//设置批量更新的每次处理条数

``adapter.UpdateBatchSize = 5000;

``adapter.SelectCommand.Transaction = conn.BeginTransaction();``/////////////////开始事务

``if (table.ExtendedProperties[``"SQL"``] != ``null``)

``{

``adapter.SelectCommand.CommandText = table.ExtendedProperties[``"SQL"``].ToString();

``}

``adapter.Update(table);

``adapter.SelectCommand.Transaction.Commit();``/////提交事务

``}

``catch (Exception ex)

``{

``if (adapter.SelectCommand != ``null && adapter.SelectCommand.Transaction != ``null``)

``{

``adapter.SelectCommand.Transaction.Rollback();

``}

``throw ex;

``}

``finally

``{

``conn.Close();

``conn.Dispose();

``}

}

 

 

 

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:Sql的基础知识技巧,server批量插入与更新两种解

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。