把某一字段更新为连续值的SQL

by admin on 2019年12月15日

背景

select @@Identity 返回自动递增字段的值。

  1. IDENTITY 列不能由用户直接更新,它是由系统自动维护的。 

2.该列数据类型必须为数值型:int, smallint, tinyint, decimal or numeric
with scale 0。 

3.该列不能为 null。 

4.不能在该列上设置缺省值。 

5.递增量只能为整形(比如:1,2,-3)。不能为小数,也不能为0。 

6.基值(种子值 seed)可以由用户设置,缺省值为1。 

理解 @@IDENTITY 

@@IDENTITY 返回最后一个插入 IDENTITY 的值,这些操作包括:INSERT, SELECT
INTO,或者 bulk copy。如果在给没有 IDENTITY
列的其他表插入记录,系统将其置为 null。如果有多行记录插入到 IDENTITY
表中,@@IDENTITY
表示最后一个产生的值。如果触发了某个触发器,并且这个触发器执行向另一个带有
IDENTITY 列的表的插入操作,@@IDENTITY
将返回这个由触发器产生的值。如果这个触发器插入的表中不包含 IDENTITY
列,那么 @@IDENTITY 将为 null。如果插入操作失败,@@IDENTITY
值依然会增加,所以 IDENTITY 不保证数据的连续性。 

@@IDENTITY
是当前连接的全局变量,只对当前连接有效。也就是说,如果断开连接再重新连接后,@@IDENTITY
为 null。以 ADO 来说,@@IDENTITY 在 Connection
对象打开和关闭期间是有意义的,即在 Connection 对象的存在范围内有效。在
MTS 组件中,从打开连接到显式的关闭连接(Connection.Close)或者到调用了
SetAbort,SetComplete之前,在这期间,@@IDENTITY 有意义。 

使用 Truncate table 语句会使 IDENTITY 列重新开始计算。 

得到 @@IDENTITY 的值 

有三种方法(以下代码均使用 VBScript) 

方法一: 

Dim Conn, strSQL, Rs 
Set Conn = CreateObject(“ADODB.Connection”) 
‘ Open a connection to the database 
Conn.Open(“DSN=myDSN;UID=myUID;PWD=myPWD;”) 

‘ Insert a new record into the table 
strSQL = “INSERT INTO mtTable (columnName) VALUES (‘something’)” 

‘ Execute the SQL statement 
Conn.Execute(strSQL) 

‘ Get the @@IDENTITY. 
strSQL = “SELECT @@IDENTITY AS NewID” 
Set Rs = Conn.Execute(lsSQL) 
NewID = Rs.Fields(“NewID”).value 

‘ Close the connection 
Conn.Close() 
Set Conn = Nothing 

方法二(仅限于 ADO 2.0 以上): 

Dim Conn, strSQL, Rs 
Set Conn = CreateObject(“ADODB.Connection”) 
‘ Open a connection to the database 
Conn.Open(“DSN=myDSN;UID=myUID;PWD=myPWD;”) 

‘ Insert a new record into the table 
lsSQL = “INSERT INTO myTable (columnName) VALUES (‘something’);” &_ 
“SELECT @@IDENTITY AS NewID;” 

‘ Execute the SQL statement 
Set Rs = Conn.Execute(lsSQL) 

‘ Get the second resultset into a RecordSet object 
Set Rs = Rs.NextRecordSet() 

‘ Get the inserted ID 
NewID = Rs.Fields(“NewID”).value 

‘ Close the connection 
Conn.Close() 
Set Conn = Nothing 

方法三: 

Dim Conn, strSQL, Rs 
Set Conn = CreateObject(“ADODB.Connection”) 
‘ Open a connection to the database 
Conn.Open(“DSN=myDSN;UID=myUID;PWD=myPWD;”) 

‘ Insert a new record into the table 
strSQL = “SET NOCOUNT ON;” &_ 
“INSERT INTO myTable (columnName) VALUES (‘something’);” &_ 
“SELECT @@IDENTITY AS NewID;” 

‘ Execute the SQL statement 
Set Rs = Conn.Execute(lsSQL) 

‘ Get the inserted ID 
NewID = Rs.Fields(“NewID”).value 

‘ Close the connection 
Conn.Close() 
Set Conn = Nothing  

 

 

使用C#书写的函数

/// <summary>
        /// 更新记录并返回影响新第一行第一列ID
        /// </summary>
        /// <param name="sSQL"></param>
        /// <returns></returns>
        public int UpdateDbBySQLRetID(string sSQL)
        {
            int iRet = 0;
            using (SqlConnection conn = new SqlConnection(sConnect))
            {
                SqlCommand cmd = GetCmdByProperty();
                try
                {
                    cmd.Connection = conn;
                    cmd.CommandText = sSQL + ";Select @@IDENTITY;";
                    cmd.CommandType = CommandType.Text;
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    object obj = cmd.ExecuteScalar();
                    if (obj == null)
                        iRet = 0;
                    else
                    {
                        if (!int.TryParse(obj.ToString(), out iRet))
                            iRet = 0;
                    }
                    return iRet;
                }
                catch (Exception e)
                {
                    Console.WriteLine("InsertDbBySQL:" + e.Message);
                    return -1;
                }
                finally
                {
                    if (cmd != null)
                        cmd = null;
                    conn.Close();
                }
            }
        }

 

1 --从10001起,借用生成的行号,批量编号表记录
2 
3 declare @start int = 10000;
4 
5 update t1 set t1.newNo=t2.newNo
6 from student t1
7 join (select id, (row_number() over(order by id) + @start) newNo from student) t2
8 on t1.id=t2.id

在上一篇《数据库操作类SqlHelper》博文的最后,提到了一个实践运用中遇到的问题,就是数据库表中的自增长字段的赋值不受人为控制。比如数据库有一个tb_Department表,DeptNO字段为自增长主键。

 

图片 1

现在插入一行数据

图片 2

啊!DeptNO字段怎么就是22了呢,不应该是从4开始吗?

原因:这个表之前进行过很多插入操作,数据库针对自增长字段的每次插入都会自动+1,后来删除了一部分行数据,然后重新插入的时候,数据库不会依据表中缺失的字段值进行赋值,而是在原先的基础上继续+1赋值。

结果:在新插入的“哈哈系”数据行之前,其实数据库已经向表里插入过21次了,只是DeptNO字段值大于3的行数据被删除了,现在要新插入行数据的话,就会在21的基础上+1,也就是第二个表中出现的22了。

期望:

  1. 在插入新数据的时候,针对自增长字段可以人为控制;
  2. 实际运用中,其实用户并不知道数据表中自增长字段缺失的是哪些值,程序需要自动提供缺失或者缺省值。

设计

1.在插入新数据的时候,针对自增长字段可以人为控制

数据库中针对自增长字段在插入时,不可以指定显式值的。

insert into tb_Department(DeptNO,DeptName) values(4,N'嘿嘿系')

这样插入数据会报错的,提示你“当Identity_Insert设置为off时,不能为表’tb_Department’中的标识列插入显式值”。很明显,第一个期望的解决方案就是将Identity_Insert设置on,然后执行显式值插入,最后关闭标识列插入开关。

set identity_insert tb_Department on
insert into tb_Department(DeptNO,DeptName) values(4,N'嘿嘿系')
set identity_insert tb_Department off

执行看看能不能插入,哇哦,成功了,棒棒哒。

图片 3

2.实际运用中,用户并不知道数据表中自增长字段未使用有哪些值,程序需要自动提供缺失或者缺省值

自增长字段的值分为缺失值和缺省值(这个术语是我自己定的,为了方便描述)

缺失值:比如数据表中自增长字段的值为(1,2,3,5),则缺失值为4。要想让程序自动检索到缺失值,需要对数据表进行全面扫描,逐行判断自增长字段的值是否连续递增,只要检索到不连续的值就将对应序列的值返回,并显示在窗体上,无需用户自己输入。

缺省值:比如数据表中自增长字段的值为(1,2,3,4),则缺省值为5。假设原先有10行数据,然后将大于4的行删除后,自增长字段本身还是连续递增的,只需要找到缺省值,返回给用户。

利用SQL脚本创建存储过程实现:(注意:该实现返回当前自增长字段中第一个缺失值/缺省值,只适用于每次插入一行数据的情况)

--创建一个存储过程用于自动提取自增长字段的第一个缺失值和缺省值
create procedure NumOfDeptNOForInsert
@temp int output  --定义一个输出参数,用于返回缺失值/缺省值
as
declare @Count int   --定义一个当前表中的行数
select @Count=COUNT(1) from tb_Department   --给变量@Count赋值
declare @I int, @IsOK bit = 0,@num int = 1  --定义一个用于循环的@I变量,一个用于判断是缺失值还是缺省值的变量@IsOK,一个记录缺省值的变量@num
set @I = 1;   --变量@I赋值为1
while(@I <= @Count)   --开始循环扫描行数据
begin
    select @temp=DeptNO from tb_Department where DeptNO=@I   --检索DeptNO值=@I值的行数据
    if(@temp != @I)  --判断@I值与DeptNO值是否比对不成功
    begin
        set @temp = @I  --将@I值赋值给@temp
        set @IsOK = 0   --标记为缺失值
        break    --退出循环     
    end
    else    --判断@I值与DeptNO值是否比对成功
    begin
        set @I = @I +1  --@I+1
        set @num = @I   --将@I赋值给@num
        set @IsOK = 1   --标记为缺省值
    end 
end   

if(@IsOK =0)   --判断是缺失值还是缺省值,如果是缺失值
begin
    select @temp  --直接返回@temp
