Mention fields names in INSERT statement (SQL Server)


Link to this posting

Postby Ursego » 22 Feb 2013, 08:26

Write fields names in SELECT parts of INSERT statements.

In INSERT statements, write the field name near each inserted value ("field_name = inserted_value"):

*** BAD code: ***

Code: Select all
    INSERT #recordset (
           company_number
          ,entered_date
          ,description
          ,accounting_flag
          ,interface_flag
          ,subcontractor_contact_ba_id)
    SELECT @company_number
          ,received_date
          ,full_description
          ,'Y'
          ,'N'
          ,NULL /* will be populated later */
      FROM v_change_order_header
     WHERE ...

*** GOOD code: ***

Code: Select all
    INSERT #recordset (
           company_number
          ,entered_date
          ,description
          ,accounting_flag
          ,interface_flag
          ,subcontractor_contact_ba_id)
    SELECT company_number = @company_number
          ,entered_date = received_date
          ,description = full_description
          ,accounting_flag = 'Y'
          ,interface_flag = 'N'
          ,subcontractor_contact_ba_id = NULL /* will be populated later */
      FROM v_change_order_header
     WHERE ...

It will allow you see exactly which value goes into which field - very useful when you add/remove fields to/from an INSERT statement with a lot of fields. Don't forget that the field name, written with "=" before the inserted value, acts only as a comment - the insert anyway occurs according to the position of the field in the INSERT part (so, if the field "last_name" is listed third in the INSERT part and you will write "first_name = @first_name" in the third line of the SELECT part, you will populate the field "last_name" with a value, stored in @first_name var!). Unfortunately, it is impossible in Oracle, but you can use comments to have the same effect ("inserted_value /* field_name */" instead of "field_name = inserted_value").

By the way, talking about the INSERT statement... If you don't insert any value into a field, you can simply omit that field in the statement. But it's more beautifully to explicitly insert NULL (as into subcontractor_contact_ba_id field in the example). Why? Firstly, developers will see that exist columns which are not populated by the INSERT statement (otherwise, the developers can think the table is smaller than it really is). Secondly, it acts as a comment saying the developer hasn't forgotten to populate the field - he has left it empty intentionally. It's especially good when you do the initial INSERT into a temporary table in your stored procedure leaving some fields empty (these fields will be populated later in the procedure using UPDATE of the temporary table (I even add a comment in this situation: "/* will be populated later */".
User avatar
Ursego
Site Admin
 
Posts: 143
Joined: 19 Feb 2013, 20:33



Ketones are a more high-octane fuel for your brain than glucose. Become a biohacker and upgrade yourself to version 2.0!



cron
Traffic Counter

eXTReMe Tracker