T-Sql Algorithm Question

Question

I have a T-Sql Statement as follows;

Insert into Table1
Select * From Table2

I want to know the running sequence. Does the insert waits select statement to finish before starting or it starts asap select statement starts returning values and expects new records from the select statement to continue.

This is a plain stored procedure and no transactions used.

+1 this is the first time I saw someone inserting its own values to its own table... I absolutely had no clue if it was possible... but it is... — dance2die
– dance2die, Commented Oct 7, 2010 at 11:19
@Sung: I'm not even sure it would have occurred to me to try this, but I'm definitely filing it away for future use; it's great way to generate one-off test data. — Dave
– Dave, Commented Oct 7, 2010 at 13:36
@Sung Meister actually I wrote wrong. Edited, insert into table1 not table2 — Serkan Hekimoglu
– Serkan Hekimoglu, Commented Oct 7, 2010 at 13:45

onedaywhen · Accepted Answer · 2010-10-07 10:36:53Z

3

What you have there is effectively a single statement. It will only insert into Table2 the records that were present in Table2 when you begin the insert. Otherwise the properties of ACID would not apply and you'd have issues with isolation (what if the sp is run twice concurrently) and durability. SQL Server will enforce this via locking.

edited Oct 7, 2010 at 10:36

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

answered Oct 7, 2010 at 10:21

MartW

12.6k3 gold badges46 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dance2die Over a year ago

What I don't get is, suppose that you have 10 records in table2. then inserting with "insert table2 select * from table2 (nolock)" & "insert table2 select * from table(tablock)" seem to both insert 10 records. Shouldn't "nolock" return whether a record is committed or not? I was thinking the former would generate an infinite loop

AakashM · Accepted Answer · 2010-10-07 13:11:59Z

To echo @CodeByMoonlight's answer, and to address your comment there: the physical considerations (including the specifics of locking) are always subordinate to the logical instructions specified by the query.

In the processing of an INSERT ... SELECT statement, logically speaking the SELECT is carried out to produce a resultset, and then the rows of this resultset are INSERTed. The fact that in this case the source table and the target table are the same table is irrelevant. I'm fairly sure that specifying NOLOCK or TABLOCK would in any case apply only to the SELECT, if that's where you position them.

Consider as another example this statement, which makes no sense if you read it in an 'imperative' way:

UPDATE SomeTable
SET Column1 = Column2, Column2 = Column1

With an imperative, rather than set-based, understanding, this statement might look as if it will result in Column1 and Column2 having the same value for all rows. But it doesn't - it in fact swaps the values in Column1 and Column2. Only by understanding that the logical instructions of the query dictate what actually happens can this be seen.

Collectives™ on Stack Overflow

T-Sql Algorithm Question

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related