end
else    --如果是缺省值
begin
    set @temp = @num  --将@num赋值给@temp
    select @temp      --再返回@temp
end

自增长字段的连续递增插入的存储过程设计好后,首先在SQL
Server中检验一下。

declare @temp int --定义输出参数
exec dbo.NumOfDeptNOForInsert @temp  --调用储存过程
print @temp  --打印输出参数
  •  缺失值的检验:

图片 4             图片 5

调用存储过程看看缺失的第一个值是不是5,结果跟预期一样。

  • 缺省值的检验:

图片 6           
图片 7

调用存储过程,找到的第一个缺省值为7,结果跟预期的一样。

 实践

前面的分析设计做好后,当然就是运用于实践了,主要是编写获取自增长字段的缺失值/缺省值的方法: 

        /// <summary>
        /// 获取自增长字段的第一个缺失值或者缺省值
        /// </summary>
        /// <returns>缺失值/缺省值</returns>
        private int GetDeptNO()
        {
            string cmdText = @"NumOfDeptNOForInsert";
            SqlParameter[] parameters =
            {
                new SqlParameter("@temp",SqlDbType.Int)
            };
            parameters[0].Direction = ParameterDirection.Output;
            int deptNO = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnString, CommandType.StoredProcedure, cmdText,parameters);
            return deptNO;
        }

程序整体的UI设计和编码在博文《数据库操作类SqlHelper》中都已经讲述,这里就不在反复讲了。相对于之前来说,需要更改代码的地方为“增加”按钮的点击处理程序和InsertData()方法: 

        private void tsbInsert_Click(object sender, EventArgs e)
        {
            cmdType = CmdType.Insert;
            //将gbDept控件设置可用,textbox控件设为可用,并将Text属性清空
            this.gbDept.Enabled = true;
            this.txtDeptName.Enabled = true;
            this.txtDeptName.Text = string.Empty;
            //显示即将插入的DeptNO值
            this.lbDeptNO.Text = GetDeptNO().ToString();
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        private void InsertData()
        {
            //判断系部名称是否为空
            if (string.IsNullOrEmpty(this.txtDeptName.Text.Trim()))
            {
                MessageBox.Show("系部名称不能为空!");
                return;
            }
            //定义插入数据的SQL脚本,其中set identity_insert tb_Department on/off主要是为了能让自增长主键连续有序地插入
            string cmdText = @"set identity_insert tb_Department on 
                               insert into tb_Department(DeptNO,DeptName) values(@DeptNO,@DeptName)
                               set identity_insert tb_Department off";
            ////定义插入数据的Sql脚本
            //string cmdText = @"insert into tb_Department(DeptName) values(@DeptName)";
            //SQL脚本参数设置
            SqlParameter[] parameters =
            {
                new SqlParameter("@DeptNO",(object)this.lbDeptNO.Text),
                new SqlParameter("@DeptName",(object)this.txtDeptName.Text.Trim())
            };
            //执行插入,并返回受影响的行数
            int rows = SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, cmdText, parameters);
            //判断是否插入成功,并提示
            if (rows > 0)
            {
                //更新datagridview控件的数据
                LoadData();
                //显示即将插入的DeptNO值
                this.lbDeptNO.Text = GetDeptNO().ToString();
                //将系部名称设为空
                this.txtDeptName.Text = string.Empty;
                MessageBox.Show("插入成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("插入失败!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            //执行增删改操作
            switch (cmdType)
            {
                case CmdType.Insert:
                    InsertData();
                    break;
                case CmdType.Delete:
                    DeleteData();
                    break;
                case CmdType.Update:
                    UpdateData();
                    break;
            }
        }

结果

UI和编码完成后,调试程序是很关键的,能从调试的过程中重现整个功能的思路,也能找到一些问题所在,修复bug,然后重编码。废话不多说看结果吧:

1.缺失值:

假设一开始的tb_Department表如下图所示,理论上缺失的值为(4,5,7),现在往表里插入新值,看看结果如何。点击“增加”按钮,窗体下方系部编号自动出现第一个缺失值4,系部名称我们设置为“嘟嘟系”,提交添加成功后,系部编号会自动显示下一个缺失值5。为了后面的缺省值的结果,我们再增加缺失值(5,7)两行数据,使DeptNO字段连续递增。

 图片 8图片 9

2.缺省值:

当DeptNO字段连续递增时,如下图所示,点击“增减”按钮,窗体下方的系部编号成功地提取到第一个缺省值9,系部名称输入“物理系”,提交增加成功后,系部编号会自动显示下一个缺省值10。

 图片 10图片 11

调试结果显示我们提出的需求已经得到解决。

一.总结

本文主要针对数据表中自增长字段的插入问题进行讲解,不管表中的数据增删过多少次,程序总是能提供自增长字段的缺失值或者缺省值用于新数据行的插入,从而实现插入自增长字段值的连续递增特性。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图