34,576
社区成员
发帖
与我相关
我的任务
分享关于往表中递增插入数据和等价插入数据的问题。
我有一个表： A
字段如下
ID PID NAME STR1 STR2
1 10001 li stra strb
2 10001 zheng straaa strbbb
我现在想要递增PID式的往A表中插入数据，使它变成这样的表：
ID PID NAME STR1 STR2
1 10001 li stra strb
2 10001 zheng straaa strbbb
3 10002 li stra strb
4 10002 zheng straaa strbbb
5 10003 li stra strb
6 10003 zheng straaa strbbb
7 10004 li stra strb
8 10004 zheng straaa strbbb
9 10005 li stra strb
10 10005 zheng straaa strbbb
......
10023 li stra strb
10023 zheng straaa strbbb
这里ID是主键索引，PID需要递增，而其他字段则不变。。
谢谢大家。
字段如下
ID PID NAME STR1 STR2
1 10001 li stra strb
2 10001 zheng straaa strbbb
我现在想要递增PID式的往A表中插入数据，使它变成这样的表：
ID PID NAME STR1 STR2
1 10001 li stra strb
2 10001 zheng straaa strbbb
3 10002 li stra strb
4 10002 zheng straaa strbbb
5 10003 li stra strb
6 10003 zheng straaa strbbb
7 10004 li stra strb
8 10004 zheng straaa strbbb
9 10005 li stra strb
10 10005 zheng straaa strbbb
......
10023 li stra strb
10023 zheng straaa strbbb
这里ID是主键索引，PID需要递增，而其他字段则不变。。
谢谢大家。
...全文
请发表友善的回复…
发表回复
you_tube 2009-05-26
- 打赏
- 举报
--测试--
DECLARE @TB TABLE([ID] INT, [PID] VARCHAR(5), [NAME] VARCHAR(5), [STR1] VARCHAR(6),
[STR2] VARCHAR(6))
INSERT @TB
SELECT 1, '10001', 'li', 'stra', 'strb' UNION ALL
SELECT 2, '10001', 'zheng', 'straaa', 'strbbb'
--SQL--
SELECT T1.* FROM (
SELECT id,pid + id -1 pid,a.NAME,str1,str2
FROM @TB A ,master..spt_values b
WHERE b.[type]='p' AND b.number <2) T1
--结果--
/*
id pid NAME str1 str2
----------- ----------- ----- ------ ------
1 10001 li stra strb
1 10001 li stra strb
2 10002 zheng straaa strbbb
2 10002 zheng straaa strbbb
(4 行受影响)
*/
lihan6415151528 2009-05-26
- 打赏
- 举报
CREATE TABLE TEST(ID INT IDENTITY(1,1) ,PID INT,NAME VARCHAR(20),STR1 VARCHAR(20),STR2 VARCHAR(20))
INSERT INTO TEST SELECT 10001,'LI','STRA','STRB'
UNION ALL SELECT 10001,'ZHENG','STRAAAA','STRBBBB'
GO
SELECT * FROM TEST
declare @t int
set @t=0
while(@t<11)
begin
INSERT INTO TEST SELECT(select max(pid) from test)+1,NAME,STR1,STR2 from test where PID=(select max(pid) from test)
set @t = @t+1
end
select * from test
drop table test
ID PID NAME STR1 STR2
----------- ----------- -------------------- -------------------- --------------------
1 10001 LI STRA STRB
2 10001 ZHENG STRAAAA STRBBBB
3 10002 LI STRA STRB
4 10002 ZHENG STRAAAA STRBBBB
5 10003 LI STRA STRB
6 10003 ZHENG STRAAAA STRBBBB
7 10004 LI STRA STRB
8 10004 ZHENG STRAAAA STRBBBB
9 10005 LI STRA STRB
10 10005 ZHENG STRAAAA STRBBBB
11 10006 LI STRA STRB
12 10006 ZHENG STRAAAA STRBBBB
13 10007 LI STRA STRB
14 10007 ZHENG STRAAAA STRBBBB
15 10008 LI STRA STRB
16 10008 ZHENG STRAAAA STRBBBB
17 10009 LI STRA STRB
18 10009 ZHENG STRAAAA STRBBBB
19 10010 LI STRA STRB
20 10010 ZHENG STRAAAA STRBBBB
21 10011 LI STRA STRB
22 10011 ZHENG STRAAAA STRBBBB
23 10012 LI STRA STRB
24 10012 ZHENG STRAAAA STRBBBB
（所影响的行数为 24 行）
jia_guijun 2009-05-26
- 打赏
- 举报
declare @t table(ID int identity(1,1), PID int ,NAME varchar(10),STR1 varchar(10),STR2 varchar(10))
insert @t(PID,NAME,STR1,STR2) values(10001,'li','stra','strb')
insert @t(PID,NAME,STR1,STR2) values(10001,'zheng','straaa','strbbb')
--插入数据
declare @MaxPID int
set @MaxPID=10023 --设置最大的PID
while (select max(pid) from @t)<@MaxPID
begin
insert @t(PID,NAME,STR1,STR2)
select (select max(pid) from @t)+1,NAME,STR1,STR2 from @t where PID=(select max(pid) from @t)
end
select * from @t
/*
ID PID NAME STR1 STR2
----------- ----------- ---------- ---------- ----------
1 10001 li stra strb
2 10001 zheng straaa strbbb
3 10002 li stra strb
4 10002 zheng straaa strbbb
5 10003 li stra strb
6 10003 zheng straaa strbbb
7 10004 li stra strb
8 10004 zheng straaa strbbb
9 10005 li stra strb
10 10005 zheng straaa strbbb
11 10006 li stra strb
12 10006 zheng straaa strbbb
13 10007 li stra strb
14 10007 zheng straaa strbbb
15 10008 li stra strb
16 10008 zheng straaa strbbb
17 10009 li stra strb
18 10009 zheng straaa strbbb
19 10010 li stra strb
20 10010 zheng straaa strbbb
21 10011 li stra strb
22 10011 zheng straaa strbbb
23 10012 li stra strb
24 10012 zheng straaa strbbb
25 10013 li stra strb
26 10013 zheng straaa strbbb
27 10014 li stra strb
28 10014 zheng straaa strbbb
29 10015 li stra strb
30 10015 zheng straaa strbbb
31 10016 li stra strb
32 10016 zheng straaa strbbb
33 10017 li stra strb
34 10017 zheng straaa strbbb
35 10018 li stra strb
36 10018 zheng straaa strbbb
37 10019 li stra strb
38 10019 zheng straaa strbbb
39 10020 li stra strb
40 10020 zheng straaa strbbb
41 10021 li stra strb
42 10021 zheng straaa strbbb
43 10022 li stra strb
44 10022 zheng straaa strbbb
45 10023 li stra strb
46 10023 zheng straaa strbbb
*/
Zoezs 2009-05-26
- 打赏
- 举报
用个循环做
declare @i int
alter table TB alter id int identity(int,1,1)
set @i=0
while @i<1000
Begin
insert into TB(PID,[NAME],STR1,STR2)
select PID+@i,[NAME],STR1,STR2 from TA
set @i=@i+1
End
delete from TA
insert into TA
select * from TB
csdyyr 2009-05-26
- 打赏
- 举报
DECLARE @TB TABLE([ID] INT, [PID] VARCHAR(5), [NAME] VARCHAR(5), [STR1] VARCHAR(6), [STR2] VARCHAR(6))
INSERT @TB
SELECT 1, '10001', 'li', 'stra', 'strb' UNION ALL
SELECT 2, '10001', 'zheng', 'straaa', 'strbbb'
SELECT TOP 23 SEQ=IDENTITY(int,1,1) INTO # FROM syscolumns
SELECT ID=IDENTITY(int,1,1),
CAST(PID AS INT)+SEQ-1 AS PID,
NAME,
STR1,
STR2
INTO #T
FROM @TB AS TB,# AS TA
ORDER BY PID,TB.ID
SELECT * FROM #T
DROP TABLE #,#T
/*
ID PID NAME STR1 STR2
----------- ----------- ----- ------ ------
1 10001 li stra strb
2 10001 zheng straaa strbbb
3 10002 li stra strb
4 10002 zheng straaa strbbb
5 10003 li stra strb
6 10003 zheng straaa strbbb
7 10004 li stra strb
8 10004 zheng straaa strbbb
9 10005 li stra strb
10 10005 zheng straaa strbbb
11 10006 li stra strb
12 10006 zheng straaa strbbb
13 10007 li stra strb
14 10007 zheng straaa strbbb
15 10008 li stra strb
16 10008 zheng straaa strbbb
17 10009 li stra strb
18 10009 zheng straaa strbbb
19 10010 li stra strb
20 10010 zheng straaa strbbb
21 10011 li stra strb
22 10011 zheng straaa strbbb
23 10012 li stra strb
24 10012 zheng straaa strbbb
25 10013 li stra strb
26 10013 zheng straaa strbbb
27 10014 li stra strb
28 10014 zheng straaa strbbb
29 10015 li stra strb
30 10015 zheng straaa strbbb
31 10016 li stra strb
32 10016 zheng straaa strbbb
33 10017 li stra strb
34 10017 zheng straaa strbbb
35 10018 li stra strb
36 10018 zheng straaa strbbb
37 10019 li stra strb
38 10019 zheng straaa strbbb
39 10020 li stra strb
40 10020 zheng straaa strbbb
41 10021 li stra strb
42 10021 zheng straaa strbbb
43 10022 li stra strb
44 10022 zheng straaa strbbb
45 10023 li stra strb
46 10023 zheng straaa strbbb
(46 row(s) affected)
*/
