Data Insertion

Question

Post reply

Data Insertion

kapil_kk

SSC-Insane

Points: 21316
More actions
October 10, 2013 at 6:18 am

#284432

Hi,
I am stuck into a scenario of data insertion.
please help
Create table table1
(
OfficeID int,
ProjectID int,
TaskID int,
TaskName varchar(100)
constraint pk_table primary key
(
OfficeID,
ProjectID,
TaskID
)
)
I am inserting data into this table through an XML file in which taskid is 0 always.
><INPUT><CID>17064</CID><OwnerID>17064</OwnerID><TaskId>0</TaskId>><MSG>test follow up</MSG></INPUT><INPUT><CID>17064</CID><OwnerID>17064</OwnerID><TaskId>0</TaskId>><MSG>test follow up 123</MSG></INPUT>
Everytime when data gets insert into table1 taskid should be max on basis of officeid and ProjectID.
I am storing this xml into a temp table then put join on base table with this temp table.
Output desired:
OfficeID PRojectID TaskID TaskName
17064 17064 1 Test follow up1 ----- Already exist in table
17064 17064 2 Test follow up2
17064 17064 3 Test follow up 123
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Your XML isn't very clear, but see if this helps

DECLARE @x XML='

<root>

<INPUT>

<CID>17064</CID>

<OwnerID>17064</OwnerID>

<TaskId>0</TaskId>

<MSG>test follow up</MSG>

</INPUT>

<INPUT>

<CID>17064</CID>

<OwnerID>17064</OwnerID>

<TaskId>0</TaskId>

<MSG>test follow up 123</MSG>

</INPUT>

</root>

';

SELECT x.input.value('(CID/text())[1]','INT') AS OfficeID,

x.input.value('(OwnerID/text())[1]','INT') AS ProjectID,

ROW_NUMBER() OVER(ORDER BY x.input) AS TaskID,

x.input.value('(MSG/text())[1]','VARCHAR(100)') AS TaskName

FROM @x.nodes('/root/INPUT') AS x(input);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Mark-101232 (10/10/2013)
Your XML isn't very clear, but see if this helps
DECLARE @x XML='
<root>
<INPUT>
<CID>17064</CID>
<OwnerID>17064</OwnerID>
<TaskId>0</TaskId>
<MSG>test follow up</MSG>
</INPUT>
<INPUT>
<CID>17064</CID>
<OwnerID>17064</OwnerID>
<TaskId>0</TaskId>
<MSG>test follow up 123</MSG>
</INPUT>
</root>
';
SELECT x.input.value('(CID/text())[1]','INT') AS OfficeID,
x.input.value('(OwnerID/text())[1]','INT') AS ProjectID,
ROW_NUMBER() OVER(ORDER BY x.input) AS TaskID,
x.input.value('(MSG/text())[1]','VARCHAR(100)') AS TaskName
FROM @x.nodes('/root/INPUT') AS x(input);

I have converted this xml andstored data into a temp table...

Also task need to get generated on basis of max taskid on basis of officeid,projectid AND taskid

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/