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
 How to write merge statement for single Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 01/03/2013 :  10:55:16  Show Profile  Reply with Quote
i already used merge for comparing 2 tables of same data. but how to use merge for single table.

MERGE INTO dbo.UIDef AS t1
USING dbo.UIDef_temp t2
ON
t1.UIDefId = t2.UIDefId
WHEN MATCHED THEN
UPDATE SET
t1.PortalId = t2.PortalId,
t1.PageURL = t2.PageURL,
t1.PageObject = t2.PageObject,
t1.CreatedByUserId = t2.CreatedByUserId,
t1.CreatedDate = t2.CreatedDate,
t1.LastModifiedByUserId = t2.LastModifiedByUserId,
t1.LastModifiedDate = t2.LastModifiedDate

WHEN NOT MATCHED BY TARGET THEN
INSERT(
PageURL,
PageObject,
CreatedByUserId,
CreatedDate,
LastModifiedByUserId,
LastModifiedDate
)
VALUES(
t2.PageURL,
t2.PageObject,
t2.CreatedByUserId,
t2.CreatedDate,
t2.LastModifiedByUserId,
t2.LastModifiedDate
);

it is for 2 tables comparision

but how i need to in sertt or update for single table ???

P.V.P.MOhan

theboyholty
Posting Yak Master

United Kingdom
221 Posts

Posted - 01/03/2013 :  11:01:05  Show Profile  Visit theboyholty's Homepage  Reply with Quote
Not sure what you're trying to do here. You have 2 tables - UIDef and UIDef_temp. Is UIDef_temp a direct copy of UIDef?

Even if this is the case, merging two identical tables (in terms of structure AND data) is totally counter-productive as ALL the records will be matched and the UPDATE will not change anything.

Please correct me if I've misunderstood the question.



---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/03/2013 :  11:04:23  Show Profile  Reply with Quote
whats purpose of merging onto same table? can you explain the scenario?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 01/03/2013 :  11:05:28  Show Profile  Reply with Quote
thats what i am asking not comapring of 2 tables just need to do for single table UIDef just forget about the second table.Need to insert or update for UIDEF

P.V.P.MOhan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 01/03/2013 :  23:24:19  Show Profile  Reply with Quote
MERGE INTO dbo.UIDef AS t1
USING Source t2
ON
you can do both INSERT and UPDATE on Target table (dbo.UIDef) by using MERGE. If Source and target tables are same, then how can u do both INSERT and UPDATE?
What is your source here?

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/04/2013 :  02:23:15  Show Profile  Reply with Quote
quote:
Originally posted by mohan123

thats what i am asking not comapring of 2 tables just need to do for single table UIDef just forget about the second table.Need to insert or update for UIDEF

P.V.P.MOhan


What you'll be updating on same table? Inserting do make sense when you want to replicate same data for multiple ids/category values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 01/04/2013 :  02:24:26  Show Profile  Reply with Quote
with in the same table i am sking chandu...for uidef table insert and update

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/04/2013 :  02:36:32  Show Profile  Reply with Quote
insert and update based on what conditions?

please follow below link to understand how to post a question. Please help us to help you quicker by providing required information in below specified format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 01/04/2013 :  03:35:45  Show Profile  Reply with Quote
quote:
Originally posted by mohan123

with in the same table i am sking chandu...for uidef table insert and update
P.V.P.MOhan


Please post sample data in that table and also show us expected result...?

--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 01/04/2013 :  08:25:52  Show Profile  Reply with Quote
CREATE TABLE myTable99(Col1 int, name varchar(10))

INSERT INTO myTable99(Col1, name )
SELECT 1, 'x' UNION ALL
SELECT 2, 'x' UNION ALL
SELECT 3, 'x' UNION ALL

now if i want to update in mytable99

for 3rd row by using

MERGE INTO dbo.myTable99AS t1
USING dbo.myTable99t2
ON t1.c1 = t2.c1
WHEN MATCHED THEN
UPDATE SET t1.name = t2.name
WHEN NOT MATCHED BY TARGET THEN
INSERT(c1, name)
VALUES(t2.c1, t2.name);


i think u can have clear view now

P.V.P.MOhan
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/04/2013 :  08:30:21  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Apart from the typos - how can you ever not get a match if you are comparing the table with itself.
The match will only ever update the name value to itself.
Net effect - no change to the data.



==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 01/04/2013 :  09:02:11  Show Profile  Reply with Quote
CREATE TABLE myTable99(Col1 int, name varchar(10))

INSERT INTO myTable99(Col1, name )
SELECT 1, 'x' UNION ALL
SELECT 2, 'x' UNION ALL
SELECT 3, 'x' UNION ALL

--now if i want to update in mytable99
-- If you want to update only 3rd row, then sorce should be SELECT * FROM myTable99 WHERE col1 = 3

MERGE INTO dbo.myTable99AS t1
USING (SELECT * FROM myTable99 WHERE col1 = 3) t2
ON t1.col1 = t2.col1
WHEN MATCHED THEN
UPDATE SET t1.name = t2.name+' Updated'
WHEN NOT MATCHED BY TARGET THEN
INSERT(c1, name)
VALUES(t2.c1, t2.name);

For your understanding only, i used 3rd row as source and updated name with extra string "updated"...

--
Chandu
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/04/2013 :  09:50:42  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Still would the insert part ever be used?

Would be a good idea if you told us what you were trying to accomplish as I don't think this is the way to go about it.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
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.16 seconds. Powered By: Snitz Forums 2000