澳门新浦京娱乐场网站-www.146.net-新浦京娱乐场官网
做最好的网站

澳门新浦京娱乐场网站积存进度,动态给表增多

转自

.游标形式
 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

三、技巧
一、一=一,一=二 的运用,在 SQL 语句组合时用的较多
“where 一=1” 是象征选取任何
“where 1=二”全体不选,
如:
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

有二个表,用户要求在后台操作它,希望能对它动态进展加多删减字段。那些意义大概没反常,但是它原本插入与创新的八个存款和储蓄进度,也亟需壹块修改。因而Insus.NET完结了它,由此此文少禽令你打探到什么动态为1个表加多删除字段以及动态修改它的仓储进程。

一.游标形式

 

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

首先需求建2个表[A],这一个表唯有多个字段,二个是[ID]机关增加,另三个是表[B]的字段名,存款和储蓄的每单笔记录,便是用户须要操作的表[B]的字段。这个表[A]急需建添加,更新,以及去除的贮存过程,方便用户在后台方便操作,还有入眼部分,须要写触发器。如有记录对表[A]开始展览增加,更新或是删除时,它会触发去作表[B]对应操作,还要去修改表[B]的囤积进程。

  

 

二、缩短数据库
--重建索引 DBCC REINDEX DBCC INDEXDEFRAG
--缩短数据和日志 DBCC SH奥迪Q5INKDB DBCC SH瑞虎INKFILE

动态修改表[B]的存款和储蓄进度:

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

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

澳门新浦京娱乐场网站 1澳门新浦京娱乐场网站 2View Code

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

 

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

CREATE PROCEDURE [dbo].[usp_B_DymanicallyAlterStoreProcedure]
AS
    DECLARE @VariableList NVARCHAR(MAX) = ''  
    DECLARE @FieldList NVARCHAR(MAX) = '' 
    DECLARE @ValueList NVARCHAR(MAX) = ''  
    DECLARE @FieldValueList NVARCHAR(MAX) = ''
    
    DECLARE @I INT = 1, @R INT = 0
    SET @R = (SELECT MAX([Id]) FROM [dbo].[A])
    WHILE (@I <= @R)
    BEGIN
        DECLARE @fName NVARCHAR(100)
        IF EXISTS(SELECT [Id] FROM [dbo].[A] WHERE [Id] = @I)
        BEGIN
            SELECT @fName = [FieldName] FROM [dbo].[A] WHERE [Id] = @I
            SET @VariableList = @VariableList   ',@'   @fName  ' DE君威L(1八,肆)' --动态的字段数据类型都大同小异
            SET @FieldList =  @FieldList   ',['   @fName   ']'
            SET @ValueList = @ValueList   ',@'   @fName
            SET @FieldValueList = @FieldValueList   ',['   @fName   '] = @'   @fName
        END
        SET @I = @I   1
    END

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 --将游标向下移一行,获取的多寡放入事先定义的变量@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 --将游标向下移一行
END

 

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

    DECLARE @sql_I NVARCHAR(MAX),@sql_U NVARCHAR(MAX)
    SET @sql_I = '
    ALTER PROCEDURE [dbo].[usp_B_Insert]
    (
        @ItemCode NVARCHAR(50) 
        '  @VariableList  '
    )
    AS    
    INSERT INTO [dbo].[B] ([ItemCode]'  @FieldList  ') VALUES (@ItemCode'  @ValueList  ')
    '
    EXECUTE sp_EXECUTESQL @sql_I;
    
    SET @sql_U = '
    ALTER PROCEDURE [dbo].[usp_B_Update]
    (
        @Id INT,
        @ItemCode NVARCHAR(50)
        '  @VariableList  '
    )
    AS    
    UPDATE [dbo].[B] SET [ItemCode] = @ItemCode'  @FieldValueList  ' WHERE [Id] = @Id
    '
    EXECUTE sp_EXECUTESQL @sql_U;

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

 

陆、修复数据库
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

表[A]的插入触发器:

    复制代码代码如下:

 

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

澳门新浦京娱乐场网站 3澳门新浦京娱乐场网站 4View Code

    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)

CREATE TRIGGER [dbo].[tri_A_Insert] ON [dbo].[A]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @FieldName NVARCHAR(50) 
    SELECT @FieldName = [FieldName] FROM INSERTED

    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'

    EXECUTE('IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE [id] = OBJECT_ID(''B'') AND [name] = '''  @FieldName  ''')    
    ALTER TABLE [B] ADD ['  @FieldName  '] DECIMAL(18,4) NULL')

    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

    EXECUTE [dbo].[usp_B_DymanicallyAlterStoreProcedure];
END

    (

/// <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每批次)

八、表达:改变有些表
exec sp_changeobjectowner 'tablename','dbo'

表[A]删除触发器:

    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)

澳门新浦京娱乐场网站 5澳门新浦京娱乐场网站 6View Code

    )

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

CREATE TRIGGER [dbo].[tri_A_Delete] ON [dbo].[A]
FOR DELETE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @FieldName NVARCHAR(50) 
    SELECT  @FieldName = [FieldName] FROM DELETED

    DECLARE @Id INT

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

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

    EXECUTE('IF EXISTS(SELECT * FROM SYSCOLUMNS WHERE [id] = OBJECT_ID(''B'') AND [name] = '''  @FieldName  ''')    
    ALTER TABLE [B] DROP COLUMN ['  @FieldName  ']')
    
    EXECUTE [dbo].[usp_B_DymanicallyAlterStoreProcedure];
END

    DECLARE @Name NVARCHAR(50)

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

10、SQL SELANDVE揽胜 中央直机关接循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i 1
end
案例:
有如下表,要求就裱中具有沒有及格的成績,在每便增長 0.壹的底子上,使她们正好及格:
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();

``}

}

 

 

 

 

本文由澳门新浦京娱乐场网站发布于数据库,转载请注明出处:澳门新浦京娱乐场网站积存进度,动态给表增多