Solved

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

Posted on 2008-11-02
5
557 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now