22,209
社区成员
发帖
与我相关
我的任务
分享
有2个结构完全相同的两个表:表1,表2,
表中有一个mbi_id 字段 是Int 类型,没有自动增加1的属性,建了索引
该表通过外部应用程序输入数据时
mbi_id字段的值是 max(mbi_id)+1
表1
mbi_id,col1,col2
1 aa aa
2 bb bb
3 cc cc
.
.
99 ss sd
100 sa ss
表2
mbi_id,col1,col2
1 xxx xxx
2 xxx xxx
3 xx xxx
.
.
199 xx xxx
200 xx xxx
现在的问题是 我想通过脚本
把 biao1的所有数据插入到biao2 中,得到
表2
mbi_id,col1,col2
1 xxx xxx
2 xxx xxx
3 xx xxx
.
.
199 xx xxx
200 xx xxx
201 aa aa
202 bb bb
203 cc cc
.
.
299 ss sd
300 sa ss
大哥 你这样 运行的结果是
表1
1 aa aa
2 bb bb
3 cc cc
99 ss sd
100 sa ss
表2
1 xxx xxx
2 xxx xxx
3 xx xxx
199 xx xxx
200 xx xxx
插入后你的结果为
1 xxx xxx
2 xxx xxx
3 xx xxx
199 xx xxx
200 xx xxx
201 xxx xxx
202 xxx xxx
203 xx xxx
399 xx xxx
正确的结果应该为
1 xxx xxx
2 xxx xxx
3 xx xxx
199 xx xxx
200 xx xxx
201 aa aa
202 bb bb
203 cc cc
204 ss sd
205 sa ss
declare @表1 table([mbi_id] int,[col1] nvarchar(2),[col2] nvarchar(2))
Insert @表1
select 1,N'aa',N'aa' union all
select 2,N'bb',N'bb' union all
select 3,N'cc',N'cc' union all
select 99,N'ss',N'sd' union all
select 100,N'sa',N'ss'
declare @表2 table([mbi_id] int,[col1] nvarchar(3),[col2] nvarchar(3))
Insert @表2
select 1,N'xxx',N'xxx' union all
select 2,N'xxx',N'xxx' union all
select 3,N'xx',N'xxx' union all
select 199,N'xx',N'xxx' union all
select 200,N'xx',N'xxx'
declare @maxid int
select @maxid = max(mbi_id) from @表2
insert into @表2 select mbi_id+@maxid,col1,col2 from @表1
select * from @表2
/*
mbi_id col1 col2
----------- ---- ----
1 xxx xxx
2 xxx xxx
3 xx xxx
199 xx xxx
200 xx xxx
201 aa aa
202 bb bb
203 cc cc
299 ss sd
300 sa ss
(10 行受影响)
*/
INSERT INTO @表2
SELECT ISNULL(( SELECT MAX([mbi_id]) FROM @表2),1) + [mbi_id],[col1],[col2] FROM @表2
改为:
INSERT INTO @表2
SELECT ISNULL(( SELECT MAX([mbi_id]) FROM @表2),1) + [mbi_id],[col1],[col2] FROM @表1
INSERT INTO @表2
SELECT ISNULL(( SELECT MAX([mbi_id]) FROM @表2),1) + (select count(1) from @表1 where [mbi_id]<=t.[mbi_id] ),t.[col1],t.[col2] FROM @表1 t
-->生成测试数据
declare @表1 table([mbi_id] int,[col1] nvarchar(2),[col2] nvarchar(2))
Insert @表1
select 1,N'aa',N'aa' union all
select 2,N'bb',N'bb' union all
select 3,N'cc',N'cc' union all
select 99,N'ss',N'sd' union all
select 100,N'sa',N'ss'
--Select * from @表1
declare @表2 table([mbi_id] int,[col1] nvarchar(3),[col2] nvarchar(3))
Insert @表2
select 1,N'xxx',N'xxx' union all
select 2,N'xxx',N'xxx' union all
select 3,N'xx',N'xxx' union all
select 199,N'xx',N'xxx' union all
select 200,N'xx',N'xxx'
--Select * from @表2
INSERT INTO @表2
SELECT ISNULL(( SELECT MAX([mbi_id]) FROM @表2),1) + [mbi_id],[col1],[col2] FROM @表2