inserting data in master and detailed tables

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

inserting data in master and detailed tables

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-12-29 : 13:53:24

Hi All,

Iam having a master and detailed tables as follows :

create table kids

(name varchar(25),

ID INT PRIMARY key IDENTITY

)

create table wishlist

(kid_name VARCHAR(25) references kids

,artno INT

,description varchar(50)

,price decimal

)

iam extracting the xml elements using sql query and saving into temp table and updating the master and detailed tables as shown below, but iam getting the wrong output when i ran the query for the second time.
kindly help me on the same:

declare @x xml

set @x = '<SinterklaasWishlists>

<child>

<name>Tim</name>

<wishlist>

<article>

<artno>21491269</artno>

<description>Crane</description>

<price>12.50</price>

</article>

<article>

<artno>21499517</artno>

<description>Keyboard</description>

<price>10</price>

</article>

<article>

<artno>21521591</artno>

<description>Crime Investigation Game</description>

<price>9.95</price>

</article>

</wishlist>

</child>

<child>

<name>Tim2</name>

<wishlist>

<article>

<artno>21491269</artno>

<description>Crane</description>

<price>12.50</price>

</article>

<article>

<artno>21499517</artno>

<description>Keyboard</description>

<price>10</price>

</article>

<article>

<artno>21521591</artno>

<description>Crime Investigation Game</description>

<price>9.95</price>

</article>

</wishlist>

</child>

</SinterklaasWishlists>'

insert into #list(kid_name,artno,descriptio,price)

SELECT

c1.value('name[1]','VARCHAR(255)') AS ChildName,

c2.value('artno[1]','BIGINT') AS Article_Number,

c2.value('description[1]','varchar(255)') AS [Description],

c2.value('price[1]','varchar(255)') AS Price

FROM

@x.nodes('//child') T1(c1)

CROSS APPLY c1.nodes('wishlist/article') T2(c2);

--select * from #list

insert into kids(name)

select distinct kid_name from #list

select * from kids

--CREATE TABLE #list (

--kid_name VARCHAR(255),

--artno BIGINT,

--descriptio varchar(255),

-- price varchar(255))

--drop table #list

--select * from kids

--select * from #list

update #list set kid_name= ( select k.ID from kids k where k.name= kid_name)

insert into wishlist(kid_name,artno,description,price)
select * from #list

select * from kids

select * from wishlist

rams

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-29 : 13:58:09

you seriously DON'T mean that you are creating tables in master....right?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-29 : 14:51:13

No, he means a master-detail table design (like Orders-OrderLines)

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-29 : 15:06:18

Gail...do you want to BET?

I always win these bets

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-29 : 15:28:49

Yes, I'll bet.

quote:
Iam having a master and detailed tables as follows

There is absolutely nothing in his post about a specific database.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-29 : 15:33:00

...and that is how I will win

Use [Master]
GO

I have had "Production" DBA's screw this up

edit: and I hate XML

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-12-29 : 15:37:18

I read it like Gail too. What odds are you offering?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-29 : 15:43:51

quote:
Originally posted by X002548

...and that is how I will win

Use [Master]
GO

Which does not appear anywhere in the OP's code whatsoever.

I don't post the USE statements when I post code. Going to assume I have everything in master?

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-29 : 15:44:44

Rams, you say you're getting incorrect output. What's wrong and what should it be?

--
Gail Shaw
SQL Server MVP

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-12-30 : 07:33:56

This sounds like "I store my data in SQL SERVER, send the data to a web page, then save it again to SQL SERVER, but insist on using XML instead of native SQL."

XML is awesome, but only when used in the intended manner (kinda like Preparation H

)

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources