SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 inserting data in master and detailed tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jimoomba
Yak Posting Veteran

India
89 Posts

Posted - 12/29/2011 :  13:53:24  Show Profile  Reply with Quote
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 - 12/29/2011 :  13:58:09  Show Profile  Reply with Quote
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/


Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/29/2011 :  14:51:13  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
No, he means a master-detail table design (like Orders-OrderLines)

--
Gail Shaw
SQL Server MVP
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/29/2011 :  15:06:18  Show Profile  Reply with Quote
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/


Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/29/2011 :  15:28:49  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

Edited by - GilaMonster on 12/29/2011 15:31:23
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/29/2011 :  15:33:00  Show Profile  Reply with Quote
...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/



Edited by - X002548 on 12/29/2011 15:34:28
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/29/2011 :  15:37:18  Show Profile  Reply with Quote
I read it like Gail too. What odds are you offering?
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/29/2011 :  15:43:51  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/29/2011 :  15:44:44  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Rams, you say you're getting incorrect output. What's wrong and what should it be?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 12/30/2011 :  07:33:56  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000