two tables insert and updation

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

two tables insert and updation

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-03 : 04:39:06

Hi all,

I have to insert or update two tables, i have done for one table with
merge satement is it possible to use two merges in same procedure itself to update or insert int to two differnt tables. ihave tried but its throwing error.

Regards,
Divya

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-03 : 05:18:50

Do anybody have any idea

Regards,
Divya

rajpes
Starting Member

13 Posts

Posted - 2010-02-03 : 05:44:42

can you give your query and table descriptions?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-03 : 05:59:19

What error do you get? Post the proc in question

Harsh Athalye
http://www.letsgeek.net/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 06:17:25

quote:
Originally posted by divyaram

Do anybody have any idea

Regards,
Divya

can you give more info on your scenario...with some sample data

jimmyriddle
Starting Member

2 Posts

Posted - 2010-02-03 : 07:01:40

Hi have you got any more information?

This might help you

[url]http://www.yourcodefactory.com/forum/topics.aspx?ForumID=22[/url]

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-03 : 07:47:03

FSP_User_Address_Insert_copy
'<FIS_User_ContactInfo>
<FIS_User_Address common="1" Add_ID="155" Industry_ID="1" Org_Cat_ID="1" Org_Type_ID="1" Org_Name="jan88newww" Dept_Name="jan24_Department" Lab_Off_Name="jan24_labOfficeName" Addr_Line1="jan24_Address Line1" Addr_Line2="jan24_Address Line2" Addr_Line3="jan24_Address Line3" City="jan24_City" Zip_Post_Code="jan24_ZipPostalCode" State_Province="jan24_State Provience" Country="13" Phone="2870787" Phone_Extn="9911" Web_Off_Lab="jan24_Website lab Office Name" Web_Div_Dept="jan24_website Division Department" Web_Org_per="jan24_Website Organization" Addr_Flag="0" Addr_Type="H" >
<Equivalent_Names_Organization>
<Org_Names Eqiv_Name="oRGANIZAotionNamemmmmm default " Add_Eqv_ID=" 587" Type="0" Lang_id="1" common="1" />
</Equivalent_Names_Organization>
</FIS_User_Address>
<FIS_User_Address common="2" Add_ID="0" Industry_ID="21" Org_Cat_ID="7" Org_Type_ID="4" Org_Name="76" Dept_Name="67" Lab_Off_Name="67" Addr_Line1="67" Addr_Line2="7676" Addr_Line3="76" City="76" Zip_Post_Code="76" State_Province="76" Country="31" Phone="7667666" Phone_Extn="7676" Web_Off_Lab="76" Web_Div_Dept="7676" Web_Org_per="767676" Addr_Flag="0" Addr_Type="H" >
<Equivalent_Names_Organization>
<Org_Names Eqiv_Name="569" Add_Eqv_ID="0" Type="0" Lang_id="1" common="2"/>
</Equivalent_Names_Organization>
</FIS_User_Address>
</FIS_User_ContactInfo>
',12
--SELECT * FROM FIS_User_Address
--SELECT * FROM FIS_User_OrgLab_EquvNames
*/

-- =============================================
ALTER PROCEDURE [dbo].[FSP_User_Address_Insert_Copy]
@XML NVARCHAR(MAX),
@User_ID INT
As
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @idoc INT,@MinID INT,@MaxID INT,@Address_ID INT

DECLARE @data XML
set @data=@XML

CREATE TABLE #User_Address
(
ID INT IDENTITY,
Add_ID INT,
User_ID INT,
common int,
Industry_ID int,
Org_Cat_ID int,
Org_Type_ID int,
Org_Name NVARCHAR(150),
Dept_Name NVARCHAR(150),
Lab_Off_Name NVARCHAR(150),
Addr_Line1 NVARCHAR(100),
Addr_Line2 NVARCHAR(100),
Addr_Line3 NVARCHAR(100),
City NVARCHAR(50),
Zip_Post_Code NVARCHAR(50),
State_Province NVARCHAR(50),
Country INT,
Phone NUMERIC(18,0),
Phone_Extn NUMERIC(18,0),
Web_Off_Lab NVARCHAR(100),
Web_Div_Dept NVARCHAR(100),
Web_Org_per NVARCHAR(100),
Addr_Flag INT,
Addr_Type CHAR(1),
Created_By int,
Add_Eqv_ID INT,
Eqiv_Name NVARCHAR(200),
Type TINYINT,
Lang_ID INT,
Address_ID INT
)

INSERT INTO #User_Address

(Add_ID ,
common ,
Industry_ID ,
Org_Cat_ID ,
Org_Type_ID ,
Org_Name ,
Dept_Name ,
Lab_Off_Name ,
Addr_Line1 ,
Addr_Line2 ,
Addr_Line3 ,
City ,
Zip_Post_Code ,
State_Province ,
Country ,
Phone ,
Phone_Extn ,
Web_Off_Lab ,
Web_Div_Dept ,
Web_Org_per ,
Addr_Flag ,
Addr_Type ,
Eqiv_Name ,
Add_Eqv_ID ,
Type ,
Lang_ID )
SELECT
p.n.value('@Add_ID', 'INT') AS Add_ID,
p.n.value('@common', 'INT') AS common,
p.n.value('@Industry_ID', 'INT') AS Industry_ID,
p.n.value('@Org_Cat_ID', 'INT') AS Org_Cat_ID,
p.n.value('@Org_Type_ID', 'INT') AS Org_Type_ID,
p.n.value('@Org_Name', 'VARCHAR(200)') AS Org_Name,
p.n.value('@Dept_Name', 'VARCHAR(200)') AS Dept_Name,
p.n.value('@Lab_Off_Name', 'VARCHAR(200)') AS Lab_Off_Name,
p.n.value('@Addr_Line1', 'VARCHAR(200)') AS Addr_Line1,
p.n.value('@Addr_Line2' ,'VARCHAR(200)') AS Addr_Line2,
p.n.value('@Addr_Line3' ,'VARCHAR(200)') AS Addr_Line3,
p.n.value('@City' ,'VARCHAR(200)') AS City,
p.n.value('@Zip_Post_Code' ,'VARCHAR(200)') AS Zip_Post_Code,
p.n.value('@State_Province' ,'VARCHAR(200)') AS State_Province,
p.n.value('@Country' ,'VARCHAR(200)') AS Country,
p.n.value('@Phone' ,'VARCHAR(200)') AS Phone,
p.n.value('@Phone_Extn' ,'VARCHAR(200)') AS Phone_Extn,
p.n.value('@Web_Off_Lab' ,'VARCHAR(200)') AS Web_Off_Lab,
p.n.value('@Web_Div_Dept' ,'VARCHAR(200)') AS Web_Div_Dept,
p.n.value('@Web_Org_per' ,'VARCHAR(200)') AS Web_Org_per,
p.n.value('@Addr_Flag' ,'INT') AS Addr_Flag,
p.n.value('@Addr_Type' ,'CHAR(1)') AS Addr_Type,
t.n.value('@Eqiv_Name', 'VARCHAR(200)') AS Eqiv_Name,
t.n.value('@Add_Eqv_ID', 'INT') AS Add_Eqv_ID,
t.n.value('@Type', 'INT') AS Type,
t.n.value('@Lang_id', 'INT') AS Lang_ID
FROM @data.nodes('FIS_User_ContactInfo/FIS_User_Address') AS p(n)
CROSS APPLY n.nodes('Equivalent_Names_Organization/Org_Names') AS t(n)

--BEGIN TRAN
--BEGIN TRY

