Zaurb
asked on
T-SQL - INSERTing range of rows from one table into antoher
Hi!
I'm creating a stored procedure, that will accept a value from a user, check against one table and then copy corresponding rows from that table into another.
I've made the procedure, but ItemID remains all the same in all rows...
What do I do wrong here?
I'm creating a stored procedure, that will accept a value from a user, check against one table and then copy corresponding rows from that table into another.
I've made the procedure, but ItemID remains all the same in all rows...
What do I do wrong here?
CREATE PROC up_name
@SerialNo NVARCHAR(10)
AS
INSERT ProdCycle
(
OrderID,
SerialNum,
ItemNo
)
SELECT ProdID, SerialNum, ItemNo
FROM Serials
WHERE OrderID =
(
SELECT OrderID,
FROM Serials
WHERE SerialNum=@SerialNum
)
ASKER
Sorry, ItemID=ItemNo.
The Serials table contains OrderID column but it's not actually an ID column. It allows duplicate values. And SerialNum, instead is a unique value in this table. So, I need to pass SerialNum to query, that will check OrderID for this SerialNum and select all rows with the same OrderID to another table.
The Serials table is similar to the following sample:
OrderID SerialNum ItemID
945 1a21s23 231521
945 1a21s43 231411
945 1a21ac3 231311
102 1a21we3 231512
102 1a21sd3 231511
I need to pass a serial number, like 1a21s23 and insert all rows with OrderID 945 into another table.
The Serials table contains OrderID column but it's not actually an ID column. It allows duplicate values. And SerialNum, instead is a unique value in this table. So, I need to pass SerialNum to query, that will check OrderID for this SerialNum and select all rows with the same OrderID to another table.
The Serials table is similar to the following sample:
OrderID SerialNum ItemID
945 1a21s23 231521
945 1a21s43 231411
945 1a21ac3 231311
102 1a21we3 231512
102 1a21sd3 231511
I need to pass a serial number, like 1a21s23 and insert all rows with OrderID 945 into another table.
CREATE PROC up_name
@SerialNo NVARCHAR(10)
AS
INSERT ProdCycle
(
OrderID,
SerialNum,
ItemNo
)
SELECT OrderID, SerialNum, ItemNo
FROM Serials
WHERE OrderID =
(
SELECT OrderID,
FROM Serials
WHERE SerialNum=@SerialNum
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Think you just have an extra comma in code.
CREATE PROC up_name
@SerialNo NVARCHAR(10)
AS
INSERT ProdCycle
(
OrderID,
SerialNum,
ItemNo
)
SELECT ProdID, SerialNum, ItemNo
FROM Serials
WHERE OrderID =
(
SELECT OrderID /*, <-- remove this */
FROM Serials
WHERE SerialNum=@SerialNum
)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I see no ItemID
Also, you are passing in @SerialNum, but then looking up the OrderID from the same table. Why not do this because it is essentially what you are telling it to do.:
Open in new window