INSERT

  • Nice question thanks. I've learnt something new, that SELECT INTO does not transfer the properties of a computed column to the target table.

    Dave Morris :alien:

    "Measure twice, saw once"

  • Earlier I'm aware of Index and constraints will not be transferred to destination table..but about Computed columns i came to know only from this QotD. Thanks kapil.

    --
    Dineshbabu
    Desire to learn new things..

  • Hugo Kornelis (5/18/2013)


    sea4stars (5/18/2013)


    Interesting: After I decided on my answer of the first insert running ok, and the second insert failing, I fired up my trusty copy of SQL Server Express 2012 and ran your question in my test area to verify it. This resulted in BOTH inserts failing. So, trusting software over wetware, I selected the answer of having both fail. Oh well!

    You probably executed thee two statements as a single batch. Since the error you get is raised at compile time and SQL Server always compiles the entire batch before starting execution, the ffect would be that none of the statements run. However, you get only a single error message, refering to a single statement. So the answer option "both statements will throw an error" is definitely not correct.

    When I answered this question (yesterday), I knew that neither statement would execute when sent as a single batch, but since there was no answer option that described this behaviour, I knew the author intended the statements to be executed independently. (If the answer option had been phrased as "neither statement will execute successfully", I would indeed have been confused, and forced to make a 50/50 guess as to the author's intentions).

    Hi Hugo,

    I'm little bit confused with your explaination,

    I knew that neither statement would execute when sent as a single batch.

    I didn't understand this part. can you give some more explaination if possible with some sample code.

    --
    Dineshbabu
    Desire to learn new things..

  • I'm little bit confused with your explaination,

    I knew that neither statement would execute when sent as a single batch.

    I didn't understand this part. can you give some more explaination if possible with some sample code.

    [/quote]

    If you submit a single batch with the two statements, the batch fails at compile time; you get an error message and no results.

    If you select the two statements as two batches, then the first batch (with the first statement) executes successfully, and the second batch (with the second statement) fails at compile time.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo,

    In this scenario, eventhough if we run both insert statements as single batch, first statement will be executed successfully and we will get error only on second one rite? Correct me if i'm wrong or understanding in wrong direction.

    --
    Dineshbabu
    Desire to learn new things..

  • Dineshbabu (5/23/2013)


    Hugo,

    In this scenario, eventhough if we run both insert statements as single batch, first statement will be executed successfully and we will get error only on second one rite? Correct me if i'm wrong or understanding in wrong direction.

    Did you try it?

    As I already said in my previous reply, if you execute both in a single batch, the first statement will NOT execute. You'll get an error on the second, but because that is generated when parsing and compiling the batch, the first statement is not executed.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (5/23/2013)


    Dineshbabu (5/23/2013)


    Hugo,

    In this scenario, eventhough if we run both insert statements as single batch, first statement will be executed successfully and we will get error only on second one rite? Correct me if i'm wrong or understanding in wrong direction.

    Did you try it?

    As I already said in my previous reply, if you execute both in a single batch, the first statement will NOT execute. You'll get an error on the second, but because that is generated when parsing and compiling the batch, the first statement is not executed.

    I'm really very sorry.. Still i'm not clear.. can you give me the sample code ,one as single batch and another separate batch.

    Then as per BOL

    http://msdn.microsoft.com/en-IN/library/ms175502(v=sql.105).aspx

    CREATE TABLE dbo.t3(a int) ;

    INSERT INTO dbo.t3 VALUES (1) ;

    INSERT INTO dbo.t3 VALUES (1,1) ;

    INSERT INTO dbo.t3 VALUES (3) ;

    GO

    SELECT * FROM dbo.t3 ;

    First, the batch is compiled. The CREATE TABLE statement is compiled, but because the table dbo.t3 does not yet exist, the INSERT statements are not compiled.

    Second, the batch starts to execute. The table is created. The first INSERT is compiled and then immediately executed. The table now has one row. Then, the second INSERT statement is compiled. The compilation fails, and the batch is terminated. The SELECT statement returns one row.

    In SQL Server 2000, the batch starts to execute and the table is created. The three INSERT statements are compiled one by one but are not executed. Because the second INSERT causes a compilation error, the whole batch is terminated. The SELECT statement returns no rows.

    This says like first statement will be executed immediately after compilation.

    --
    Dineshbabu
    Desire to learn new things..

  • Dineshbabu (5/23/2013)


    I'm really very sorry.. Still i'm not clear.. can you give me the sample code ,one as single batch and another separate batch.

    Sure. It's here: http://www.sqlservercentral.com/questions/T-SQL/98942/[/url].

    First run the part where the two tables are created (the CREATE TABLE and the SELECT INTO). You may have to change upper- and lower-case, depending on case sensitivity of your instance.

    Then, run the two INSERT statements as a single batch. You'll get an error. Use SELECT against both tables to verify that no row was inserted in either one.

    Then, run the two INSERT statements individually (either by selecting and execution one at a time, or by typing "go" in between them). You'll, again, get an error. Use SELECT against both tables to verify that this time, a row was inserted in the first table, but in the second.

    And for bonus points - drop both tables, and now execute the entire script (table creation + insert statements) at once. Now, the first insert IS executed. To explain this, google for "deferred name resolution".


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (5/23/2013)


    Sure. It's here: http://www.sqlservercentral.com/questions/T-SQL/98942/[/url].

    First run the part where the two tables are created (the CREATE TABLE and the SELECT INTO). You may have to change upper- and lower-case, depending on case sensitivity of your instance.

    Then, run the two INSERT statements as a single batch. You'll get an error. Use SELECT against both tables to verify that no row was inserted in either one.

    Then, run the two INSERT statements individually (either by selecting and execution one at a time, or by typing "go" in between them). You'll, again, get an error. Use SELECT against both tables to verify that this time, a row was inserted in the first table, but in the second.

    And for bonus points - drop both tables, and now execute the entire script (table creation + insert statements) at once. Now, the first insert IS executed. To explain this, google for "deferred name resolution".

    Now I understood the entire story.

    Still now I was trying as you mentioned in the bonus point (table creation + insert statements) at once. So while parsing time both tables was not exist but all the statements are syntax wise correct and parsing is finished sucessfully. so while execution, first statement will be executed successfully but second insert will fail at compilation.

    If I run table creation as one batch and two insert statements as one batch, then while compiling the second (Insert) batch, error will be raised by second insert statement so entire batch will be failed at compilation time itself...

    I hope now i'm correct track...

    --
    Dineshbabu
    Desire to learn new things..

  • Dineshbabu (5/23/2013)


    Still now I was trying as you mentioned in the bonus point (table creation + insert statements) at once. So while parsing time both tables was not exist but all the statements are syntax wise correct and parsing is finished sucessfully. so while execution, first statement will be executed successfully but second insert will fail at compilation.

    If I run table creation as one batch and two insert statements as one batch, then while compiling the second (Insert) batch, error will be raised by second insert statement so entire batch will be failed at compilation time itself...

    I hope now i'm correct track...

    Yes, you are on the right track!

    When compiling a batch, SQL Server will check for existence of tables and columns. If a table exists but a column doesn't, you'll get an error. But if the table itself does not exist, "deferred name resolution" kicks in - SQL Server will assume (hope) that the table will be created in time and continue optimizing. Then, when that specific statement is executed, a new attempt is made to bind the table and column names, and now it must succeed.

    Deferred name resolution for tables has advantages (like being able to create a stored procedure that uses a table that will be created later) and disadvantages (like limited column name checks, and no error message until run-time if you mistype a table name in a stored proc). There could also be situations where deferred name resolution for columns could be nice (e.g. a batch or stored proc that adds a column to a table and then uses it), as well as situations where it would cause problems. But that is a moot point, for the SQL Server development team decided, for whatever reason, to implement deferred name resolution for tables (and views), but not for columns.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Thank you very much Hugo for spending your valuable time to make me understand.

    --
    Dineshbabu
    Desire to learn new things..

  • EZ PZ

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Nice question. 🙂

  • Hugo Kornelis (5/23/2013)


    I'm little bit confused with your explaination,

    I knew that neither statement would execute when sent as a single batch.

    I didn't understand this part. can you give some more explaination if possible with some sample code.

    If you submit a single batch with the two statements, the batch fails at compile time; you get an error message and no results.

    If you select the two statements as two batches, then the first batch (with the first statement) executes successfully, and the second batch (with the second statement) fails at compile time.[/quote]

    how it is possible insert into second table even without # or @ (table variable or temp table) Symbol? what type of table is this?

    Manik
    You cannot get to the top by sitting on your bottom.

  • manik123 (5/27/2013)


    how it is possible insert into second table even without # or @ (table variable or temp table) Symbol? what type of table is this?

    A normal (permanent) table. It has been created by the SELECT ... INTO statement.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 16 through 30 (of 30 total)

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