Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Unanswered: Check whether existed rows for either updating or inserting then updating!

    Hello all,

    My school data look like:
    Student Class Score
    -----------------------------
    A English 100
    A Math 85
    A Reading 90

    B English
    B Math

    There are three classes that requiring for each student (for example, student A has English, Math and Reading. I am not sure how to check whether the rows are existed in database and do update for score? If not existed row, perform to insert rows before update the score data.

    To simplify above data, I suppose to have 3 students: A, B, C. If my current data like above missing? I like to write T-SQL for insert and update to having the final results as following:

    Student Class Score
    -------------------------------
    A English 100
    A Math 85
    A Reading 90

    B English 45
    B Math 20
    B Reading 17

    C English 100
    C Math 90
    C Reading 100

    Any expertise T-SQL, please help with score parameter in stored procedure? Thanks in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How do you want to supply(or get?) the missing data?
    from file? or, from another table? or, ...
    Last edited by tonkuma; 05-13-13 at 17:04.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are there any scenario to update(or to correct) some data which alredy existed, like
    ('A' , 'Math' , 85) to ('A' , 'Math' , 88)

  4. #4
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    That is the rules that I need to fill all data. Even though if data are existing, missing or omitting. The scored update can override the current data, if not there then update and if missing rows will insert first then next to update.
    Last edited by avt2k6; 05-13-13 at 17:49.

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    That is the rules that I need to fill all data. Even though if data are existing, missing or omitting. The scored update can override the current data, if not there then update and if missing rows will insert first then next to update.
    Since these attributes are required, they define a student's report card. I will gues that the mssing data comes from a DEFAULT until you tell us otherwise.

    CREATE TABLE Report_Cards
    (student_name CHAR(1) NOT NULL PRIMARY KEY
    REFERENCES Students (student_name),
    english_score DEFAULT 0 INTEGER NOT NULL
    CHECK (english_score BETWEEN 0 AND 100),
    math_score DEFAULT 0 INTEGER NOT NULL
    CHECK (math_score BETWEEN 0 AND 100),
    reading_score DEFAULT 0 INTEGER NOT NULL
    CHECK (reading_score BETWEEN 0 AND 100),
    ..);

  6. #6
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Hi,

    You misunderstood what I need help in t-sql for update or insert data to above table such tblSchool which consists of three columns: student, Class and Score with pre-exited data as shown.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You can use the MERGE command to compare two tables and synchronise them. This solution requires that the final dataset is present in a table (or view). In the code below this table is called "TmpFinalSchool".
    Code:
    CREATE TABLE tblSchool(
    	Student		CHAR(1)		NOT NULL,
    	Class		VARCHAR(20)	NOT NULL,
    	Score		INTEGER		NULL
    		CHECK (Score BETWEEN 0 AND 100),
    	CONSTRAINT pk_tblSchool PRIMARY KEY (Student, Class)
    );
    
    INSERT INTO tblSchool (Student, Class, Score) VALUES
    ('A', 'English', 10),
    ('A', 'Math', 85),
    ('A', 'Reading', 90),
    
    ('B', 'English', NULL),
    ('B', 'Math', NULL)
    
    SELECT * FROM tblSchool
    
    CREATE TABLE TmpFinalSchool(
    	Student		CHAR(1)		NOT NULL,
    	Class		VARCHAR(20)	NOT NULL,
    	Score		INTEGER		NULL
    		CHECK (Score BETWEEN 0 AND 100)
    	CONSTRAINT pk_TmpFinalSchool PRIMARY KEY (Student, Class)
    );
    
    INSERT INTO TmpFinalSchool (Student, Class, Score) VALUES
    ('A', 'English', 100),
    ('A', 'Math', 85),
    ('A', 'Reading', 90),
    
    ('B', 'English', 45),
    ('B', 'Math', 20),
    ('B', 'Reading', 17),
    
    ('C', 'English', 100),
    ('C', 'Math', 90),
    ('C', 'Reading', 100)
    
    SELECT * FROM TmpFinalSchool
    
    MERGE tblSchool AS TARGET
    USING TmpFinalSchool AS SOURCE 
    ON (TARGET.Student = SOURCE.Student 
    	AND TARGET.Class = SOURCE.Class
    	) 
    --When records are matched, update 
    --the records if there is any change
    WHEN MATCHED 
    	AND COALESCE(TARGET.Score, -1) <> COALESCE(SOURCE.Score, -1)
    	THEN
    UPDATE SET TARGET.Score = SOURCE.Score
    --When no records are matched, insert
    --the incoming records from source
    --table to target table
    WHEN NOT MATCHED BY TARGET 
    	THEN 
    INSERT (Student, Class, Score) 
    VALUES (SOURCE.Student, SOURCE.Class, SOURCE.Score)
    --When there is a row that exists in target table and
    --same record does not exist in source table
    --then delete this record from target table
    WHEN NOT MATCHED BY SOURCE 
    	THEN 
    DELETE
    --$action specifies a column of type nvarchar(10) 
    --in the OUTPUT clause that returns one of three 
    --values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
    --according to the action that was performed on that row
    OUTPUT $action, 
    	DELETED.Student AS TargetStudent, 
    	DELETED.Class AS TargetClass, 
    	DELETED.Score AS TargetScore, 
    	INSERTED.Student AS SourceStudent, 
    	INSERTED.Class AS SourceClass, 
    	INSERTED.Score AS SourceScore; 
    
    SELECT @@ROWCOUNT;
    GO
    SELECT * FROm tblSchool ORDER BY 1, 2
    SELECT * FROM TmpFinalSchool ORDER BY 1, 2
    
    DROP TABLE tblSchool;
    DROP TABLE TmpFinalSchool;
    The contents of tblSchool is now equal to that of TmpFinalSchool.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Cool

    Hi,

    That what I am looking for helping. Your solution is perfect. Thanks again and really appreciate your time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •