Insert value into 2 tables - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Insert value into 2 tables

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-06-12 : 04:06:37

I've tables as following,

CREATE TABLE [dbo].[PaymentH](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[payer] [int] NOT NULL,
	[paymentDte] [date] NOT NULL,
 CONSTRAINT [PK_PaymentH] PRIMARY KEY CLUSTERED 
(
	[idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[PaymentItem](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[paymentH] [int] NOT NULL,
	[paymentType] [int] NOT NULL,
	[amount] [decimal](10, 2) NOT NULL,
 CONSTRAINT [PK_PaymentItem] PRIMARY KEY CLUSTERED 
(
	[idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PaymentItem]  WITH CHECK ADD  CONSTRAINT [FK_PaymentItem_PaymentH1] FOREIGN KEY([paymentH])
REFERENCES [dbo].[PaymentH] ([idx])
GO

ALTER TABLE [dbo].[PaymentItem] CHECK CONSTRAINT [FK_PaymentItem_PaymentH1]
GO

My input as following,

declare @payer int
set @payer=1
declare @paymentDte date
set @paymentDte='20110213'

declare @data xml 
set @data='<data> 
<paymentItem><type>personal</type><amount>100.00</amount></paymentItem>
<paymentItem><type>business</type><amount>160.00</amount></paymentItem>
</data>'

I need help to built T-SQL. Then, my tables have a value as following

PaymentH
idx   | payer   | paymentDte
--------------------------------
1	    1	      2011-02-13

PaymentItem
idx   | paymentH    | paymentType    | amount
-----------------------------------------------
1       1             personal         100.00
2       1             business         160.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-12 : 04:58:54

[code]
DECLARE @PayerID int

insert into PaymentH([payer],
[paymentDte]
)
values(@payer,@paymentDte)

SELECT @PayerID=SCOPE_IDENTITY()

insert into PaymentItem (paymentH,paymentType,amount)
select @PayerID,t.u.value('./type[1]','varchar(100)'),t.u.value('./amount[1]','decimal(10,2)')
from @data.nodes('/data/paymentItem')t(u)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2011-06-12 : 05:46:27

Sir,

I heard SCOPE_IDENTITY() sometimes return incorrect value. Did you have any sample using output inserted?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-12 : 15:30:02

[code]
declare @PayerIds table (PayerId int);

insert into PaymentH([payer],
[paymentDte]
)
output inserted.idx into @PayerIds
values(@payer,@paymentDte)

insert into PaymentItem (paymentH,paymentType,amount)
select PayerId,t.u.value('./type[1]','varchar(100)'),t.u.value('./amount[1]','decimal(10,2)')
from @PayerIds cross apply @data.nodes('/data/paymentItem')t(u)[/code]If your inserts are all like this - one row at a time into the PaymentH table, this would work. Otherwise, you will need a way to correlate the data in the XML to the PayerIds.

As I writing this, I am thinking that if all your inserts are like this - one row at a time, you may not run into the scope_identity problem either. As far as I know, that bug occurs when there is parallelism, and this insert should not trigger parallelism. (Or you can set MAXDOP to 1.). Probably irrelevant to you because the output clause lets you do what you want to do without having to worry about any of that stuff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources