Prev: Creating Views with Indexes for entire Database Excluding UniqueIdentifier field
Next: Please help with a simple Max Select query
From: KH on 26 Apr 2010 14:23
Is the entire statement below a transaction, or is the select statement like
a derived table which is "materialized" before a transaction for the INSERT
begins?

--
INSERT INTO foo (x)
SELECT y FROM bar AS b
WHERE NOT EXISTS(SELECT * FROM foo WHERE x = b.y);
GO

-- Thanks in advance

From: Tom Moreau on 26 Apr 2010 15:23
It's considered a transaction.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"KH" <KH(a)discussions.microsoft.com> wrote in message
news:0F8D46A1-3120-44A9-A390-E3A1272A217A(a)microsoft.com...
Is the entire statement below a transaction, or is the select statement like
a derived table which is "materialized" before a transaction for the INSERT
begins?

--
INSERT INTO foo (x)
SELECT y FROM bar AS b
WHERE NOT EXISTS(SELECT * FROM foo WHERE x = b.y);
GO

-- Thanks in advance

From: Rich Dillon on 26 Apr 2010 19:57
It is a single transaction. But if you're concerned about the possibility
of a new row being added to "foo" by another process after the WHERE clause
has been evaluated but before the INSERT, that can still happen depending
upon the transaction isolation level you use.

For example:

CREATE TABLE foo (x INT NOT NULL PRIMARY KEY);
CREATE TABLE bar (y INT NOT NULL PRIMARY KEY);
INSERT INTO bar (y) VALUES (1);

Start this in one query window:

SET NOCOUNT ON;
WHILE 1 = 1
BEGIN
BEGIN TRY
INSERT INTO foo (x) VALUES (1);
END TRY
BEGIN CATCH
--
END CATCH;
DELETE FROM foo;
END;

And then start this in another window:

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
WHILE 1 = 1
BEGIN
BEGIN TRY
INSERT INTO foo (x)
SELECT y FROM bar AS b
WHERE NOT EXISTS (SELECT * FROM foo WHERE x = b.y);
END TRY
BEGIN CATCH
PRINT 'FAILED'
BREAK;
END CATCH;
END;


Hope that helps,
Rich


"KH" <KH(a)discussions.microsoft.com> wrote in message
news:0F8D46A1-3120-44A9-A390-E3A1272A217A(a)microsoft.com...
> Is the entire statement below a transaction, or is the select statement
> like
> a derived table which is "materialized" before a transaction for the
> INSERT
> begins?
>
> --
> INSERT INTO foo (x)
> SELECT y FROM bar AS b
> WHERE NOT EXISTS(SELECT * FROM foo WHERE x = b.y);
> GO
>
> -- Thanks in advance
>
 | 
Pages: 1
Prev: Creating Views with Indexes for entire Database Excluding UniqueIdentifier field
Next: Please help with a simple Max Select query