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
 two tables insert and updation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divyaram
Posting Yak Master

India
177 Posts

Posted - 02/03/2010 :  04:39:06  Show Profile  Reply with Quote
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

India
177 Posts

Posted - 02/03/2010 :  05:18:50  Show Profile  Reply with Quote
Do anybody have any idea

Regards,
Divya
Go to Top of Page

rajpes
Starting Member

13 Posts

Posted - 02/03/2010 :  05:44:42  Show Profile  Reply with Quote
can you give your query and table descriptions?
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 02/03/2010 :  05:59:19  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
What error do you get? Post the proc in question

Harsh Athalye
http://www.letsgeek.net/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/03/2010 :  06:17:25  Show Profile  Reply with Quote
quote:
Originally posted by divyaram

Do anybody have any idea

Regards,
Divya


can you give more info on your scenario...with some sample data
Go to Top of Page

jimmyriddle
Starting Member

2 Posts

Posted - 02/03/2010 :  07:01:40  Show Profile  Visit jimmyriddle's Homepage  Reply with Quote
Hi have you got any more information?

This might help you

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

Edited by - jimmyriddle on 02/03/2010 07:02:06
Go to Top of Page

divyaram
Posting Yak Master

India
177 Posts

Posted - 02/03/2010 :  07:47:03  Show Profile  Reply with Quote
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
Go to Top of Page

divyaram
Posting Yak Master

India
177 Posts

Posted - 02/03/2010 :  07:58:29  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/03/2010 :  11:13:20  Show Profile  Reply with Quote
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)
Go to Top of Page

divyaram
Posting Yak Master

India
177 Posts

Posted - 02/04/2010 :  00:10:01  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/04/2010 :  03:32:59  Show Profile  Reply with Quote
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?
Go to Top of Page

divyaram
Posting Yak Master

India
177 Posts

Posted - 02/04/2010 :  03:51:22  Show Profile  Reply with Quote
kk i got the idea.. ill try like that

Regards,
Divya
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/04/2010 :  03:52:35  Show Profile  Reply with Quote
ok..lets us know how you got on
Go to Top of Page

divyaram
Posting Yak Master

India
177 Posts

Posted - 02/04/2010 :  04:15:09  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/04/2010 :  04:16:51  Show Profile  Reply with Quote
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
Go to Top of Page

divyaram
Posting Yak Master

India
177 Posts

Posted - 02/04/2010 :  04:29:14  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/04/2010 :  04:31:36  Show Profile  Reply with Quote
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?
Go to Top of Page

divyaram
Posting Yak Master

India
177 Posts

Posted - 02/04/2010 :  04:34:07  Show Profile  Reply with Quote
all are consistent

Regards,
Divya
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/04/2010 :  04:57:47  Show Profile  Reply with Quote
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?
Go to Top of Page

divyaram
Posting Yak Master

India
177 Posts

Posted - 02/04/2010 :  05:08:57  Show Profile  Reply with Quote
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
Go to Top of Page

divyaram
Posting Yak Master

India
177 Posts

Posted - 02/05/2010 :  08:44:43  Show Profile  Reply with Quote
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
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.17 seconds. Powered By: Snitz Forums 2000