Stairway to T-SQL DML Level 9: Adding Records to a table using INSERT Statement

  • Greg Larsen

    SSC-Insane

    Points: 20605

    Comments posted to this topic are about the item Stairway to T-SQL DML Level 9: Adding Records to a table using INSERT Statement

    Gregory A. Larsen, MVP

  • jfogel

    SSCarpal Tunnel

    Points: 4042

    I was unaware of the output option for this. I'm not sure I'll ever really need it but it is cool to know I have the option.

    Cheers

  • ACinAZ

    SSCommitted

    Points: 1849

    Another useful variation of the INSERT command is the "SIF" format to create a table with fields based on the source table:

    SELECT Id, Name, Quantity

    INTO SomeOtherTable -- Or even @SomeOtherTable

    FROM Fruit

    This will create a table ("SomeOtherTable") with the Id, Name, and Quantity columns from the Fruit table. You can specify a WHERE clause to limit the records you pull out (WHERE Quantity > 2). Useful for on-the-fly requirements to get a subset of records out of a larger table to manipulate or scan through.

  • ScottC91

    SSC Rookie

    Points: 43

    Why does the Id column in listing 9 start with 21 after the previous is dropped and the new one is made an identity?

    Sorry... newbie here.

  • Greg Larsen

    SSC-Insane

    Points: 20605

    Very good question. Looks like the article output is incorrect. The id values should be 19 and 20. I'll will get this update. Thank you for pointing this out.

    Greg

    Gregory A. Larsen, MVP

  • dhspiegel

    Valued Member

    Points: 58

    In listing 6 you use some logic for Id in the select part of the statement, but I can't figure out what that part is doing. Specifically the "7+(6-Id)", what is this doing?

  • dhspiegel

    Valued Member

    Points: 58

    Ok, I figured it out, but the syntax is not intuitive. I had to start at the end of the statement and work backwards.

  • anthony.rooney

    Old Hand

    Points: 382

    Hi

    I could not figure this out either, can you share your insight.

    I also struggled with - "SELECT b.Id + 9, a.Name + b.name"

    As the stairway is a learning tool for beginners it seems to have jumped ahead a bit as this was not covered off in the select section

  • eric.goldman 55906

    SSC Rookie

    Points: 42

    While the column list is not necessarily required by the INSERT statement, I have learned that it is at least a best practice to always include it.

    If the order of the columns within the table changes, you may start receiving errors or corrupt data (arguably worse) that are difficult to troubleshoot. By always including the column list, you avoid such issues because you are explicitly specifying which column to insert which value into.

    Always including the column list also keeps your insert statement from breaking when a new column is added to the table (assuming the column is nullable or has a default value constraint).

  • Jeff Moden

    SSC Guru

    Points: 994238

    @Greg Larsen,

    Your the one that got paid some decent money for posting this series. How about you answer some of the questions for a change!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply