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 t1USING dbo.UIDef_temp t2ON t1.UIDefId = t2.UIDefIdWHEN 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.LastModifiedDateWHEN 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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 UIDEFP.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-03 : 23:24:19
|
MERGE INTO dbo.UIDef AS t1USING Source t2ONyou 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
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 UIDEFP.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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 updateP.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
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 updateP.V.P.MOhan
Please post sample data in that table and also show us expected result...?--Chandu |
|
|
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 ALLSELECT 2, 'x' UNION ALLSELECT 3, 'x' UNION ALLnow if i want to update in mytable99for 3rd row by using MERGE INTO dbo.myTable99AS t1USING dbo.myTable99t2ON t1.c1 = t2.c1WHEN MATCHED THEN UPDATE SET t1.name = t2.nameWHEN NOT MATCHED BY TARGET THEN INSERT(c1, name) VALUES(t2.c1, t2.name);i think u can have clear view nowP.V.P.MOhan |
|
|
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. |
|
|
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 ALLSELECT 2, 'x' UNION ALLSELECT 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 = 3MERGE INTO dbo.myTable99AS t1USING (SELECT * FROM myTable99 WHERE col1 = 3) t2ON t1.col1 = t2.col1WHEN MATCHED THEN UPDATE SET t1.name = t2.name+' Updated'WHEN NOT MATCHED BY TARGET THENINSERT(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
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. |
|
|
|