0

I Have a store procedure which could be simplified as:

INSERT into TABLE1 (column0, column1...) 
  SELECT field0, field1... from TABLE2

And I noticed sometime randomly the TABLE1 will miss SOME data from the insertion, what's the possibility to cause this problem? Isn't it automatically wrapped as 'Implicitly TRANSACTION'?

NOTE, that SP was called in a .NET C# code, and I didn't catch any exception during the execution when this weird issue happened.

[Edit0]: This is original sql statement in my production code:

CREATE PROCEDURE [dbo].[SaveSomething]
    --The only parameter passed from C# code which is a XML format string.
    @Source nvarchar(max)
AS
BEGIN
--Open that XML with a handle attached
EXEC sp_xml_preparedocument @docHandle OUTPUT, @Source

--turn that XML into a temp table
SELECT
     [PeriodID]
    ,[DESCRIPTION]
    ...
INTO #tmpPeriods
FROM OPENXML(@docHandle, N'/FirstLevelRoot/SecondLevelRoot/LeafNode') 
WITH (PeriodID nvarchar(10) '../@PromotionId',
    [DESCRIPTION] nvarchar(60) '@Reason', 
    ... )

--insert that temp table data to the real useful table. And this is the place we found randomly 
--some condition satisfied rows in tmpPeriods didn't get inserted to TargetTable.
INSERT INTO [dbo].[TargetTable](
     [ID]
    ,[DESCRIPTION]
    ...)
 SELECT
     SourceTable.[PeriodID]
    ,SourceTable.[DESCRIPTION]
    ...
FROM #tmpPeriods as SourceTable
LEFT JOIN [TargetTable]
ON SourceTable.[PeriodID] = TargetTable.ID
WHERE TargetTable.ID IS NULL

As my partner tested several times with the same input XML string, and have all tables emptied, but sometimes the TargetTable miss some expected rows.

The isolation level is the default of 'Read Committed'.

Shawn
  • 702
  • 1
  • 9
  • 36
  • I think we'll need to see the non simplified version and calling code for this – Tanner Jul 16 '14 at 08:50
  • Possible duplicate to [LINK](http://stackoverflow.com/questions/1071286/does-sql-server-wrap-select-insert-queries-into-an-implicit-transaction) – schlonzo Jul 16 '14 at 08:51
  • 1
    this probably depends a *lot* on the isolation level... so... what *is* the isolation level? it will also depend a lot on what makes you think the data is "missing". If you're in a race with another spid, all sorts of fun but valid scenarios are possible. – Marc Gravell Jul 16 '14 at 08:54

1 Answers1

0

Check all triggers on your table. It seem that you have instead of insert trigger on the Table1. if you don't observe trigger format, may be insert some data from insertion.

mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128