February 16, 2009 at 9:52 pm
Hi Guys,
i have a doubt in merge statement. i have 2 tables . Their structures as as follows:
CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15),
Maths decimal(5,2),
Science decimal(5,2),
GK decimal(5,2)
)
CREATE TABLE StudentTotalMarks
(
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
INSERT INTO StudentDetails
VALUES(1,'sa',91,92,93);
INSERT INTO StudentDetails
VALUES(2,'tech',94,95,96)
INSERT INTO StudentDetails
VALUES(3,'pillar',93,97,98)
Go
GO
INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
Go
If i want to insert the 4th row in StudentDetails table and i need to update the 2nd and 3rd rows maths=maths+5,science=science+10, and i need to delete 2nd row in the StudentTotalMarks table. Can i do it in the MERGE statemetent. please help me out on this.
February 16, 2009 at 10:11 pm
Records don’t have specific order in the table, so you can’t use merge and specify operations according to the order of the records in the table. With merge you have to specify the criteria on how to find matching records. This should be according to values in columns at both tables. Then you specify what to do when there is a match and what to do when there isn’t a match (at both tables source and destination). If you can find this kind of logic in your case, then you can use merge statement. If you can’t find this kind of logic (by the way I didn’t find it), then you’ll have to use another way to change your data. I recommend that you’ll read more about merge in BOL
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply