农产品加工公司农副产品收购数据库一表转“一对多”，求存储过程

Question

公司业务需要，特来求助
原表结构及示例如下：
id   村名  联合生产队   定价日期   收货日期      花生   地瓜   小麦   特价  接货人 输入日期      备注
01   陈村    一队     7/16/2007   7/23/2007   20    6     12    N    cany   7/25/2007  NULL
02   杨村    四队     7/16/2007   7/23/2007   17    8     16    N    cany   7/25/2007  NULL
04   陈村    一队     9/22/2007   10/8/2007   14    9     22    Y    Kate   10/12/2007 NULL
06   陈村    三队     9/22/2007   10/8/2007   16    12    19    Y    Kate   10/12/2007 NULL说明：id不是连续的，原来是取最后一个id，然后加1生成新条目的id号，现在因为现在收购品种变得不确定，于是新的收购使用一对多的表。
新表结构和示例如下：  表一
id  村名   联合生产队  接货人  输入日期    备注
01  陈村   一队         cany   1/12/2008    NULL
02  杨村   四队         Kate   1/18/2008    NULL
表二
id  品种  定价日期    收货日期   是否特价  
01  花生   1/4/2008    1/9/2008   N         
01  地瓜   1/4/2008    1/9/2008   N        
01  小麦   1/5/2008    1/9/2008   N
01  玉米   1/5/2008    1/9/2008   N
02  花生   1/8/2008    1/14/2008  Y
02  小麦   1/8/2008    1/14/2008  Y在新表1和2里，id是对应的，两个表id相同的都是同一票单的，就是说id为01的单，是陈村一队发的4种货，而02那张单，只从杨村4队收购了2种货，现要把旧表转成一对多，并分拆加在两个新表后面，旧表不删除，求存储过程
新表的id取法和旧表一样

Answer 1

基本看明白了,是不是原来的表分成两个表对应插入新表1和新表2.就是插入新表的情况该如何搞?
比如已经存在的:01  陈村  一队        cany 1/12/2008
新表也有这个数据,只是日期不同,也要重新插入,另给编号?

Answer 2

新表的id取法和旧表一样: 是否是指在新表2中要有一个id保存原表中的id?

Answer 3

declare @aa table(id nvarchar(10),村名 nvarchar(20),联合生产队 nvarchar(20),定价日期 datetime,收货日期 datetime,花生 int,地瓜 int,小麦 int,特价 nvarchar(10),接货人 nvarchar(20),输入日期 datetime,备注 nvarchar(200))
insert into @aa select '01','陈村','一队','7/16/2007','07/23/2007',20,6,12,'N','cany','7/25/2007',null
insert into @aa select '02','杨村','四队','7/16/2007','07/23/2007',17,8,16,'N','cany','7/25/2007',null
insert into @aa select '04','陈村','一队','9/22/2007','10/8/2007',14,9,22,'y','Kate', '10/12/2007',null
insert into @aa select '06','陈村','三队','9/22/2007','10/8/2007',16,12,19,'y','Kate','10/12/2007',nulldeclare @表一 table(id nvarchar(4),村名 nvarchar(20),联合生产队 nvarchar(20),接货人 nvarchar(10), 输入日期 datetime, 备注 nvarchar(200))
declare @表二 table(id nvarchar(4),品种 nvarchar(20),定价日期 datetime,收货日期 datetime,是否特价 nvarchar(1))
insert into @表一  select id,村名,联合生产队,接货人,输入日期,备注 from @aa insert into @表二  select id,花生 ,定价日期,收货日期,特价 from @aainsert into @表二  select id,地瓜 ,定价日期,收货日期,特价 from @aa insert into @表二  select id,小麦 ,定价日期,收货日期,特价 from @aa select * from @表一
select * from @表二
---结果
id   村名                   联合生产队                接货人        输入日期                    备注
---- -------------------- -------------------- ---------- ----------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01   陈村                   一队                   cany       2007-07-25 00:00:00.000 NULL
02   杨村                   四队                   cany       2007-07-25 00:00:00.000 NULL
04   陈村                   一队                   Kate       2007-10-12 00:00:00.000 NULL
06   陈村                   三队                   Kate       2007-10-12 00:00:00.000 NULL(4 row(s) affected)id   品种                   定价日期                    收货日期                    是否特价
---- -------------------- ----------------------- ----------------------- ----
01   20                   2007-07-16 00:00:00.000 2007-07-23 00:00:00.000 N
02   17                   2007-07-16 00:00:00.000 2007-07-23 00:00:00.000 N
04   14                   2007-09-22 00:00:00.000 2007-10-08 00:00:00.000 y
06   16                   2007-09-22 00:00:00.000 2007-10-08 00:00:00.000 y
01   6                    2007-07-16 00:00:00.000 2007-07-23 00:00:00.000 N
02   8                    2007-07-16 00:00:00.000 2007-07-23 00:00:00.000 N
04   9                    2007-09-22 00:00:00.000 2007-10-08 00:00:00.000 y
06   12                   2007-09-22 00:00:00.000 2007-10-08 00:00:00.000 y
01   12                   2007-07-16 00:00:00.000 2007-07-23 00:00:00.000 N
02   16                   2007-07-16 00:00:00.000 2007-07-23 00:00:00.000 N
04   22                   2007-09-22 00:00:00.000 2007-10-08 00:00:00.000 y
06   19                   2007-09-22 00:00:00.000 2007-10-08 00:00:00.000 y(12 row(s) affected)

Answer 4

declare @aa table(id nvarchar(10),村名 nvarchar(20),联合生产队 nvarchar(20),定价日期 datetime,收货日期 datetime,花生 int,地瓜 int,小麦 int,特价 nvarchar(10),接货人 nvarchar(20),输入日期 datetime,备注 nvarchar(200))
insert into @aa select '01','陈村','一队','7/16/2007','07/23/2007',20,6,12,'N','cany','7/25/2007',null
insert into @aa select '02','杨村','四队','7/16/2007','07/23/2007',17,8,16,'N','cany','7/25/2007',null
insert into @aa select '04','陈村','一队','9/22/2007','10/8/2007',14,9,22,'y','Kate', '10/12/2007',null
insert into @aa select '06','陈村','三队','9/22/2007','10/8/2007',16,12,19,'y','Kate','10/12/2007',nulldeclare @表一 table(id nvarchar(4),村名 nvarchar(20),联合生产队 nvarchar(20),接货人 nvarchar(10), 输入日期 datetime, 备注 nvarchar(200))
declare @表二 table(id nvarchar(4),品种 nvarchar(20),数量 int,定价日期 datetime,收货日期 datetime,是否特价 nvarchar(1))
insert into @表一  select id,村名,联合生产队,接货人,输入日期,备注 from @aa insert into @表二  select id,'花生',花生 ,定价日期,收货日期,特价 from @aainsert into @表二  select id,'地瓜',地瓜 ,定价日期,收货日期,特价 from @aa insert into @表二  select id,'小麦',小麦 ,定价日期,收货日期,特价 from @aa select * from @表一
select * from @表二
--结果
id   村名                   联合生产队                接货人        输入日期                    备注
---- -------------------- -------------------- ---------- ----------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01   陈村                   一队                   cany       2007-07-25 00:00:00.000 NULL
02   杨村                   四队                   cany       2007-07-25 00:00:00.000 NULL
04   陈村                   一队                   Kate       2007-10-12 00:00:00.000 NULL
06   陈村                   三队                   Kate       2007-10-12 00:00:00.000 NULL(4 row(s) affected)id   品种                   数量          定价日期                    收货日期                    是否特价
---- -------------------- ----------- ----------------------- ----------------------- ----
01   花生                   20          2007-07-16 00:00:00.000 2007-07-23 00:00:00.000 N
02   花生                   17          2007-07-16 00:00:00.000 2007-07-23 00:00:00.000 N
04   花生                   14          2007-09-22 00:00:00.000 2007-10-08 00:00:00.000 y
06   花生                   16          2007-09-22 00:00:00.000 2007-10-08 00:00:00.000 y
01   地瓜                   6           2007-07-16 00:00:00.000 2007-07-23 00:00:00.000 N
02   地瓜                   8           2007-07-16 00:00:00.000 2007-07-23 00:00:00.000 N
04   地瓜                   9           2007-09-22 00:00:00.000 2007-10-08 00:00:00.000 y
06   地瓜                   12          2007-09-22 00:00:00.000 2007-10-08 00:00:00.000 y
01   小麦                   12          2007-07-16 00:00:00.000 2007-07-23 00:00:00.000 N
02   小麦                   16          2007-07-16 00:00:00.000 2007-07-23 00:00:00.000 N
04   小麦                   22          2007-09-22 00:00:00.000 2007-10-08 00:00:00.000 y
06   小麦                   19          2007-09-22 00:00:00.000 2007-10-08 00:00:00.000 y(12 row(s) affected)

Answer 5

to 老乌龟
两个新表是08年才开始用的，三队今年还没收货，现在是要把07年的表放到08年的表里。
id只是一个货单标识而已，重复数据不管，旧表的纪录另外给编号，把旧表加在新表的后面，就是拆开来放。
旧表是固定3个品种，而08年则可能一个队收到很多种类，也可能只有一种，所以今年用一对多的表来记录，基本情况一个表，收购到的货物一个表，凭id来确定是否同一个货单里的to Time Space
我的意思是，新id也是取最后一个然后+1，就是新表里加入旧表的纪录，旧纪录给新id号以上是示例，因为07年旧表的纪录有5000多条，希望各位高手指点一个存储过程，手动转会死人的

Answer 6

 to qiuming0306 数量忘了加了，在新表2里重新整理如下表二 
id     品种   数量  定价日期         收货日期       是否特价     
01     花生    16   1/4/2008         1/9/2008       N                   
01     地瓜    7     1/4/2008          1/9/2008       N                 
01     小麦    18    1/5/2008         1/9/2008       N 
01     玉米    12    1/5/2008         1/9/2008       N 
02     花生    18    1/8/2008         1/14/2008     Y 
02     小麦    23    1/8/2008         1/14/2008     Y 

Answer 7

to Haiwer 
刚在上面加上了，请继续研究，那是数量，麻袋个数

Answer 8

7楼的方法好像有个问题，我的意思是，把原表的条目拆开放到新表，并且，id重新给过，也就是说，上面示例的旧表加到新表后，新表一最后一个id应该是06，原表纪录id号的01，02，04，06到了新表一就是接着前面的03，04，05，06

Answer 9

--改7楼的代码
declare @aa table(id nvarchar(10),村名 nvarchar(20),联合生产队 nvarchar(20),定价日期 datetime,收货日期 datetime,花生 int,地瓜 int,小麦 int,特价 nvarchar(10),接货人 nvarchar(20),输入日期 datetime,备注 nvarchar(200))
insert into @aa select '01','陈村','一队','7/16/2007','07/23/2007',20,6,12,'N','cany','7/25/2007',null
insert into @aa select '02','杨村','四队','7/16/2007','07/23/2007',17,8,16,'N','cany','7/25/2007',null
insert into @aa select '04','陈村','一队','9/22/2007','10/8/2007',14,9,22,'y','Kate', '10/12/2007',null
insert into @aa select '06','陈村','三队','9/22/2007','10/8/2007',16,12,19,'y','Kate','10/12/2007',nulldeclare @表一 table(id nvarchar(4),村名 nvarchar(20),联合生产队 nvarchar(20),接货人 nvarchar(10), 输入日期 datetime, 备注 nvarchar(200))
declare @表二 table(id nvarchar(4),品种 nvarchar(20),数量 int,定价日期 datetime,收货日期 datetime,是否特价 nvarchar(1))
insert into @表一  select right('00'+rtrim((select count(1) from @aa where id<=a.id)),2) as id,村名,联合生产队,接货人,输入日期,备注 from @aa ainsert into @表二  select right('00'+rtrim((select count(1) from @aa where id<=a.id)),2) as id,'花生',花生 ,定价日期,收货日期,特价 from @aa ainsert into @表二  select right('00'+rtrim((select count(1) from @aa where id<=a.id)),2) as id,'地瓜',地瓜 ,定价日期,收货日期,特价 from @aa ainsert into @表二  select right('00'+rtrim((select count(1) from @aa where id<=a.id)),2) as id,'小麦',小麦 ,定价日期,收货日期,特价 from @aa aselect * from @表一
select * from @表二--结果
id   村名                   联合生产队                接货人        输入日期                                                   备注                                                                                                                                                                                                       
---- -------------------- -------------------- ---------- ------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
01   陈村                   一队                   cany       2007-07-25 00:00:00.000                                NULL
02   杨村                   四队                   cany       2007-07-25 00:00:00.000                                NULL
03   陈村                   一队                   Kate       2007-10-12 00:00:00.000                                NULL
04   陈村                   三队                   Kate       2007-10-12 00:00:00.000                                NULL（所影响的行数为 4 行）id   品种                   数量          定价日期                                                   收货日期                                                   是否特价 
---- -------------------- ----------- ------------------------------------------------------ ------------------------------------------------------ ---- 
01   花生                   20          2007-07-16 00:00:00.000                                2007-07-23 00:00:00.000                                N
02   花生                   17          2007-07-16 00:00:00.000                                2007-07-23 00:00:00.000                                N
03   花生                   14          2007-09-22 00:00:00.000                                2007-10-08 00:00:00.000                                y
04   花生                   16          2007-09-22 00:00:00.000                                2007-10-08 00:00:00.000                                y
01   地瓜                   6           2007-07-16 00:00:00.000                                2007-07-23 00:00:00.000                                N
02   地瓜                   8           2007-07-16 00:00:00.000                                2007-07-23 00:00:00.000                                N
03   地瓜                   9           2007-09-22 00:00:00.000                                2007-10-08 00:00:00.000                                y
04   地瓜                   12          2007-09-22 00:00:00.000                                2007-10-08 00:00:00.000                                y
01   小麦                   12          2007-07-16 00:00:00.000                                2007-07-23 00:00:00.000                                N
02   小麦                   16          2007-07-16 00:00:00.000                                2007-07-23 00:00:00.000                                N
03   小麦                   22          2007-09-22 00:00:00.000                                2007-10-08 00:00:00.000                                y
04   小麦                   19          2007-09-22 00:00:00.000                                2007-10-08 00:00:00.000                                y（所影响的行数为 12 行）

Answer 10

to Haiwer 
 还是没有理解到我的意思，新表一原来已经有2条记录了，旧表有4条记录，那么，合并之后新表一应该有6条记录新表一在合并完成之后应该是id     村名       联合生产队     接货人     输入日期         备注 
01     陈村       一队           cany       1/12/2008     NULL 
02     杨村       四队           Kate       1/18/2008     NULL   //这上面2条是新表的
03     陈村       一队           cany       7/25/2007     NULL 
04     杨村       四队           cany       7/25/2007     NULL 
05     陈村       一队           Kate       10/12/2007    NULL 
06     陈村       三队           Kate       10/12/2007    NULL   //这上面几条是旧表加上去的加入新纪录时，id 还是和旧表的取法一样，取最后一个id，加1

Answer 11

...
再改下
--改7楼的代码
declare @aa table(id nvarchar(10),村名 nvarchar(20),联合生产队 nvarchar(20),定价日期 datetime,收货日期 datetime,花生 int,地瓜 int,小麦 int,特价 nvarchar(10),接货人 nvarchar(20),输入日期 datetime,备注 nvarchar(200))
insert into @aa select '01','陈村','一队','7/16/2007','07/23/2007',20,6,12,'N','cany','7/25/2007',null
insert into @aa select '02','杨村','四队','7/16/2007','07/23/2007',17,8,16,'N','cany','7/25/2007',null
insert into @aa select '04','陈村','一队','9/22/2007','10/8/2007',14,9,22,'y','Kate', '10/12/2007',null
insert into @aa select '06','陈村','三队','9/22/2007','10/8/2007',16,12,19,'y','Kate','10/12/2007',nulldeclare @表一 table(id nvarchar(4),村名 nvarchar(20),联合生产队 nvarchar(20),接货人 nvarchar(10), 输入日期 datetime, 备注 nvarchar(200))
declare @表二 table(id nvarchar(4),品种 nvarchar(20),数量 int,定价日期 datetime,收货日期 datetime,是否特价 nvarchar(1))--旧记录
insert @表一 select
'01',     '陈村',       '一队',                   'cany',       '1/12/2008',         NULL 
union all select
'02',     '杨村',       '四队',                   'Kate',       '1/18/2008',         NULL 
insert @表二 select
'01',     '花生',16,       '1/4/2008',         '1/9/2008' ,      'N'                   
union all select
'01',     '地瓜',7,       '1/4/2008',         '1/9/2008' ,      'N'                 
union all select
'01',     '小麦',18,       '1/5/2008',         '1/9/2008' ,      'N' 
union all select
'01',     '玉米',12,       '1/5/2008',         '1/9/2008' ,      'N' 
union all select
'02',     '花生',18,       '1/8/2008',         '1/14/2008',     'Y' 
union all select
'02',     '小麦',23,       '1/8/2008',         '1/14/2008',     'Y' --求原数据最大id
declare @maxid int
select @maxid=cast(max(id) as int) from @表一--拆分
insert into @表一  select right('00'+rtrim(@maxid+(select count(1) from @aa where id<=a.id)),2) as id,村名,联合生产队,接货人,输入日期,备注 from @aa ainsert into @表二  select right('00'+rtrim(@maxid+(select count(1) from @aa where id<=a.id)),2) as id,'花生',花生 ,定价日期,收货日期,特价 from @aa ainsert into @表二  select right('00'+rtrim(@maxid+(select count(1) from @aa where id<=a.id)),2) as id,'地瓜',地瓜 ,定价日期,收货日期,特价 from @aa ainsert into @表二  select right('00'+rtrim(@maxid+(select count(1) from @aa where id<=a.id)),2) as id,'小麦',小麦 ,定价日期,收货日期,特价 from @aa aselect * from @表一
select * from @表二--结果
id   村名                   联合生产队                接货人        输入日期                                                   备注                                                                                                                                                                                                       
---- -------------------- -------------------- ---------- ------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
01   陈村                   一队                   cany       2008-01-12 00:00:00.000                                NULL
02   杨村                   四队                   Kate       2008-01-18 00:00:00.000                                NULL
03   陈村                   一队                   cany       2007-07-25 00:00:00.000                                NULL
04   杨村                   四队                   cany       2007-07-25 00:00:00.000                                NULL
05   陈村                   一队                   Kate       2007-10-12 00:00:00.000                                NULL
06   陈村                   三队                   Kate       2007-10-12 00:00:00.000                                NULL（所影响的行数为 6 行）id   品种                   数量          定价日期                                                   收货日期                                                   是否特价 
---- -------------------- ----------- ------------------------------------------------------ ------------------------------------------------------ ---- 
01   花生                   16          2008-01-04 00:00:00.000                                2008-01-09 00:00:00.000                                N
01   地瓜                   7           2008-01-04 00:00:00.000                                2008-01-09 00:00:00.000                                N
01   小麦                   18          2008-01-05 00:00:00.000                                2008-01-09 00:00:00.000                                N
01   玉米                   12          2008-01-05 00:00:00.000                                2008-01-09 00:00:00.000                                N
02   花生                   18          2008-01-08 00:00:00.000                                2008-01-14 00:00:00.000                                Y
02   小麦                   23          2008-01-08 00:00:00.000                                2008-01-14 00:00:00.000                                Y
03   花生                   20          2007-07-16 00:00:00.000                                2007-07-23 00:00:00.000                                N
04   花生                   17          2007-07-16 00:00:00.000                                2007-07-23 00:00:00.000                                N
05   花生                   14          2007-09-22 00:00:00.000                                2007-10-08 00:00:00.000                                y
06   花生                   16          2007-09-22 00:00:00.000                                2007-10-08 00:00:00.000                                y
03   地瓜                   6           2007-07-16 00:00:00.000                                2007-07-23 00:00:00.000                                N
04   地瓜                   8           2007-07-16 00:00:00.000                                2007-07-23 00:00:00.000                                N
05   地瓜                   9           2007-09-22 00:00:00.000                                2007-10-08 00:00:00.000                                y
06   地瓜                   12          2007-09-22 00:00:00.000                                2007-10-08 00:00:00.000                                y
03   小麦                   12          2007-07-16 00:00:00.000                                2007-07-23 00:00:00.000                                N
04   小麦                   16          2007-07-16 00:00:00.000                                2007-07-23 00:00:00.000                                N
05   小麦                   22          2007-09-22 00:00:00.000                                2007-10-08 00:00:00.000                                y
06   小麦                   19          2007-09-22 00:00:00.000                                2007-10-08 00:00:00.000                                y（所影响的行数为 18 行）