| Author |
 Topic  |
|
|
divyaram
Posting Yak Master
India
177 Posts |
 Posted - 02/03/2010 : 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
India
177 Posts |
Posted - 02/03/2010 : 05:18:50
|
Do anybody have any idea
Regards,
Divya |
 |
|
|
rajpes
Starting Member
13 Posts |
Posted - 02/03/2010 : 05:44:42
|
| can you give your query and table descriptions? |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/03/2010 : 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 |
|
|
divyaram
Posting Yak Master
India
177 Posts |
Posted - 02/03/2010 : 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
India
177 Posts |
Posted - 02/03/2010 : 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
India
47157 Posts |
Posted - 02/03/2010 : 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
India
177 Posts |
Posted - 02/04/2010 : 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
India
47157 Posts |
Posted - 02/04/2010 : 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
India
177 Posts |
Posted - 02/04/2010 : 03:51:22
|
 kk i got the idea.. ill try like that
Regards,
Divya |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/04/2010 : 03:52:35
|
| ok..lets us know how you got on |
|
|
|
divyaram
Posting Yak Master
India
177 Posts |
Posted - 02/04/2010 : 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
India
47157 Posts |
Posted - 02/04/2010 : 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
India
177 Posts |
Posted - 02/04/2010 : 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
India
47157 Posts |
Posted - 02/04/2010 : 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
India
177 Posts |
Posted - 02/04/2010 : 04:34:07
|
all are consistent
Regards,
Divya |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/04/2010 : 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
India
177 Posts |
Posted - 02/04/2010 : 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
India
177 Posts |
|
| |
Topic |
|
|
|
two tables insert and updation
