Insert stored procedure for related tables

I have two sets of related tables: Quote - QuoteDetail and Order - OrderItem

I need to copy Quote - QuoteDetail records to Order - OrderItem tables

I have the stored procedure up to this point: Insert a Quote in the Order table and get the new Order @.@.Identity.

I need to insert the QuoteDetail records into the OrderItem table using the new OrderID

Thank you for your help.Thanks to all who looked!

I figured it out. I was trying to make it harder than it actually was.|||Hope this helps !

CREATE PROCEDURE [InsertTest]
AS
INSERT INTO tblPerson(Login,Password,Email,DateCreated)
VALUES('jaja','aaa','22@.yahoo.com','02-03-04')
INSERT INTO tblSnippet(CategoryID,PersonID,Title,Description,DateCreated)
VALUES(1,@.@.IDENTITY,'HAHA','This is good article',GETDATE());
GO

|||can u post the solution :)|||Here is the code you requested. I was having a mental block over the Select versus the VAULES() to put the @.OrderID into the new Item records. The simple solution is setting the@.OrderID AS OrderID in the SELECT statement.

CREATE PROCEDURE dbo.ECPO_Quote_Convert
(
@.QuoteIDint,
@.OrderIDint output
)
AS
INSERT INTO ECP_Order
(
UserID, ...
(rest of the fields)
 )
SELECT
UserID, ...
(rest of the fields)
FROM
ECP_Quote
WHERE
QuoteID = @.QuoteID
SELECT @.OrderID = @.@.Identity
-- Insert QuoteDetail
INSERT INTO ECP_OrderItem
(
OrderID, ...
(rest of the fields)
 )
SELECT
@.OrderID AS OrderID, ...
(rest of the fields)
FROM
ECP_QuoteDetail
WHERE
QuoteID = @.QuoteID AND Quantity > 0

Insert Script from one table to another

I have a table in the SQL Server 2005 database that I want to copy 5 columns from one table into another table. I wanted to create a script to do this operation so I can use this on another database that would have different values for these columns.

If I were to use something like this:

DECLARE @.pdtno smallint,

@.publyear smallint,

@.issord int,

@.pdtrnno smallint,

@.layout varchar(60)

code to select the values from the original table and put the column values into variables

code to loop through each record

INSERT INTO layout_temp(pdtno, publyear, issord, pdtrnno, layout)

VALUES (@.pdtno, @.publyear, @.issord, @.pdtrnno, @.layout)

Does this make sense? How would I code the script to go through each record and put the column values from the original table into the variables then insert these into the copy of the original table?

Thanks in advance

? Why not just do: INSERT INTO layout_temp(pdtno, publyear, issord, pdtrnno, layout) SELECT pdtno, publyear, issord, pdtrnno, layout FROM layout-- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <nailers67@.discussions..microsoft.com> wrote in message news:06652218-e226-4a6e-98de-b9046345ef27@.discussions.microsoft.com... I have a table in the SQL Server 2005 database that I want to copy 5 columns from one table into another table. I wanted to create a script to do this operation so I can use this on another database that would have different values for these columns. If I were to use something like this: DECLARE @.pdtno smallint, @.publyear smallint, @.issord int, @.pdtrnno smallint, @.layout varchar(60) code to select the values from the original table and put the column values into variables code to loop through each record INSERT INTO layout_temp(pdtno, publyear, issord, pdtrnno, layout) VALUES (@.pdtno, @.publyear, @.issord, @.pdtrnno, @.layout) Does this make sense? How would I code the script to go through each record and put the column values from the original table into the variables then insert these into the copy of the original table? Thanks in advance|||

I believe that was a stupid question on my part. I think I tried to make it too difficult. That worked well. Thank you for your help. I appreciate it.