Help with a Stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a Stored procedure

Author

Topic

jadi
Starting Member

20 Posts

Posted - 2009-01-28 : 15:20:30

Help help me here!

I have a view right now which gets the data from different table.
I have two tables
HEADER and LINES
How can I write a store procedure so that the data from my view is written to the two tables.
I want some data from view to go to the HEADER and some to the LINES.

Please some one help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-28 : 15:25:07

Show us a data example as it's hard for us to imagine what your environment looks like with just words.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 03:55:14

seems like what you need is OUTPUT operator in sql 2005. have a look at syntax in books online

jadi
Starting Member

20 Posts

Posted - 2009-01-29 : 08:48:38

I want to write two insert statements into different tables in a single stored procedure. The values are coming from a view.

Can you write an expample for me visakh or tkizer.
I need help urgently!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 09:04:12

quote:
Originally posted by jadi

I want to write two insert statements into different tables in a single stored procedure. The values are coming from a view.

Can you write an expample for me visakh or tkizer.
I need help urgently!

are you using sql 2005?

jadi
Starting Member

20 Posts

Posted - 2009-01-29 : 09:29:14

2007

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-29 : 09:32:25

quote:
Originally posted by jadi

2007

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 09:33:02

2007? there's no such version of sql server. what does below return?

SELECT @@VERSION

jadi
Starting Member

20 Posts

Posted - 2009-01-29 : 09:34:43

SORRY FRND

IT IS

Microsoft SQL Server 2005 - 9.00.3042.00

jadi
Starting Member

20 Posts

Posted - 2009-01-29 : 10:23:16

ALTER PROCEDURE [dbo].[FILL_HEADER_AND_LINES]

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO HEADERS(ORDER_NUMBER,CUSTOMER_PO)
SELECT ORDER_NUMBER,CUSTPO
FROM VW_DATA_QUERY
if @@error <> 0
rollback

INSERT INTO LINES(INVOICE_NUMBER,INVOICE_AMOUNT,INVOICE_ADDRESS)
SELECT INVOICE_NUMBER,SALES_PRICE,INVOICE_ADDRESS
FROM VW_DATA_QUERY
if @@error <> 0
rollback

END
GO

===========This SP works Now..........

In this SP as you see I am inserting in two tables. HEADER and LINES.
I want the line to reference the HEADER KEYFIELD.
How Can I do that in the SP? Is it possible?
In the LINES table REFKEY field I want to insert the Key of that HEADER

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 10:42:20

Something like this

CREATE PROCEDURE [dbo].[FILL_HEADER_AND_LINES]
AS

SET NOCOUNT ON

INSERT	HEADERS
	(
		ORDER_NUMBER,
		CUSTOMER_PO
	)
OUTPUT	inserted.pkCol,
	inserted.INVOICE_NUMBER,
	inserted.SALES_PRICE,
	inserted.INVOICE_ADDRESS
INTO	LINES
	(
		KeyField,
		INVOICE_NUMBER,
		INVOICE_AMOUNT,
		INVOICE_ADDRESS
	)
SELECT	ORDER_NUMBER,
	CUSTPO
FROM	VW_DATA_QUERY