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
 How to write merge statement for single Table

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-01-03 : 10:55:16
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

226 Posts

Posted - 2013-01-03 : 11:01:05
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

52326 Posts

Posted - 2013-01-03 : 11:04:23
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

252 Posts

Posted - 2013-01-03 : 11:05:28
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-03 : 23:24:19
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

52326 Posts

Posted - 2013-01-04 : 02:23:15
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

252 Posts

Posted - 2013-01-04 : 02:24:26
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

52326 Posts

Posted - 2013-01-04 : 02:36:32
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-04 : 03:35:45
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

252 Posts

Posted - 2013-01-04 : 08:25:52
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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-04 : 08:30:21
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-04 : 09:02:11
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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-04 : 09:50:42
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
   

- Advertisement -