A数据库code表
id(自动编号),ccode,codename,year(smallint),pubufts(timestamp)
1 101 xj 2013
2 102 yh 2013
3 101 xj 2014
4 102 yh 2014
5 101 xj 2015
6 102 yh 2015B数据库code表和A数据库表结构完全相同,实际上是同一个软件生成的两个结构相同的数据库,B数据库code表为空
我想把A数据库code表的信息全部复制到B数据库code表中。我的SQL语句是这样写的:
insert into b..code(ccode,codename,year) select ccode,codename,year from a..code
执行后,没有提示错误,并且A库的记录数是多少,B库的记录数就是多少,我以为没问题,结果打开B库的code一看傻眼了。
B库code表信息如下:
id(自动编号),ccode,codename,year(smallint),pubufts(timestamp)
1 101 xj 2013
2 102 yh 2013
3 101 xj 2013
4 102 yh 2013
5 101 xj 2013
6 102 yh 2013---------------------------------------------------------------------
怎么year字段的值都是2013了?
id(自动编号),ccode,codename,year(smallint),pubufts(timestamp)
1 101 xj 2013
2 102 yh 2013
3 101 xj 2014
4 102 yh 2014
5 101 xj 2015
6 102 yh 2015B数据库code表和A数据库表结构完全相同,实际上是同一个软件生成的两个结构相同的数据库,B数据库code表为空
我想把A数据库code表的信息全部复制到B数据库code表中。我的SQL语句是这样写的:
insert into b..code(ccode,codename,year) select ccode,codename,year from a..code
执行后,没有提示错误,并且A库的记录数是多少,B库的记录数就是多少,我以为没问题,结果打开B库的code一看傻眼了。
B库code表信息如下:
id(自动编号),ccode,codename,year(smallint),pubufts(timestamp)
1 101 xj 2013
2 102 yh 2013
3 101 xj 2013
4 102 yh 2013
5 101 xj 2013
6 102 yh 2013---------------------------------------------------------------------
怎么year字段的值都是2013了?
insert into code select ID,ccode,codename,year,pubufts from a.code
select ID,ccode,codename,year,pubufts into Test from a.code
select * Test
看看Test表中的Year是多少
1. LZ眼花了,搞错了数据库
2. 有人手贱偷偷update了。
3. 有触发器偷偷update了。
约束是ccode(ASC),iyear(ASC) ,唯一
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[gl_v_code_InsteadTrigger] on [dbo].[code]
INSTEAD OF INSERT
AS
BEGIN
declare @iYear smallint
set @iYear =(select top 1 iyear from inserted)
if (@iYear is null or @iYear=0)
set @iYear=(select top 1 iYear from U8LoginProcess a inner join master..sysprocesses b on a.cStation=b.hostname and a.cProcessId=b.hostprocess where b.spid=@@spid and (cSub_id='DP') )
INSERT INTO code([cclass], [cclass_engl], [cclassany], [cclassany_engl], [ccode], [ccode_name], [ccode_engl], [igrade], [bproperty], [cbook_type], [cbook_type_engl], [chelp], [cexch_name], [cmeasure], [bperson], [bcus], [bsup], [bdept], [bitem], [cass_item], [br], [be], [cgather], [bend], [bexchange], [bcash], [bbank], [bused], [bd_c], [dbegin], [dend], [itrans], [bclose], [cother], [iotherused], [bcDefine1], [bcDefine2], [bcDefine3], [bcDefine4], [bcDefine5], [bcDefine6], [bcDefine7], [bcDefine8], [bcDefine9], [bcDefine10], [iViewItem], [bGCJS], [bCashItem], [bcDefine11], [bcDefine12], [bcDefine13], [bcDefine14], [bcDefine15], [bcDefine16], [bReport], [cUserDefineType], [iyear])
SELECT [cclass], [cclass_engl], [cclassany], [cclassany_engl], [ccode], [ccode_name], [ccode_engl], [igrade], [bproperty], [cbook_type], [cbook_type_engl], [chelp], [cexch_name], [cmeasure], [bperson], [bcus], [bsup], [bdept], [bitem], [cass_item], [br], [be], [cgather], [bend], [bexchange], [bcash], [bbank], [bused], [bd_c], [dbegin], [dend], [itrans], [bclose], [cother], [iotherused], [bcDefine1], [bcDefine2], [bcDefine3], [bcDefine4], [bcDefine5], [bcDefine6], [bcDefine7], [bcDefine8], [bcDefine9], [bcDefine10], [iViewItem], [bGCJS], [bCashItem], [bcDefine11], [bcDefine12], [bcDefine13], [bcDefine14], [bcDefine15], [bcDefine16], [bReport], [cUserDefineType], @iYear FROM inserted
END