MERGE FIS_User_Address AS FIS_User_Address
USING #User_Address AS #User_Address
ON
(FIS_User_Address.Industry_ID=#User_Address.Industry_ID AND
FIS_User_Address.Org_Cat_ID=#User_Address.Org_Cat_ID AND
FIS_User_Address.Org_Type_ID=#User_Address.Org_Type_ID AND
FIS_User_Address.Org_Name=#User_Address.Org_Name AND
FIS_User_Address.Dept_Name=#User_Address.Dept_Name AND
FIS_User_Address.Lab_Off_Name=#User_Address.Lab_Off_Name AND
FIS_User_Address.Addr_Line1=#User_Address.Addr_Line1 AND
FIS_User_Address.Addr_Line2=#User_Address.Addr_Line2 AND
FIS_User_Address.Addr_Line3=#User_Address.Addr_Line3 AND
FIS_User_Address.City=#User_Address.City AND
FIS_User_Address.Zip_Post_Code=#User_Address.Zip_Post_Code AND
FIS_User_Address.State_Province=#User_Address.State_Province AND
FIS_User_Address.Country=#User_Address.Country AND
FIS_User_Address.Phone=#User_Address.Phone AND
FIS_User_Address.Phone_Extn=#User_Address.Phone_Extn AND
FIS_User_Address.Web_Off_Lab=#User_Address.Web_Off_Lab AND
FIS_User_Address.Web_Div_Dept=#User_Address.Web_Div_Dept AND
FIS_User_Address.Web_Org_per=#User_Address.Web_Org_per AND
FIS_User_Address.Addr_Flag=#User_Address.Addr_Flag AND
FIS_User_Address.Addr_Type=#User_Address.Addr_Type)
WHEN MATCHED THEN
UPDATE SET
FIS_User_Address.Industry_ID=#User_Address.Industry_ID,
FIS_User_Address.Org_Cat_ID=#User_Address.Org_Cat_ID,
FIS_User_Address.Org_Type_ID=#User_Address.Org_Type_ID ,
FIS_User_Address.Org_Name=#User_Address.Org_Name ,
FIS_User_Address.Dept_Name=#User_Address.Dept_Name ,
FIS_User_Address.Lab_Off_Name=#User_Address.Lab_Off_Name ,
FIS_User_Address.Addr_Line1=#User_Address.Addr_Line1 ,
FIS_User_Address.Addr_Line2=#User_Address.Addr_Line2 ,
FIS_User_Address.Addr_Line3=#User_Address.Addr_Line3 ,
FIS_User_Address.City=#User_Address.City ,
FIS_User_Address.Zip_Post_Code=#User_Address.Zip_Post_Code ,
FIS_User_Address.State_Province=#User_Address.State_Province ,
FIS_User_Address.Country=#User_Address.Country ,
FIS_User_Address.Phone=#User_Address.Phone ,
FIS_User_Address.Phone_Extn=#User_Address.Phone_Extn ,
FIS_User_Address.Web_Off_Lab=#User_Address.Web_Off_Lab ,
FIS_User_Address.Web_Div_Dept=#User_Address.Web_Div_Dept ,
FIS_User_Address.Web_Org_per=#User_Address.Web_Org_per ,
FIS_User_Address.Addr_Flag=#User_Address.Addr_Flag ,
FIS_User_Address.Addr_Type=#User_Address.Addr_Type,
FIS_User_Address.Modified_By=@User_ID ,
FIS_User_Address.Modified_Date=GETDATE()
--FROM #User_Address WHERE #User_Address.Add_ID=FIS_User_Address.Address_ID
WHEN NOT MATCHED THEN

INSERT ( User_ID ,
Industry_ID ,
Org_Cat_ID ,
Org_Type_ID ,
Org_Name ,
Dept_Name ,
Lab_Off_Name ,
Addr_Line1 ,
Addr_Line2 ,
Addr_Line3 ,
City ,
Zip_Post_Code ,
State_Province ,
Country ,
Phone ,
Phone_Extn ,
Web_Off_Lab ,
Web_Div_Dept ,
Web_Org_per ,
Addr_Flag ,
Addr_Type,
Created_By ,
Created_Date)
VALUES
( @User_ID ,
#User_Address.Industry_ID ,
#User_Address.Org_Cat_ID ,
#User_Address.Org_Type_ID ,
#User_Address.Org_Name ,
#User_Address.Dept_Name ,
#User_Address.Lab_Off_Name ,
#User_Address.Addr_Line1 ,
#User_Address.Addr_Line2 ,
#User_Address.Addr_Line3 ,
#User_Address.City ,
#User_Address.Zip_Post_Code ,
#User_Address.State_Province ,
#User_Address.Country ,
#User_Address.Phone ,
#User_Address.Phone_Extn ,
#User_Address.Web_Off_Lab ,
#User_Address.Web_Div_Dept ,
#User_Address.Web_Org_per ,
#User_Address.Addr_Flag ,
#User_Address.Addr_Type,
@User_ID ,
GETDATE());

DROP TABLE #User_Address

END

this is how i have done

Regards,
Divya

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-03 : 07:58:29

Table structure is like this

Address_ID int
User_ID int
Addr_Flag tinyint
Addr_Type nchar(1)
Addr_Line1 nvarchar(100)
Addr_Line2 nvarchar(100)
Addr_Line3 nvarchar(100)
City nvarchar(50)
Zip_Post_Code nvarchar(50)
State_Province nvarchar(50)
Country int
Phone numeric(18, 0)
Phone_Extn numeric(18, 0)
Phone_Mob numeric(18, 0)
Web_Off_Lab nvarchar(100)
Web_Div_Dept nvarchar(100)
Web_Org_per nvarchar(100)
Is_Mail_Addr bit
Modified_By int
Modified_Date datetime
Created_By int
Created_Date datetime
Industry_ID int
Org_Cat_ID int
Org_Type_ID int
Org_Name nvarchar(150)
Dept_Name nvarchar(150)
Lab_Off_Name nvarchar(150)
IsDeleted bit

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 11:13:20

divya please post only problematic part (or if thats complex a mock up of scenario by means of some simple sample data will do. but make sure you post data along table structure rather than queries)

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-04 : 00:10:01

quote:
Originally posted by rajpes

can you give your query and table descriptions?

Hi visakh rajpes asked for query that y i post the whole query which i have done

Actually merge is working if i put the condition for one table... if put secong merge for insert and update second table it will show error
multiple part can't be bounded... so is it possible to use two merge in same procedure itself....will it work if i put two merges

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 03:32:59

quote:
Originally posted by divyaram

quote:
Originally posted by rajpes

can you give your query and table descriptions?

Hi visakh rajpes asked for query that y i post the whole query which i have done

Actually merge is working if i put the condition for one table... if put secong merge for insert and update second table it will show error
multiple part can't be bounded... so is it possible to use two merge in same procedure itself....will it work if i put two merges

Regards,
Divya

you're trying to do two merges on same table simultaneously? what about putting intermediate result somewhere and then doing merge at last?

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-04 : 03:51:22

kk i got the idea.. ill try like that

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 03:52:35

ok..lets us know how you got on

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-04 : 04:15:09

i made some changes inpu xml format all input i made in single node and tried it to update with condition which matches but again got same problem as multipart identifier...
because here
MERGE FIS_User_Address AS FIS_User_Address
USING #User_Address AS #User_Address

we can specify only one table i need mention two table name merge will not support like that

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 04:16:51

quote:
Originally posted by divyaram

i made some changes inpu xml format all input i made in single node and tried it to update with condition which matches but again got same problem as multipart identifier...
because here
MERGE FIS_User_Address AS FIS_User_Address
USING #User_Address AS #User_Address

we can specify only one table i need mention two table name merge will not support like that

Regards,
Divya

can you please post a mock up data to explain your scenario . i particularly want to understand need of merging on two tables

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-04 : 04:29:14

input xml format

<FIS_User_ContactInfo>
<FIS_User_Address Name="Divya"add_id ="0"user_id="101" adress="india" deptname="development">
<Names_Organization>
<FIS_Org_Names user_id="101" Add_Eqv_ID=" 587" Type="0" Lang_id="1" />
</Names_Organization>
</FIS_User_Address>
<FIS_User_Address Name="joe"add_id ="1"user_id="100" adress="india" deptname="development">
<Names_Organization>
<FIS_Org_Names user_id="100" Add_Eqv_ID=" 585" Type="0" Lang_id="1" />
</Names_Organization>
</FIS_User_Address>
</FIS_User_ContactInfo>

two tables are ther FIS_User_Address and FIS_Org_Names
if add_Id = 0 its an insert else its update

i need to update or inert into user_address table and org_names tables common column in two tables is user_id
this is the scenario

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 04:31:36

quote:
Originally posted by divyaram

input xml format

<FIS_User_ContactInfo>
<FIS_User_Address Name="Divya"add_id ="0"user_id="101" adress="india" deptname="development">
<Names_Organization>
<FIS_Org_Names user_id="101" Add_Eqv_ID=" 587" Type="0" Lang_id="1" />
</Names_Organization>
</FIS_User_Address>
<FIS_User_Address Name="joe"add_id ="1"user_id="100" adress="india" deptname="development">
<Names_Organization>
<FIS_Org_Names user_id="100" Add_Eqv_ID=" 585" Type="0" Lang_id="1" />
</Names_Organization>
</FIS_User_Address>
</FIS_User_ContactInfo>

two tables are ther FIS_User_Address and FIS_Org_Names
if add_Id = 0 its an insert else its update

i need to update or inert into user_address table and org_names tables common column in two tables is user_id
this is the scenario

Regards,
Divya

are columns in table consistent with xml attributes?

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-04 : 04:34:07

all are consistent

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 04:57:47

quote:
Originally posted by divyaram

all are consistent

Regards,
Divya

then isnt it a matter of dumping data to temp tables and doing insert/update of main tables from them?

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-04 : 05:08:57

sorry i missed one condition which has to be checked in second table insertion

<FIS_User_ContactInfo>
<FIS_User_Address Name="Divya"add_id ="0"user_id="101" adress="india" deptname="development">
<Names_Organization>
<FIS_Org_Names user_id="101" Add_Eqv_ID=" 587" Type="0" Lang_id="1"Add_OrgName="0" />
</Names_Organization>
</FIS_User_Address>
<FIS_User_Address Name="joe"add_id ="1"user_id="100" adress="india" deptname="development">
<Names_Organization>
<FIS_Org_Names user_id="100" Add_Eqv_ID=" 585" Type="0" Lang_id="1"Add_OrgName="1" />
</Names_Organization>
</FIS_User_Address>
</FIS_User_ContactInfo>

one more condition is there likie the first table Add_OrgName="0" then insert else update

Regards,
Divya

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-05 : 08:44:43

http://www.sqlservercentral.com/articles/Advanced+Querying/3122/

we can use two merges in single sp .. i got this link recently .. i think this link will help anyone who lokking for more than one merge in a single SP

Regards,
Divya

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources