May 27, 2009 at 5:46 pm
Hi,
I have a table with 7 rows names PacomTransactionHistory and i insert this into a table named StagePacomTransactionHistory where IsImported = 0 - this works fine
INSERT INTO StagePacomSTAXTransactionHistory
(TransactionType, TerminalID, InductionNo, CardSerialNumber, TransactionDate, DataString, DepartmentID, BreakTaken, ErrorID, Online, isImported)
SELECT TransactionType, TerminalID, InductionNo, CardSerialNumber, TransactionDate, PacomTransactionType, NULL, NULL, 20000, 0, ISImported
FROM PacomTransactionHistory
WHERE (ISImported = 0)
I then update PacomTransactionHistory to IsImported = 1
However when i use the following query to insert from StagePacomTransactionHistory to terminalTransactionHistory - i get duplicate rows - 14 rows are inserted
INSERT
INTO TerminalTransactionHistory
SELECT
TransactionType, TerminalID, InductionNo, CardSerialNumber,
TransactionDate, DataString, DepartmentID, BreakTaken, ErrorID,
Online
FROM StagePacomSTAXTransactionHistory
where ISImported = 0
any help would be appreciated
May 27, 2009 at 6:07 pm
Daryl, if you post some table structure and sample data, someone can probably help you figure it out.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 27, 2009 at 6:46 pm
Table Data
CREATE TABLE [dbo].[PacomTransactionHistory] (
[TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
[TransactionType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TerminalID] [int] NOT NULL ,
[InductionNo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CardSerialNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SurName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransactionDate] [datetime] NOT NULL ,
[BreakTaken] [bit] NULL ,
[ErrorID] [int] NULL ,
[PacomReaderName] [varchar] (33) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PacomReaderNumber] [tinyint] NULL ,
[PacomSiteRTUNumber] [smallint] NULL ,
[PacomAreaNumber] [tinyint] NULL ,
[PacomTransactionType] [varchar] (81) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CARD_AUDIT_TRAIL_ID] [uniqueidentifier] NOT NULL ,
[ISImported] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[StagePacomSTAXTransactionHistory] (
[TransactionID] [uniqueidentifier] NULL ,
[TransactionType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TerminalID] [int] NOT NULL ,
[InductionNo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CardSerialNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransactionDate] [datetime] NOT NULL ,
[DataString] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DepartmentID] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BreakTaken] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PacomTransactionType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Online] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[isImported] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TerminalTransactionHistory] (
[TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
[TransactionType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TerminalID] [int] NOT NULL ,
[InductionNo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CardSerialNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransactionDate] [datetime] NOT NULL ,
[DataString] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DepartmentID] [int] NULL ,
[BreakTaken] [bit] NULL ,
[ErrorID] [int] NULL ,
[Online] [bit] NULL
) ON [PRIMARY]
GO
Sample Data
499,LOGOUT,197,MW8907862,EEF70D00FCFF12E0,G Black,20/05/2009 12:58,,0,Entry Gate,1,1,1,Card download status update,{E4C1333E-F059-4594-8194-86F33314AEAB},1
500,LOGIN,197,MK11005894,EEF70D00FCFF12E0,G Black,20/05/2009 12:59,,0,Exit Gate,2,1,1,Valid card,{7D44AD5D-F496-4A5B-A9F3-03307A7D2929},1
501,LOGOUT,197,MK11005894,EEF70D00FCFF12E0,G Black,20/05/2009 12:59,,0,Entry Gate,1,1,1,Valid card,{08C0997F-FE67-47D5-814E-A01AF282F7D6},1
Problem
The 3 rows above are in the PacomTransactionHistory table, they will be inserted as 3 into StagePacomSTAXTransactionHistory however they will be duplicated into 6 in the TerminalTransactionHistory table
Thanks
May 28, 2009 at 8:04 am
Daryl
I'm not getting the same results as you are getting. Check out my code below, just to make sure I have captured your process correctly. Please note how I formated the sample data for easy insert. Also, PacomTransactionHistory has fields for first name and last name, so I separated 'G Black' into 'G','Black'. And I changed the dates from 20/5/2009 to 5/20/2009, as they would not insert on my machine the way you have it. My thoughts are that there is a timing lapse in updating IsImported to 1, or there is a trigger on PacomTransactionHistory causing the records to be inserted behind the scenes. All I can tell you is that when I run it, I get what I believe to be the correct results. Is this a procedure or a batch routine? If so, please post the entire code, so we can have a closer look. If you are working in a test environment, I would reccomend deleting TerminalTransactionHistory, then check it after every step of your process to determine precisely when the duplicate records are being inserted. Is it possible they are there before you inserted them, perhaps by another procedure?
IF OBJECT_ID('PacomTransactionHistory','u') IS NOT NULL
DROP TABLE PacomTransactionHistory
CREATE TABLE [dbo].[PacomTransactionHistory] (
[TransactionID] [int],-- IDENTITY (1, 1) NOT NULL ,
[TransactionType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TerminalID] [int] NOT NULL ,
[InductionNo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CardSerialNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SurName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransactionDate] [datetime] NOT NULL ,
[BreakTaken] [bit] NULL ,
[ErrorID] [int] NULL ,
[PacomReaderName] [varchar] (33) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PacomReaderNumber] [tinyint] NULL ,
[PacomSiteRTUNumber] [smallint] NULL ,
[PacomAreaNumber] [tinyint] NULL ,
[PacomTransactionType] [varchar] (81) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CARD_AUDIT_TRAIL_ID] [uniqueidentifier] NOT NULL ,
[ISImported] [bit] NOT NULL
) ON [PRIMARY]
IF OBJECT_ID('StagePacomSTAXTransactionHistory','u') IS NOT NULL
DROP TABLE StagePacomSTAXTransactionHistory
CREATE TABLE [dbo].[StagePacomSTAXTransactionHistory] (
[TransactionID] [uniqueidentifier] NULL ,
[TransactionType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TerminalID] [int] NOT NULL ,
[InductionNo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CardSerialNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransactionDate] [datetime] NOT NULL ,
[DataString] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DepartmentID] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BreakTaken] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PacomTransactionType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Online] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[isImported] [bit] NULL
) ON [PRIMARY]
IF OBJECT_ID('TerminalTransactionHistory','u') IS NOT NULL
DROP TABLE TerminalTransactionHistory
CREATE TABLE [dbo].[TerminalTransactionHistory] (
[TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
[TransactionType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TerminalID] [int] NOT NULL ,
[InductionNo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CardSerialNumber] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransactionDate] [datetime] NOT NULL ,
[DataString] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DepartmentID] [int] NULL ,
[BreakTaken] [bit] NULL ,
[ErrorID] [int] NULL ,
[Online] [bit] NULL
) ON [PRIMARY]
GO
INSERT INTO PacomTransactionHistory
SELECT '499','LOGOUT','197','MW8907862','EEF70D00FCFF12E0','G', 'Black','5/20/2009 12:58',NULL,'0','Entry Gate','1','1','1','Card download status update','{E4C1333E-F059-4594-8194-86F33314AEAB}','1' UNION ALL
SELECT '500','LOGIN','197','MK11005894','EEF70D00FCFF12E0','G','Black','5/20/2009 12:59',NULL,'0','Exit Gate','2','1','1','Valid card','{7D44AD5D-F496-4A5B-A9F3-03307A7D2929}','1' UNION ALL
SELECT '501','LOGOUT','197','MK11005894','EEF70D00FCFF12E0','G','Black','5/20/2009 12:59',NULL,'0','Entry Gate','1','1','1','Valid card','{08C0997F-FE67-47D5-814E-A01AF282F7D6}','1'
GO
--I am assuming the initial value of IsImported should be 0 right?
UPDATE PacomTransactionHistory
SET IsImported = 0
GO
INSERT INTO StagePacomSTAXTransactionHistory
(TransactionType, TerminalID, InductionNo, CardSerialNumber, TransactionDate, DataString, DepartmentID, BreakTaken, ErrorID, Online, isImported)
SELECT TransactionType, TerminalID, InductionNo, CardSerialNumber, TransactionDate, PacomTransactionType, NULL, NULL, 20000, 0, ISImported
FROM PacomTransactionHistory
WHERE (ISImported = 0)
-- 3 rows affected
GO
--Set IsImported = 1 so the records will not be inserted into
--StagePacomsTAXTransactionHistory again. How are the records inserted
--into PacomTransactionHistory in the first place?
--Does your procedure lock the table, so that no new records can be
--inserted between when you insert the records having IsImported = 0
--into StagePacomSTAXTransactionHistory and
--when you update IsImportted = 1? If not, what will happen if a new
--record is inserted into PacomTransactionHistory *after* you insert records
--having IsImported = 0 into StagePacomSTAXTransactionHistory,
--and *before* you set IsImported = 1?
UPDATE PacomTransactionHistory
SET IsImported = 1
-- 3 rows affected
GO
INSERT
INTO TerminalTransactionHistory
SELECT
TransactionType, TerminalID, InductionNo, CardSerialNumber,
TransactionDate, DataString, DepartmentID, BreakTaken, ErrorID,
Online
FROM StagePacomSTAXTransactionHistory
where ISImported = 0
-- 3 rows affected
SELECT
*
FROM PacomTransactionHistory
SELECT
*
FROM StagePacomSTAXTransactionHistory
SELECT
*
FROM TerminalTransactionHistory
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply