--生成修改含有某个字段的表情况语句
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 --设置关闭显示插入