mssql生成修改含有某个字段的表情况语句

--生成修改含有某个字段的表情况语句

select s.NAME AS NAME , IDENTITY(INT,1,1) Rowid INTO tempcolortable_230303 from sysobjects s,syscolumns c where s.id = c.id and c.name = 'ColorID' and s.xtype = 'u'

DECLARE @i INT,@sql VARCHAR(4000),@tablename VARCHAR(500),@jqidsz INT
SET @jqidsz=43760
SET @i=1
SET @sql='declare @xtypeid int
 '
 PRINT(@sql)
WHILE EXISTS(SELECT 1 FROM tempcolortable_230303 WHERE Rowid=@i)
BEGIN
    SELECT @tablename=name FROM tempcolortable_230303 WHERE Rowid=@i
    SET @sql=' IF exists(select 1 from '+@tablename+' where '''+@tablename+''' <> ''Color'')
              begin
                select top 1 @xtypeid=c.xtype from sysobjects s,syscolumns c where s.name='''+@tablename+''' and s.id = c.id and c.name = ''ColorID'' and s.xtype = ''u''  --判断列类型
                --修改表COLORID值与颜色表值一致
                update a set a.ColorID=c.colorid FROM  COLOR c,'+@tablename+' a where a.colorid not in (SELECT ColorID FROM dbo.Color) 
                        and a.colorid=case when @xtypeid in (56,59,62,106,108,127) then cast(RIGHT(''00000'' + CAST(c.Colorid AS VARCHAR), 5) as int) else RIGHT(''00000'' + CAST(c.Colorid AS VARCHAR), 5) end
                --修改表值到5位以内，符合程序要求
                update '+@tablename+' set ColorID=ColorID-'+CAST(@jqidsz AS VARCHAR(50) )+' WHERE ColorID>1 and ColorID-'+CAST(@jqidsz AS VARCHAR(50) )+'>0 
              end 
              '
    PRINT(@sql)
    SET @i=@i+1
END

--显示插入自增变量值
--修改表值到5位以内，符合程序要求
                SET IDENTITY_INSERT dbo.Color on
                GO
                DELETE FROM Color WHERE ColorID>1
                INSERT INTO Color(ColorID, Name, Remark, BarCode, SysRow, [Order], UpdateTag)
                SELECT ColorID-43760, Name, Remark, BarCode, SysRow, [Order], UpdateTag FROM  temp_Color_230306 WHERE ColorID>1 and ColorID-43760>0 
                GO
                --update Color set ColorID=ColorID-43760 WHERE ColorID>1 and ColorID-43760>0 
                --go
                
                ----恢复标识列标记
                set IDENTITY_INSERT dbo.Color off
                go 
                
                DECLARE @maxcolorid1 INT
                SELECT @maxcolorid1= max(ColorID) FROM Color
                DBCC CHECKIDENT (Color, RESEED, @maxcolorid1)
                DBCC CHECKIDENT (Color)
                GO
                
--拼接生成字段值
UPDATE ptype SET ColorIdList='' WHERE ColorIdList<>'1'
declare @LPtypeIdc varchar(50)
declare @ColorIdList varchar(1000)
declare PtypeIdCursor cursor for 
select distinct typeId,ColorIdList from Ptype where sonnum = 0 and deleted = 0
    open PtypeIdCursor 

    fetch Next from PtypeIdCursor into @LPtypeIdc,@ColorIdList
  while @@FETCH_STATUS=0    
    begin
        Select @LPtypeIdc as PtypeId,GetId into #TempColorTable from GetIdListTable(@ColorIdList)
        if exists(select PtypeId,ColorId from PtypeColor where PtypeId = @LPtypeIdc and ColorId not in (Select GetId from #TempColorTable where PtypeId = @LPtypeIdc))
        begin
           select '在ptypecolor中有颜色，在ptype表coloridlist中没有',PtypeId,ColorId from PtypeColor where PtypeId = @LPtypeIdc and ColorId not in (Select GetId from #TempColorTable where PtypeId = @LPtypeIdc)
            select @ColorIdList = @ColorIdList + ',' + cast(ColorId as varchar) from PtypeColor where PtypeId = @LPtypeIdc and ColorId not in (Select GetId from #TempColorTable where PtypeId = @LPtypeIdc)
            update Ptype Set ColorIdList = RIGHT(@ColorIdList,LEN(@ColorIdList)-1 ) where TypeID = @LPtypeIdc
        end
        --if exists(select PtypeId,GetId from #TempColorTable where PtypeId = @LPtypeIdc and GetId not in (Select ColorId from PtypeColor where PtypeId = @LPtypeIdc))
        --begin
  --           select '在ptype表的coloridlist中有颜色，在ptypecolor表中没有',PtypeId,GetId as colorid from #TempColorTable where PtypeId = @LPtypeIdc and GetId not in (Select ColorId from PtypeColor where PtypeId = @LPtypeIdc)
        --    --insert into PtypeColor select PtypeId,GetId from #TempColorTable where PtypeId = @LPtypeIdc and GetId not in (Select ColorId from PtypeColor where PtypeId = @LPtypeIdc)
        --end
        Drop table #TempColorTable
        fetch Next from PtypeIdCursor into @LPtypeIdc,@ColorIdList
    end
CLOSE PtypeIdCursor
DEALLOCATE PtypeIdCursor
GO 
                
create table #Tmp(id int identity(1,1),id_1 int)
insert into #Tmp(id,id_1) values(1,1)--报错
SELECT * FROM #Tmp
set IDENTITY_INSERT #Tmp ON  --设置显示插入
insert into #Tmp(id,id_1) values(1,1)--正确
SELECT * FROM #Tmp
set IDENTITY_INSERT #Tmp OFF --设置关闭显示插入