Mention fields names in INSERT statement (SQL Server)

Share this topic:



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 having really 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 by 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 above 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: 112
Joined: 19 Feb 2013, 20:33

Return to Ocean of Databases

Who is online

Users browsing this forum: No registered users and 1 guest


Mention fields names in INSERT statement (SQL Server)

Share this topic:


If you think that this site is not too bad, please LIKE it in Facebook. Thanks!





cron
free counters

eXTReMe Tracker