April 23, 2012 at 6:15 am
Hi Friends,
I am here for some general discussions regarding cursor...
I had a scenario that i wanted to check all the rows with certain conditions before inserting into another table...
So i used Cursor since we use While loop to fetch row by row process, but some of my colleagues told me that not to use Cursor since it is not good in performance(like they told it happened)... But i did not have any other idea on my mind to achieve this. so i have been using the cursor and according to my knowledge, cursor is the only thing that we can use for row by row process..Sorry if am wrong...
Friends, Please tell me about the pro's and Con's of using Cursor...
Below is my Cursor...Give me your valuable suggestions
Any suggestions would be really appreciated
DECLARE @NEWCASE nvarchar(25)
DECLARE @NEWDOCNO nvarchar(25)
DECLARE @NEWNAR nvarchar(max)
DECLARE @NEWDATE datetime
DECLARE @NEWDIDESC nvarchar(25)
DECLARE @NEWDIDCU nvarchar(255)
DECLARE @JurisID char(4)
DECLARE @OLDCASE nvarchar(25)
DECLARE @OLDDOCNO nvarchar(25)
DECLARE @OLDNAR nvarchar(max)
DECLARE @OLDDATE datetime
DECLARE @OLDDIDESC nvarchar(25)
DECLARE @OLDDIDCU nvarchar(255)
DECLARE @OldJurisID char(4)
DECLARE @SEQNO int
DECLARE @ADDDesc varchar(3)
DECLARE MyCursor CURSOR FAST_FORWARD for
SELECT
d.incidentidref
, DIDOC#
, Entdttm CreateDate
, DIDESC
, FDNARR + '</p>' FDNARR
, DIDCU
, d.jurisid
FROM PSDOCI d
JOIN PSFDOC
ON DIDOC# = FDDOC#
JOIN GloucMidPolice.dbo.SrcIncident src
ON d.incidentidref = src.IncidentIDRef and d.jurisid = src.jurisid
WHERE DIFILE = 'PLINCD' and (not DIDESC in ('cad system narrative', 'dispatch narrative')
and
not DIDESC like 'Converted Cad Comment%')
ORDER by d.jurisid, D.INCIDENTIDREF, DIDOC#, CAST(FDLINE AS Int)
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @NEWCASE, @NEWDOCNO, @NEWDATE, @NEWDIDESC , @NEWNAR, @NEWDIDCU, @JurisID
SET @OLDCASE = @NEWCASE
SET @OldJurisID = @JurisID
SET @OLDDOCNO = @NEWDOCNO
SET @OLDNAR = @NEWNAR
SET @OLDDATE = @NEWDATE
SET @OLDDIDESC = @NEWDIDESC
SET @OLDDIDCU = @NEWDIDCU
SET @SEQNO = 0
SET @ADDDesc = 'Yes'
WHILE @@fetch_Status = 0 AND @OLDCASE = @NEWCASE and @OldJurisID = @JurisID
BEGIN
WHILE (@OLDDOCNO = @NEWDOCNO) AND @@FETCH_STATUS = 0
BEGIN
IF @ADDDesc = 'Yes' and @SEQNO = 0
BEGIN
SET @OLDNAR = '** ' + @OLDDIDESC + ' **' + '</p>' + @OLDNAR
SET @OLDCASE = @NEWCASE
SET @OldJurisID = @JurisID
SET @OLDDOCNO = @NEWDOCNO
SET @OLDDATE = @NEWDATE
SET @OLDDIDESC = @NEWDIDESC
SET @OLDDIDCU = @NEWDIDCU
SET @ADDDesc = 'No'
FETCH NEXT FROM MyCursor INTO @NEWCASE, @NEWDOCNO, @NEWDATE, @NEWDIDESC , @NEWNAR, @NEWDIDCU, @JurisID
END
ELSE
BEGIN
set @OLDNAR = @OLDNAR + ' ' + @NEWNAR
SET @OLDCASE = @NEWCASE
SET @OldJurisID = @JurisID
SET @OLDDOCNO = @NEWDOCNO
SET @OLDDATE = @NEWDATE
SET @OLDDIDESC = @NEWDIDESC
SET @OLDDIDCU = @NEWDIDCU
SET @ADDDesc = 'No'
FETCH NEXT FROM MyCursor INTO @NEWCASE, @NEWDOCNO, @NEWDATE, @NEWDIDESC , @NEWNAR, @NEWDIDCU, @jurisid
END
END
IF @OLDCASE = @NEWCASE
BEGIN
SET @OLDDOCNO = @NEWDOCNO
SET @OLDCASE = @NEWCASE
SET @OldJurisID = @JurisID
SET @OLDNAR = @OLDNAR + ' ** ' + @NEWDIDESC + ' ** ' + '</p>' + @NEWNAR
SET @OLDDATE = @NEWDATE
SET @OLDDIDESC = @NEWDIDESC
SET @OLDDIDCU = @NEWDIDCU
SET @ADDDesc = 'Yes'
SET @SEQNO = 1
END
ELSE
BEGIN
Insert GloucMidPolice.dbo.SrcComment (JurisID, ModCode, ModIDRef, RecType, SeqNo, CreatedPFIDRef, CreatedDttm, Comment)
Values (@OldJurisID, 'INC', @OLDCASE, 'C', @SEQNO, @OLDDIDCU, @OLDDATE, @OLDNAR)
SET @OLDDOCNO = @NEWDOCNO
SET @OLDCASE = @NEWCASE
SET @OldJurisID = @JurisID
SET @OLDNAR = @NEWNAR
SET @OLDDATE = @NEWDATE
SET @OLDDIDESC = @NEWDIDESC
SET @OLDDIDCU = @NEWDIDCU
SET @ADDDesc = 'Yes'
SET @SEQNO = 0
END
END
--
close MyCursor
deallocate MyCursor
GO
Thanks,
Charmer
April 23, 2012 at 6:19 am
Cursors are not Really Good or Really Bad!
They designed and should be used for relevant tasks.
However your example is not one of them...
April 23, 2012 at 6:30 am
Eugene Elutin (4/23/2012)
Cursors are not Really Good or Really Bad!They designed and should be used for relevant tasks.
However your example is not one of them...
My cursor is taking 1 hour and 20 min for inserting around 1 million records...
I feel not o.k with time consumption....and some times it slows down the SQL server by filling up temp table...
Eugene, Do you have any better idea to accomplish the above scenario?
Thanks,
Charmer
April 23, 2012 at 6:41 am
Charmer (4/23/2012)
Eugene Elutin (4/23/2012)
Cursors are not Really Good or Really Bad!They designed and should be used for relevant tasks.
However your example is not one of them...
My cursor is taking 1 hour and 20 min for inserting around 1 million records...
I feel not o.k with time consumption....and some times it slows down the SQL server by filling up temp table...
Eugene, Do you have any better idea to accomplish the above scenario?
Yes, I have. You should write set-based queries to do what you are doing in a cursor.
April 23, 2012 at 7:14 am
If you:
A) Describe what the cursor is doing
B) Provide the table definitions (Create Table scripts)
C) Provide sample data (a dozen or so Insert statements)
then we can probably (almost certainly) help you rewrite the cursor into something much more efficient.
Without those three things, the only advice we can give is going to be very general. With those three, we can help you actually build better code.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2012 at 7:57 am
GSquared (4/23/2012)
If you:A) Describe what the cursor is doing
B) Provide the table definitions (Create Table scripts)
C) Provide sample data (a dozen or so Insert statements)
then we can probably (almost certainly) help you rewrite the cursor into something much more efficient.
Without those three things, the only advice we can give is going to be very general. With those three, we can help you actually build better code.
Ok, Gimme some time, Let me get DDL and DML to help you guys to help me....
Thanks,
Charmer
April 23, 2012 at 9:56 am
GSquared (4/23/2012)
If you:A) Describe what the cursor is doing
B) Provide the table definitions (Create Table scripts)
C) Provide sample data (a dozen or so Insert statements)
then we can probably (almost certainly) help you rewrite the cursor into something much more efficient.
Without those three things, the only advice we can give is going to be very general. With those three, we can help you actually build better code.
Here is my DDL and DML...
CREATE TABLE [dbo].[SrcComment]
(
[JurisID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ModCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ModIDRef] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RecType] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SeqNo] [smallint] NOT NULL,
[Subject] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ModIDRefSeqNo] [int] NULL,
[Comment] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreatedPFIDRef] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedDttm] [datetime] NULL,
[ModifiedPFIDRef] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ModifiedDttm] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SrcComment] ADD CONSTRAINT [SrcComment_PK] PRIMARY KEY CLUSTERED ([JurisID], [ModCode], [ModIDRef], [RecType], [SeqNo]) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SrcIncident]
(
[JurisID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IncidentIDRef] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CADNo] [bigint] NULL,
[CADNoRef] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CaseNo] [bigint] NULL,
[CaseNoRef] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReceiveSource] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalCFS] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OriginalCFSDesc] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CFSCode] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CFSCodeDesc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CFSClass] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UCRCode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MgmtCFSCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MgmtCFSDesc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MgmtCFSClass] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RptDttm] [datetime] NULL,
[StkDttm] [datetime] NOT NULL,
[DpDttm] [datetime] NULL,
[ArDttm] [datetime] NULL,
[FinDttm] [datetime] NOT NULL,
[CtPFIDRef] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CtStationIDRef] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DpPFIDRef] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DpStationIDRef] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ArPFIDRef] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ArStationIDRef] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinPFIDRef] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinStationIDRef] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrimeUnitIDRef] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReportCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DPPri] [smallint] NULL,
[CADDisposition] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CADDispositionDesc] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CaseDisposition] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CaseDispositionDesc] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PremiseType] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsTraffic] [smallint] NULL,
[SolvFactor] [smallint] NULL,
[CallCount] [smallint] NULL,
[IsDomesticVio] [smallint] NULL,
[IsAbandonStr] [smallint] NULL,
[IsSafeEntered] [smallint] NULL,
[Instrument] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AlarmSys] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AlarmSys2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Larceny] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Scene] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DamagedValue] [decimal] (10, 2) NULL,
[OffKilled] [smallint] NULL,
[OffAssaulted] [smallint] NULL,
[VictimCount] [smallint] NULL,
[IsArson] [smallint] NULL,
[IsGamble] [smallint] NULL,
[ForceUsed] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CrimeAgainst] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsAbandoned] [smallint] NULL,
[IsCompleted] [smallint] NULL,
[LocationType] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OccFromDttm] [datetime] NULL,
[OccToDttm] [datetime] NULL,
[AtDttm] [datetime] NULL,
[UcrDisposition] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UcrClearDttm] [datetime] NULL,
[NibrsDttm] [datetime] NULL,
[NibrsStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedPFIDRef] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedDttm] [datetime] NULL,
[ModifiedPFIDRef] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ModifiedDttm] [datetime] NULL,
[Custom1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Custom2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsImport] [smallint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SrcIncident] ADD CONSTRAINT [SrcIncident_PK] PRIMARY KEY CLUSTERED ([JurisID], [IncidentIDRef]) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PSDOCI]
(
[DITYPE] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DIFILE] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DISECD] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DIDESC] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DIFKEY] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DIDOC#] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DIOLST] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DIDCD] [numeric] (8, 0) NULL,
[DIDCU] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DILCD] [numeric] (8, 0) NULL,
[DILCU] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DIFLD1] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DIFLD2] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JurisID] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IncidentIDRef] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EntDttm] [datetime] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [PSDOCI_PnxX1] ON [dbo].[PSDOCI] ([IncidentIDRef], [JurisID], [EntDttm], [DIDOC#], [DIDESC], [DIFILE], [DIDCU]) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PSFDOC]
(
[FDDOC#] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FDLINE] [numeric] (5, 0) NULL,
[FDNARR] [nvarchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [PSFDOC_PnxX1] ON [dbo].[PSFDOC] ([FDDOC#], [FDNARR], [FDLINE]) ON [PRIMARY]
GO
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000001 ', 2000000001, N'200000000001 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE10133F5E4 AS DateTime), CAST(0x00008EE10133F5E4 AS DateTime), CAST(0x00008EE1013461B4 AS DateTime), CAST(0x00008EE101346E98 AS DateTime), CAST(0x00008EE101355ED4 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 1, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000002 ', 2000000002, N'200000000002 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE10149982C AS DateTime), CAST(0x00008EE10149982C AS DateTime), CAST(0x00008EE10149CA90 AS DateTime), CAST(0x00008EE101499958 AS DateTime), CAST(0x00008EE1014A1B6C AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 4, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000003 ', 2000000003, N'200000000003 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE1014E9200 AS DateTime), CAST(0x00008EE1014E9200 AS DateTime), CAST(0x00008EE1014ECEF0 AS DateTime), CAST(0x00008EE1014E9200 AS DateTime), CAST(0x00008EE1014F2F08 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 1, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000004 ', 2000000004, N'200000000004 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE101847B2C AS DateTime), CAST(0x00008EE101847B2C AS DateTime), CAST(0x00008EE1018AE750 AS DateTime), CAST(0x00008EE200038784 AS DateTime), CAST(0x00008EE2000B04B4 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 5, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000005 ', 2000000005, N'200000000005 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE2000145C8 AS DateTime), CAST(0x00008EE2000145C8 AS DateTime), CAST(0x00008EE200021C3C AS DateTime), CAST(0x00008EE20004CAF4 AS DateTime), CAST(0x00008EE200172E60 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 5, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000006 ', 2000000006, N'200000000006 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE20120DE00 AS DateTime), CAST(0x00008EE20120DE00 AS DateTime), CAST(0x00008EE2012A4F94 AS DateTime), CAST(0x00008EE20144E82C AS DateTime), CAST(0x00008EE20156F738 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 5, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000007 ', 2000000007, N'200000000007 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE2015996B4 AS DateTime), CAST(0x00008EE2015996B4 AS DateTime), CAST(0x00008EE20159E538 AS DateTime), CAST(0x00008EE2015B2074 AS DateTime), CAST(0x00008EE2015FBEE0 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 1, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000008 ', 2000000008, N'200000000008 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE20176A36C AS DateTime), CAST(0x00008EE20176A36C AS DateTime), CAST(0x00008EE201772580 AS DateTime), CAST(0x00008EE2017F5F5C AS DateTime), CAST(0x00008EE201818F84 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 4, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000009 ', 2000000009, N'200000000009 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE2017D6648 AS DateTime), CAST(0x00008EE2017D6648 AS DateTime), CAST(0x00008EE2018633CC AS DateTime), NULL, CAST(0x00008EE201865244 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 4, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000010 ', 2000000010, N'200000000010 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE201822EE4 AS DateTime), CAST(0x00008EE201822EE4 AS DateTime), CAST(0x00008EE2018AD6E8 AS DateTime), CAST(0x00008EE2018AE4F8 AS DateTime), CAST(0x00008EE300093F30 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 4, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000011 ', 2000000011, N'200000000011 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE30004CC20 AS DateTime), CAST(0x00008EE30004CC20 AS DateTime), CAST(0x00008EE3000A2E40 AS DateTime), CAST(0x00008EE3001D27AC AS DateTime), CAST(0x00008EE300365880 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 4, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000012 ', 2000000012, N'200000000012 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE3001E6D74 AS DateTime), CAST(0x00008EE3001E6D74 AS DateTime), CAST(0x00008EE30037CF80 AS DateTime), CAST(0x00008EE300389C94 AS DateTime), CAST(0x00008EE3003C4AC4 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 4, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000013 ', 2000000013, N'200000000013 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE3003BD918 AS DateTime), CAST(0x00008EE3003BD918 AS DateTime), CAST(0x00008EE3003C52F8 AS DateTime), CAST(0x00008EE3003C5FDC AS DateTime), CAST(0x00008EE300452784 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 4, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000014 ', 2000000014, N'200000000014 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE6012B15A0 AS DateTime), CAST(0x00008EE6012B15A0 AS DateTime), CAST(0x00008EE6013DA594 AS DateTime), CAST(0x00008EE6013DAC9C AS DateTime), CAST(0x00008EE6014AF8E8 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 5, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
INSERT [dbo].[SrcIncident] ([JurisID], [IncidentIDRef], [CADNo], [CADNoRef], [CaseNo], [CaseNoRef], [ReceiveSource], [OriginalCFS], [OriginalCFSDesc], [CFSCode], [CFSCodeDesc], [CFSClass], [UCRCode], [MgmtCFSCode], [MgmtCFSDesc], [MgmtCFSClass], [RptDttm], [StkDttm], [DpDttm], [ArDttm], [FinDttm], [CtPFIDRef], [CtStationIDRef], [DpPFIDRef], [DpStationIDRef], [ArPFIDRef], [ArStationIDRef], [FinPFIDRef], [FinStationIDRef], [PrimeUnitIDRef], [ReportCode], [DPPri], [CADDisposition], [CADDispositionDesc], [CaseDisposition], [CaseDispositionDesc], [PremiseType], [IsTraffic], [SolvFactor], [CallCount], [IsDomesticVio], [IsAbandonStr], [IsSafeEntered], [Instrument], [AlarmSys], [AlarmSys2], [Larceny], [Scene], [DamagedValue], [OffKilled], [OffAssaulted], [VictimCount], [IsArson], [IsGamble], [ForceUsed], [CrimeAgainst], [IsAbandoned], [IsCompleted], [LocationType], [OccFromDttm], [OccToDttm], [AtDttm], [UcrDisposition], [UcrClearDttm], [NibrsDttm], [NibrsStatus], [CreatedPFIDRef], [CreatedDttm], [ModifiedPFIDRef], [ModifiedDttm], [Custom1], [Custom2], [IsImport]) VALUES (N'1501', N'200000000015 ', 2000000015, N'200000000015 ', NULL, NULL, NULL, N'UNK ', N'Conversion ', N'UNK ', N'Conversion', NULL, NULL, NULL, NULL, NULL, CAST(0x00008EE60154F398 AS DateTime), CAST(0x00008EE60154F398 AS DateTime), CAST(0x00008EE601552E30 AS DateTime), CAST(0x00008EE60159FB7C AS DateTime), CAST(0x00008EE60161F868 AS DateTime), N' ', NULL, N' ', NULL, NULL, NULL, N' ', NULL, N'P141 ', NULL, 5, N'N ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'DZCASE ', CAST(0x0000919C00000000 AS DateTime), NULL, NULL, NULL)
/****** Object: Table [dbo].[PSFDOC] Script Date: 04/23/2012 21:24:41 ******/
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010522.AAA', CAST(2 AS Numeric(5, 0)), N'Additional Information:')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010522.AAA', CAST(3 AS Numeric(5, 0)), N'KEEP TYPING')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010522.AAA', CAST(1 AS Numeric(5, 0)), N'THE QUICK BROWN FOX JUMPED OVE THE LAZY DOGS BACK 11:05:15')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010522.AAB', CAST(2 AS Numeric(5, 0)), N'----------------------------------------------------------------------')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010522.AAB', CAST(3 AS Numeric(5, 0)), N'Incident Recalled From: 2000-00000158 AEGISPD')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010522.AAB', CAST(1 AS Numeric(5, 0)), N'QPGMR CHANGED LICENSE PLATE FROM 11:04:24')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010522.AAD', CAST(1 AS Numeric(5, 0)), N'Incident Created From: 2000-00000158 AEGISPD')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010809.AKB', CAST(4 AS Numeric(5, 0)), N' DPT: : QTR: 08/09/01 12:39')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010809.AKB', CAST(3 AS Numeric(5, 0)), N' DSP: 08/09/01 12:32 ENR: : ARV: :')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010809.AKB', CAST(2 AS Numeric(5, 0)), N' Unit#: 2324 Radio#: 000 Ofcr 1: Ofcr 2:')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010809.AKB', CAST(1 AS Numeric(5, 0)), N'Information on the units assigned to the call follows.')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010813.ABM', CAST(1 AS Numeric(5, 0)), N'QPGMR CHANGED LOCTN FROM 450,,CLEMS RUN,,RD 8:03:54')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010813.ABS', CAST(8 AS Numeric(5, 0)), N' DSP: : ARV: 08/13/01 17:22 CLR: 08/13/01 18:12')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010813.ABS', CAST(14 AS Numeric(5, 0)), N' DSP: : ARV: 08/13/01 17:22 CLR: 08/13/01 18:12')
INSERT [dbo].[PSFDOC] ([FDDOC#], [FDLINE], [FDNARR]) VALUES (N'20010813.ABS', CAST(6 AS Numeric(5, 0)), N' DSP: : ARV: 08/13/01 17:55 CLR: 08/13/01 18:01')
/****** Object: Table [dbo].[PSDOCI] Script Date: 04/23/2012 21:24:41 ******/
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RF', N'PLINCD', N'', N'Dispatch Narrative', N'200000000158 AEGISPD', N'20010522.AAA', N'', CAST(20010522 AS Numeric(8, 0)), N'QPGMR', CAST(20010522 AS Numeric(8, 0)), N'QPGMR', N'', N'', NULL, N'200000000158', CAST(0x000090A700000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'FF', N'PLINCD', N'', N'CAD System Narrative', N'200000000158 AEGISPD', N'20010522.AAB', N'', CAST(20010522 AS Numeric(8, 0)), N'QPGMR', CAST(20010522 AS Numeric(8, 0)), N'QPGMR', N'', N'', NULL, N'200000000158', CAST(0x000090A700000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RF', N'PLINCD', N'', N'Dispatch Narrative', N'200101021541 NJ0080600', N'20010814.AAP', N'', CAST(20010814 AS Numeric(8, 0)), N'JFOX', CAST(20010814 AS Numeric(8, 0)), N'JFOX', N'', N'', N'1506', N'200101021541', CAST(0x000090FB00000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RF', N'PLINCD', N'', N'CAD System Narrative', N'200000000160 AEGISPD', N'20010522.AAD', N'', CAST(20010522 AS Numeric(8, 0)), N'QPGMR', CAST(20010522 AS Numeric(8, 0)), N'QPGMR', N'', N'', NULL, N'200000000160', CAST(0x000090A700000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RF', N'PLINCD', N'', N'Dispatch Narrative', N'200100000002 NJ0081200', N'20010814.AAQ', N'', CAST(20010814 AS Numeric(8, 0)), N'PMARTIN', CAST(20010814 AS Numeric(8, 0)), N'PMARTIN', N'', N'', NULL, N'200100000002', CAST(0x000090FB00000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RF', N'PLINCD', N'', N'Dispatch Narrative', N'200100007617 NJ0080000', N'20010814.AAR', N'', CAST(20010814 AS Numeric(8, 0)), N'PMARTIN', CAST(20010814 AS Numeric(8, 0)), N'PMARTIN', N'', N'', N'1537', N'200100007617', CAST(0x000090FB00000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RF', N'PLINCD', N'', N'CAD System Narrative', N'200300005452 NJ0081800', N'20030219.AS1', N'', CAST(20030219 AS Numeric(8, 0)), N'CCMATHIS', CAST(20030219 AS Numeric(8, 0)), N'CCMATHIS', N'', N'', N'1518', N'200300005452', CAST(0x0000932500000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RM', N'PSAJCK', N'', N'mugshot', N'000002969A NJ0080100', N'20030219.AS2', N'', CAST(20030219 AS Numeric(8, 0)), N'VAARDECKI', CAST(20030219 AS Numeric(8, 0)), N'VAARDECKI', N'', N'', N'1501', N'000002969A ', CAST(0x0000932500000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RW', N'PLCASE', N'', N'WARRANT ARREST', N'200200001848 NJ0080300', N'20030219.ASZ', N'', CAST(20030219 AS Numeric(8, 0)), N'AMKILEY', CAST(20030219 AS Numeric(8, 0)), N'AMKILEY', N'', N'', N'1503', N'200200001848', CAST(0x0000932500000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RF', N'PLINCD', N'', N'Dispatch Narrative', N'200100000001 0808232', N'20010809.AKB', N'', CAST(20010809 AS Numeric(8, 0)), N'BFOX', CAST(20010809 AS Numeric(8, 0)), N'BFOX', N'', N'', NULL, N'200100000001', CAST(0x000090F600000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RF', N'PLINCD', N'', N'CAD System Narrative', N'200100000001 NJ0080800', N'20010813.ABM', N'', CAST(20010813 AS Numeric(8, 0)), N'AHOLLAND', CAST(20010813 AS Numeric(8, 0)), N'AHOLLAND', N'', N'', N'1508', N'200100000001', CAST(0x000090FA00000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RF', N'PLINCD', N'', N'Dispatch Narrative', N'200100000001 NJ0080100', N'20010813.ABS', N'', CAST(20010813 AS Numeric(8, 0)), N'WBRAMELL', CAST(20010813 AS Numeric(8, 0)), N'WBRAMELL', N'', N'', N'1501', N'200100000001', CAST(0x000090FA00000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RF', N'PLINCD', N'', N'Dispatch Narrative', N'200100000001 NJ0080500', N'20010813.ABT', N'', CAST(20010813 AS Numeric(8, 0)), N'WBRAMELL', CAST(20010813 AS Numeric(8, 0)), N'WBRAMELL', N'', N'', N'1505', N'200100000001', CAST(0x000090FA00000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RF', N'PLINCD', N'', N'Dispatch Narrative', N'200100000002 NJ0080500', N'20010814.AAO', N'', CAST(20010814 AS Numeric(8, 0)), N'WBRAMELL', CAST(20010814 AS Numeric(8, 0)), N'WBRAMELL', N'', N'', N'1505', N'200100000002', CAST(0x000090FB00000000 AS DateTime))
INSERT [dbo].[PSDOCI] ([DITYPE], [DIFILE], [DISECD], [DIDESC], [DIFKEY], [DIDOC#], [DIOLST], [DIDCD], [DIDCU], [DILCD], [DILCU], [DIFLD1], [DIFLD2], [JurisID], [IncidentIDRef], [EntDttm]) VALUES (N'RF', N'PLINCD', N'', N'CAD System Narrative', N'200100007617 NJ0080000', N'20010814.AAS', N'', CAST(20010814 AS Numeric(8, 0)), N'PMARTIN', CAST(20010814 AS Numeric(8, 0)), N'PMARTIN', N'', N'', N'1537', N'200100007617', CAST(0x000090FB00000000 AS DateTime))
Thanks,
Charmer
April 23, 2012 at 12:39 pm
Honestly I've never found an instance where a cursor was the only option... While loops have always worked quite well for me and without the headache or overhead of a cursor.
April 23, 2012 at 12:46 pm
While loops are no better (or worse) than a cursor. They should both be avoided except in some rare cases. The example in this post is NOT one of those.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2012 at 12:51 pm
Charmer you posted 2 of the 3 requested items and that one is only partially complete. You posted ddl for 4 tables but there is no data for SrcComment.
In your original code there is an insert but that table does not exists in this ddl.
The most important piece of information missing is:
A) Describe what the cursor is doing
We have most of the data but still no idea what to do with it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2012 at 7:38 pm
I am in 100% agreement with Sean that a CURSOR is not required in this case and add that I'm 100% certain that converting to a single SQL INSERT will run considerably faster than your CURSOR solution.
You are halfway there recognizing that your current run time is unacceptable.
Now, to go about this, it's difficult to give you an exact solution without some kind of description of what the CURSOR is doing. However, the steps would be something like the following:
1. Your code should consist of a single INSERT into GloucMidPolice.dbo.SrcComment.
2. Below the INSERT line, there should be an outer SELECT that transforms the fields from your input tables into the form needed for your output table.
3. The inner SELECT should essentially be what you used to establish your CURSOR.
4. The various conditions on your inner WHILE loops should be handled in the transformations in step 2 using CASE statements.
Don't give up. This is very doable and a great learning exercise once you see the approach.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 24, 2012 at 2:04 am
If you think the time taken is Unacceptable then you are definitely on the right track.
In 99.99% of the cases Cursors can be avoided...because that is what SQL server is meant to do. Its meant to do Set Based operations and not RBAR operations.
And if Cursors can be avoided then loops can also be avoided.
You start thinking of using a Cursor when you think of rows(RBAR) in SQL. SQL is not about rows....its about data fields(not rows) and SETs which form groups of these data fields.
If you plan to Select a data set and from that data set Select the required data then you definitely won't think of Cursors.
Avoid using Cursors. There is always a better Set Based Alternative.
April 24, 2012 at 2:46 am
Sean Lange (4/23/2012)
Charmer you posted 2 of the 3 requested items and that one is only partially complete. You posted ddl for 4 tables but there is no data for SrcComment.In your original code there is an insert but that table does not exists in this ddl.
The most important piece of information missing is:
A) Describe what the cursor is doing
We have most of the data but still no idea what to do with it.
PSDOCI table has a column DIDOC# which is like a primary key without duplicate ID's which is equal to FDDOC# column in PSFDOC table (as you see in first select statement in the cursor, i used to join these two columns to get the FDNARR column from the PSFDOC table)
I have attached screen shots for the select statement of those two tables and the result of SrcComment table)
My need is i need to get the FDNARR(this is like Comments) column based on FDLINE column (like a Sequence Number tells about the order)
FDNARR column in PSFDOC table must come in a single row(i mean concatenating all the FDNARR rows based on each FDDOC# ID order by FDLINE) for unique row of FDDOC# column order by FDLINE....
This must be inserted into Comment column in SrcComment...
Thanks,
Charmer
April 24, 2012 at 2:51 am
If you guys are still not understanding about my requirements, please let me know so that i'll give you better explanation of my requirement...
the two tables are like one to many relationship...
PSDOCI is parent table (DIDOC# Primary key)
PSFDOC is child table (FDDOC# foreign key) -multiple rows
i want to concatenate the FDNARR column based on DIDOC# as a single row and need to insert into the Comment column in SrcComment table...
Thanks,
Charmer
April 24, 2012 at 6:41 am
Okay, for clarification, is all this is doing is concatenating text together into a single text string, and then putting that in another table?
If so, you definitely don't need a cursor.
Or am I misundertanding the needs here?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply