| Author |
 Topic  |
|
|
mohan123
Posting Yak Master
India
203 Posts |
 Posted - 01/03/2013 : 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
United Kingdom
220 Posts |
Posted - 01/03/2013 : 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 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47174 Posts |
Posted - 01/03/2013 : 11:04:23
|
whats purpose of merging onto same table? can you explain the scenario?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
mohan123
Posting Yak Master
India
203 Posts |
Posted - 01/03/2013 : 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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 01/03/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47174 Posts |
Posted - 01/04/2013 : 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/
|
|
|
|
mohan123
Posting Yak Master
India
203 Posts |
Posted - 01/04/2013 : 02:24:26
|
with in the same table i am sking chandu...for uidef table insert and update
P.V.P.MOhan |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47174 Posts |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 01/04/2013 : 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 |
|
|
|
mohan123
Posting Yak Master
India
203 Posts |
Posted - 01/04/2013 : 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 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/04/2013 : 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. |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 01/04/2013 : 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 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/04/2013 : 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. |
|
|
| |
Topic |
|
How to write merge statement for single Table
