Solved

T-SQL - INSERTing range of rows from one table into antoher

Posted on 2008-11-02
5
584 Views
Last Modified: 2012-05-05
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?



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
)

Open in new window

0
Comment
Question by:Zaurb
5 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22862371
OrderID or ItemNo?

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.:




INSERT  ProdCycle
(
                OrderID,
                SerialNum,
                ItemNo
)
SELECT ProdID, SerialNum, ItemNo
FROM Serials
WHERE SerialNum=@SerialNum

Open in new window

0
 
LVL 1

Author Comment

by:Zaurb
ID: 22862412
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.

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
)

Open in new window

0
 
LVL 9

Accepted Solution

by:
jamesgu earned 125 total points
ID: 22862511
the code seems OK to me,

try to execute the select statement , see what do you get?

SELECT OrderID, SerialNum, ItemNo
FROM Serials
WHERE OrderID =
(
                SELECT OrderID,
                FROM Serials
                WHERE SerialNum=@SerialNum
)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22862626
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
)

Open in new window

0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 125 total points
ID: 22862641
With your corrected code:
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
)

Open in new window

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question