none
insert statement using where clause

    Question

  • HI,

         i want to insert data into table using where clause. is it possible..? i expect my store procedure will be like:

      

    ALTER PROCEDURE [dbo].[SP_PRESCRIPTED_ADVICE_AND_REVIEW_INSERT]
    @Mrn_No	varchar(50),
    @Entry_Date	datetime,
    @Doctor_Advice	varchar(50),
    @Review_Patient	varchar(50)
    AS
    BEGIN
    SET NOCOUNT ON
    	BEGIN
    		INSERT INTO dbo.t_PRESCRIPTION
          (
      				Doctor_Advice,
    				Review_Patient
          )
       VALUES
          (
            @Mrn_No,
    				@Entry_Date,
    				@Doctor_Advice,
    				@Review_Patient
           )
           where Mrn_No=@Mrn_No and Entry_Date =@Entry_Date
           
           
       END
       
    END
    
    

       But i got error on Where. is there any other way...? thank's in advance.


    Regards atik sarker
    Monday, July 04, 2011 6:54 PM

Answers

  • Please post real nd not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

    In see that your “careful research” lead to VARCHAR(50) for your strings – even a Tweet is 140 characters long.

    You did not use the DATE data type because you need to track data to the micro second. Please stop putting “sp_' on stored procedures unless you really want them to be system procs; never put the silly “t-” prefix on table names. Since a table is a set , its name has to be plural or collective noun. Procedure names are “<verb>_<object>”.

    Why did you think that an INSERT has a WHERE clause?

    Why do you think that using tabs in program text is a good idea? Even if you never read the research about eye movement, you ought to know about it from experience. You just added about 12% more maintenance time to large systems. 


    CREATE PROCEDURE Insert_Prescription_Advice
    @in_mrn_nbr CHAR(10), @in_entry_date DATE = CURRENT_TIMESTAMP,
    @in_doctor_advice_txt VARCHAR(500),
    @in_review_patient_txt VARCHAR(500))
    AS
    BEGIN
    SET NOCOUNT ON;
    INSERT INTO Prescriptions (doctor_advice, review_patient)
    SELECT X.*
      FROM VALUES(@in_mrn_no, @in_entry_date,
                  @in_doctor_advice, @in_review_patient)
           AS X(mrn_no, entry_date, doctor_advice, review_patient)
     WHERE mrn_no = @in_mrn_no
       AND entry_date = @in_entry_date;
    END;

    The VALUES clause is called a row constructor and it builds a local virtual table that can go into a SELECT. But there is another problem; did you want an INSERT INTO or an UPDATE of an existing row?

    CREATE PROCEDURE Update_Prescription_Advice
    @in_mrn_nbr CHAR(10), @in_entry_date DATE,
    @in_doctor_advice_txt VARCHAR(500),
    @in_review_patient_txt VARCHAR(500))
    AS
    BEGIN
    SET NOCOUNT ON;
    UPDATE Prescriptions
       SET doctor_advice = @in_doctor_advice,
           review_patient = @in_review_patient
     WHERE mrn_no = @in_mrn_no
       AND entry_date = @in_entry_date;
    END;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    • Marked as answer by atik sarker Tuesday, July 05, 2011 3:59 AM
    Monday, July 04, 2011 8:29 PM
  • No, you can't have WHERE clause in your INSERT statement

    You need to change your INSERT statement to INSERT SELECT something like below

     
    insert into dbo.t_PRESCRIPTION(col1, col2, col3)
     SELECT col1,col2, col3 from tablename 
    WHERE Mrn_No=@Mrn_No and Entry_Date =@Entry_Date


    • Marked as answer by atik sarker Tuesday, July 05, 2011 4:00 AM
    Monday, July 04, 2011 7:02 PM
  • You need to insert the result of the select statement. Also, the number of fields to insert should match number of fields in the select statement.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by atik sarker Tuesday, July 05, 2011 4:01 AM
    Monday, July 04, 2011 7:02 PM

All replies

  • No, you can't have WHERE clause in your INSERT statement

    You need to change your INSERT statement to INSERT SELECT something like below

     
    insert into dbo.t_PRESCRIPTION(col1, col2, col3)
     SELECT col1,col2, col3 from tablename 
    WHERE Mrn_No=@Mrn_No and Entry_Date =@Entry_Date


    • Marked as answer by atik sarker Tuesday, July 05, 2011 4:00 AM
    Monday, July 04, 2011 7:02 PM
  • You need to insert the result of the select statement. Also, the number of fields to insert should match number of fields in the select statement.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by atik sarker Tuesday, July 05, 2011 4:01 AM
    Monday, July 04, 2011 7:02 PM
  • Please post real nd not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

    In see that your “careful research” lead to VARCHAR(50) for your strings – even a Tweet is 140 characters long.

    You did not use the DATE data type because you need to track data to the micro second. Please stop putting “sp_' on stored procedures unless you really want them to be system procs; never put the silly “t-” prefix on table names. Since a table is a set , its name has to be plural or collective noun. Procedure names are “<verb>_<object>”.

    Why did you think that an INSERT has a WHERE clause?

    Why do you think that using tabs in program text is a good idea? Even if you never read the research about eye movement, you ought to know about it from experience. You just added about 12% more maintenance time to large systems. 


    CREATE PROCEDURE Insert_Prescription_Advice
    @in_mrn_nbr CHAR(10), @in_entry_date DATE = CURRENT_TIMESTAMP,
    @in_doctor_advice_txt VARCHAR(500),
    @in_review_patient_txt VARCHAR(500))
    AS
    BEGIN
    SET NOCOUNT ON;
    INSERT INTO Prescriptions (doctor_advice, review_patient)
    SELECT X.*
      FROM VALUES(@in_mrn_no, @in_entry_date,
                  @in_doctor_advice, @in_review_patient)
           AS X(mrn_no, entry_date, doctor_advice, review_patient)
     WHERE mrn_no = @in_mrn_no
       AND entry_date = @in_entry_date;
    END;

    The VALUES clause is called a row constructor and it builds a local virtual table that can go into a SELECT. But there is another problem; did you want an INSERT INTO or an UPDATE of an existing row?

    CREATE PROCEDURE Update_Prescription_Advice
    @in_mrn_nbr CHAR(10), @in_entry_date DATE,
    @in_doctor_advice_txt VARCHAR(500),
    @in_review_patient_txt VARCHAR(500))
    AS
    BEGIN
    SET NOCOUNT ON;
    UPDATE Prescriptions
       SET doctor_advice = @in_doctor_advice,
           review_patient = @in_review_patient
     WHERE mrn_no = @in_mrn_no
       AND entry_date = @in_entry_date;
    END;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    • Marked as answer by atik sarker Tuesday, July 05, 2011 3:59 AM
    Monday, July 04, 2011 8:29 PM
  • Try below query:

     

    ALTER PROCEDURE [dbo].[SP_PRESCRIPTED_ADVICE_AND_REVIEW_INSERT]
    @Mrn_No	varchar(50),
    @Entry_Date	datetime,
    @Doctor_Advice	varchar(50),
    @Review_Patient	varchar(50)
    AS
    BEGIN
    SET NOCOUNT ON
    BEGIN
    		INSERT INTO dbo.t_PRESCRIPTION
      (
     				Doctor_Advice,
    				Review_Patient,
                    Mrn_No,
                    Entry_Date
    
      )
      VALUES
      (
    
    				@Doctor_Advice,
    				@Review_Patient,
                    @Mrn_No,
                    @Entry_Date
      )
      
    END
     
    END
    
    


     


    Shailly - If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".
    • Edited by Shailly Monday, July 04, 2011 8:34 PM Formatting
    Monday, July 04, 2011 8:33 PM
  • Thank you CELKO for your helpful post.
    Regards atik sarker
    Tuesday, July 05, 2011 3:59 AM