表复制_读书人

表复制求助
CREATE TABLE [dbo].[PART_PROCESS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PARTID] [int] NOT NULL,
[STEPID] [int] NOT NULL,
[PROCESSID] [int] NOT NULL,
[SINGLETIME] [float] NULL,
[INSTID] [int] NULL,
[PROCEPROGRAM] [nvarchar](max) NULL,
[INSTUSETIME] [float] NULL,
[CHANGEPARTTIME] [float] NULL,
[ORDERNO] [float] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[PARTS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CODE] [nvarchar](max) NOT NULL,
[NAME] [nvarchar](max) NOT NULL,
[SPECIFICATION] [nvarchar](max) NOT NULL,
[DESCRIBE] [nvarchar](max) NULL
) ON [PRIMARY]

insert into parts(CODE,NAME,SPECIFICATION,DESCRIBE)values('200018','电磁铁','V5210.2-03','')
insert into parts(CODE,NAME,SPECIFICATION,DESCRIBE)values('200070','电磁铁','V5110.2-03','')
insert into PART_PROCESS(PARTID,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)values
('1','37','43','19','19','','0','7','5')
insert into PART_PROCESS(PARTID,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)values
('1','27','40','10','32','','0','7','2')
insert into PART_PROCESS(PARTID,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)values
('1','28','41','15','19','','0','7','3')
insert into PART_PROCESS(PARTID,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)values
('1','29','42','10','34','','0','7','4')
insert into PART_PROCESS(PARTID,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)values
('1','30','14','26','15','','0','0','1')[/size]

比如我现在查询
select parts.ID FROM parts where parts.name like '%电磁铁%'
会出来结果
1
2
我希望能够实现一个复制功能，把PARTID=1的在PART_PROCESS表里面内容复制一遍，但是PARTID改成2.也就是说2在PART_PROCESS表里面的数据等于物料ID1在PART_PROCESS表里面的数据.
语句在执行时，我可以手工指定匹配字段'电磁铁'但是物料2或者物料34等要自动出来。因为实际会有很多。
在线等
[最优解释]

INSERT INTO PART_PROCESS(partid,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)
 SELECT  b.id partid,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO
 FROM PART_PROCESS a INNER JOIN  parts b ON A.id&lt;&gt;b.id AND b.id&lt;&gt;1
 WHERE partid=1  AND b.NAME LIKE '%电磁铁%'

根据你的数据改了一下.
[其他解释]

INSERT INTO PART_PROCESS(partid,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)
SELECT 2 partid,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO
FROM PART_PROCESS WHERE partid=1

[其他解释]

引用:

SQL code123INSERT INTO PART_PROCESS(partid,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)SELECT 2 partid,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGR……

多谢，但是2是查询出来，因为实际会有很多，
[其他解释]
你的那个要求其实我看了4、5遍，还是不是很清楚，要不你直接用文字来说说：你想要什么？
------其他解决方案--------------------

我是两张表，parts表示物料表，里面有物料ID，物料号CODE，物料名称等
另一张表是PART_PROCESS实际是物料的工序信息。现在我有两个物料，物料ID1，2
这两个物料的共同点是 like '%电磁铁%' 返回真，真实环境会有很多like '%电磁铁%'返回真的物料。
现在，物料1的工序信息已经在PART_PROCESS表里面了。所有电磁铁（规格号会不一样，物料ID也不一样）物料的工序信息是一样的。所以我希望能够实现把所有电磁铁的物料，比如2，自动添加到PART_PROCESS表里面，除了PARTID字段是新添加的电磁铁的ID以外，其他都与物料ID1的工序一样。
不知道有没有说清楚。。
[其他解释]
算了吧我还是愿意看你给出测试数据然后再给出你想要的结果你的文字描述让人看得蛋疼
[其他解释]

INSERT INTO PART_PROCESS(partid,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)
SELECT  b.id partid,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO
FROM PART_PROCESS a INNER JOIN  parts b ON A.id&lt;&gt;b.id
WHERE partid=1  AND parts.NAME LIKE '%电磁铁%'

[其他解释]
请先清楚所有数据
测试数据
CREATE TABLE [dbo].[PART_PROCESS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PARTID] [int] NOT NULL,
[STEPID] [int] NOT NULL,
[PROCESSID] [int] NOT NULL,
[SINGLETIME] [float] NULL,
[INSTID] [int] NULL,
[PROCEPROGRAM] [nvarchar](max) NULL,
[INSTUSETIME] [float] NULL,
[CHANGEPARTTIME] [float] NULL,
[ORDERNO] [float] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[PARTS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CODE] [nvarchar](max) NOT NULL,
[NAME] [nvarchar](max) NOT NULL,
[SPECIFICATION] [nvarchar](max) NOT NULL,
[DESCRIBE] [nvarchar](max) NULL
) ON [PRIMARY]

insert into parts(CODE,NAME,SPECIFICATION,DESCRIBE)values('200018','电磁铁','V5210.2-03','')
insert into parts(CODE,NAME,SPECIFICATION,DESCRIBE)values('200070','电磁铁','V5110.2-03','')
insert into parts(CODE,NAME,SPECIFICATION,DESCRIBE)values('200071','轴芯','V5110.2-03','')
insert into PART_PROCESS(PARTID,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)values
('1','37','43','19','19','','0','7','5')
insert into PART_PROCESS(PARTID,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)values
('1','27','40','10','32','','0','7','2')
insert into PART_PROCESS(PARTID,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)values
('1','28','41','15','19','','0','7','3')
insert into PART_PROCESS(PARTID,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)values
('1','29','42','10','34','','0','7','4')
insert into PART_PROCESS(PARTID,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)values
('1','30','14','26','15','','0','0','1')

我希望执行一个语句后，select * from PART_PROCES 出现如下结果
ID,PARTID,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO
1137431919075
2127401032072
3128411519073
4129421034074
5130142615001
6237431919075
7227401032072
8228411519073
9229421034074
10230142615001

[其他解释]

引用:

SQL code1234INSERT INTO PART_PROCESS(partid,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO)SELECT b.id partid,STEPID,PROCESSID,SINGLETIME,INSTID,PROCE……

比较接近了，但是有个问题，原先在PART_PROCESS表里面的数据，物料ID=1的工序信息会重复插入一次，
[其他解释]
用我8楼的，刚才试了一下，8楼的才不重复
[其他解释]

引用:

SQL code1234INSERT INTO PART_PROCESS(partid,STEPID,PROCESSID,SINGLETIME,INSTID,PROCEPROGRAM,INSTUSETIME,CHANGEPARTTIME,ORDERNO) SELECT b.id partid,STEPID,PROCESSID,SINGLETIME,INSTID,PROC……

非常感谢，科学技术上第一生产力

表复制

热点推荐