May 29, 2012 at 5:52 am
Charmer (5/29/2012)
dwain.c (5/28/2012)
Charmer (5/28/2012)
anthony.green (5/28/2012)
You certainly don't need a CURSOR to do this.While I am loathe to admit it, I have a couple of cases where processing a large number of records was sped up by introducing a loop and processing them in batches. However, what I think you should do is SELECT your @IDs out into a TABLE VARIABLE, add a SeqNo using ROW_NUMBER() [during that SELECT] and then loop through the SeqNos, avoiding the CURSOR. You can then process multiple IDs at once using IN and use this to tune the query to see how many IDs processed at one time can achieve the best performance results.
I also suggest:
- Review your use of Dynamic SQL. I don't see any need for it in this case.
i did dynamic because i need to process only for certain ID's from a table...unless it process for all the ID's..
- Are all of those JOINs really necessary? Remove any that are not relevant
yes , it is necessary according my knowledge...but i will take your suggestion to recheck the joins
let me try with loop that you suggested and will let you know the result...
Thank you Dwain, for the help
My best recommendation is that you post DDL and some sample data for all the tables that are participating in the query (target for UPDATE and all JOINed tables). Posting the execution plan might also help some of the more expert forum posters (GilaMonster aka Gail Shaw might be able to provide you some guidelines on that).
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
May 29, 2012 at 5:58 am
Charmer (5/29/2012)
GilaMonster (5/29/2012)
No, a cursor is not likely to be good, neither is a while loop. What exactly are you trying to do? Iterative processing is typically slower than set-based in SQL server.If all you're trying to do is that update, then what you probably want to do is an UPDATE TOP (x) ... Where X is a sensible number, and have a way to tell whether a row has been updated or not. Then you just run in a loop, updating largish numbers of rows at a time.
yes, i have heard that cursor is not all good from you SQL guru's...
what i am doing is insert/update large amount of records based on a ID....
i don't need to process all the records at the same time since it kills the performance(i already tried it)....
i thought of going batch by batch and so i take one ID in single execution so that it will never kill the performance...
so i fetch each id and doing insert/update using cursor, and then next id and so on...
Gila, Could you please refer to my previous post..? i have put my sample cursor definition of what exactly i am doing here..
i googled for checking 'update top' command....but it is like "update top 100 table set column = value "...
could you please give me a sample so that i will work on that one..and i will come back to you with the result?
Thanks,
Charmer
May 29, 2012 at 6:01 am
ok dwain, let me try to get DDL', and DML's...
Thanks,
Charmer
May 29, 2012 at 6:12 am
first a gentle reprimand: you've seen enough posts about providing code...posting pseudo code gets you a psuedo-answer that you have to adapt and ask more questions about...if you posted your real cursor, we could adapt that to a instantly test-able example.
i've adapted your example to do a set based loop to process 10K records at a time;
see if you can adapt this to work for you.
--a temp table to hold all the ID's to be processed.
--this is used for situations where you cannot determine which data has been updated or not via a WHERE statement.
select distinct row_number() OVER (PARTITION BY ID ORDER BY ID) As RW,ID
INTO #IdsToProcess
from table1 t1
--because of an inner join in another place, we know we only need records that exist in Table + Table4
inner join table4 t4 on t1.Column1 = t4.Column1
GROUP BY ID --replaces the DISTINCT with the equivilent group by
----due to bandwith / time considerations, update in batches based on ROWCOUNT
--an arbitrary integer used to keep track of what has been processed.
DECLARE @i INT,
@max-2 INT
SET @i = 0;
SELECt @max-2=COUNT(1) FROM #IdsToProcess
WHILE 1 = 1 --yeah, i know
BEGIN
IF NOT EXISTS(SELECT 1
from
table1 t1
left outer join table2 t2 on t1.Column1 = t2.Column1
left outer join table3 t3 on t1.Column1 = t3.Column1
inner join table5 t5 on t3.Column1 = t5.Column1
WHERE t1.ID BETWEEN @i ANd (@i + 10000) --10K rows at a time.
)
BREAK; --bail out of the endless loop!
PRINT 'Iteration ' + CONVERT(VARCHAR(30),@i) + ' : ' + CONVERT(VARCHAR(30),@i + 10000) + ' Out Of ' + CONVERT(VARCHAR(30),@max )
--still here? do another update.
UPDATE Dest
--which table is the source?! you never aliased it in thepseudo code, left for you to fix on purpose
SET Dest.[Column1] = Source.[Column1]
from table1 Dest
inner join #IdsToProcess Temp On Dest.ID = Temp.ID
left outer join table2 t2 on t1.Column1 = t2.Column1
left outer join table3 t3 on t1.Column1 = t3.Column1
inner join table5 t5 on t3.Column1 = t5.Column1
WHERE Temp.ID BETWEEN @i AND (@i + 10000)
SET @i = @i + 10000 + 1
END
Lowell
May 29, 2012 at 6:34 am
I didn't initially see your comments embedded with mine. Let me see if I can clean this up a bit.
dwain.c (5/29/2012)
I also suggest:- Review your use of Dynamic SQL. I don't see any need for it in this case.
Charmer (5/29/2012)
i did dynamic because i need to process only for certain ID's from a table...unless it process for all the ID's..
I don't see what you've stated as a valid reason. You can see from what Lowell posted that it should not be necessary, even without the loop he obligingly wrote for you.
dwain.c (5/29/2012)
- Are all of those JOINs really necessary? Remove any that are not relevant
Charmer (5/29/2012)
yes , it is necessary according my knowledge...but i will take your suggestion to recheck the joins
I'm having a hard time understanding why you need something from Table5 to update your target table. That's a lot of joins. I won't go on a rant like CELKO would about proper normalization of data, but I have to believe there's a better (more efficient way) to get at whatever it is you think you need in Table5. That's why I suggested posting your DDL. Even with minimal sample data, we can take that to the next level and replicate it across a million rows or so to see exactly what is slowing it down.
Don't forget my suggestion about posting the query plan if you can figure out the right way to do this.
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
May 29, 2012 at 6:44 am
Charmer (5/29/2012)
i googled for checking 'update top' command....but it is like "update top 100 table set column = value "...
Yup, that's the command.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 29, 2012 at 6:47 am
GilaMonster (5/29/2012)
Charmer (5/29/2012)
i googled for checking 'update top' command....but it is like "update top 100 table set column = value "...Yup, that's the command.
oh....but i am not sure how the top is going to work for me?
Thanks,
Charmer
May 29, 2012 at 6:51 am
Charmer (5/29/2012)
GilaMonster (5/29/2012)
Charmer (5/29/2012)
i googled for checking 'update top' command....but it is like "update top 100 table set column = value "...Yup, that's the command.
oh....but i am not sure how the top is going to work for me?
update top or doing an update in batches is the suggestion when you say an update for a billion rows takes too long/locks table.
to minimize that impact, you do multiple updates of smaller batches...but as Gail mentioned, to do that with UPDATE TOP , you have to be able to determine what was updates and what was not (via a WHERE statement) ie WHERE Destination.Column1 <> Source.Column1
if you cannot determine what was updated via A WHERE statement , my example is one other way to do it.
Lowell
May 29, 2012 at 7:06 am
hi all here my ddl and dml and the cursor i use...
/****** Object: Table [dbo].[SrcCodeMap1] Script Date: 05/29/2012 18:30:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SrcCodeMap](
[JurisID] [char](4) NOT NULL,
[CodeID] [int] NOT NULL,
[SrcCodeValueRef] [varchar](100) NOT NULL,
[SrcCodeValue] [varchar](40) NOT NULL,
[PnxCodeValue] [nvarchar](50) NULL,
[SysCodeValue] [char](10) NULL,
[NewCodeValue] [char](10) NULL,
[VendorCode] [char](2) NULL,
[Misc1] [varchar](255) NULL,
[Misc2] [varchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SrcCodeMap] ([JurisID], [CodeID], [SrcCodeValueRef], [SrcCodeValue], [PnxCodeValue], [SysCodeValue], [NewCodeValue], [VendorCode], [Misc1], [Misc2]) VALUES (N'0 ', 100, N'NJ0089999', N'NJ0089999', N'1500 ', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[SrcCodeMap] ([JurisID], [CodeID], [SrcCodeValueRef], [SrcCodeValue], [PnxCodeValue], [SysCodeValue], [NewCodeValue], [VendorCode], [Misc1], [Misc2]) VALUES (N'0 ', 100, N'NJ0080100', N'NJ0080100', N'1501 ', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[SrcCodeMap] ([JurisID], [CodeID], [SrcCodeValueRef], [SrcCodeValue], [PnxCodeValue], [SysCodeValue], [NewCodeValue], [VendorCode], [Misc1], [Misc2]) VALUES (N'0 ', 100, N'NJ0080200', N'NJ0080200', N'1502 ', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[SrcCodeMap] ([JurisID], [CodeID], [SrcCodeValueRef], [SrcCodeValue], [PnxCodeValue], [SysCodeValue], [NewCodeValue], [VendorCode], [Misc1], [Misc2]) VALUES (N'0 ', 100, N'NJ0080300', N'NJ0080300', N'1503 ', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[SrcCodeMap] ([JurisID], [CodeID], [SrcCodeValueRef], [SrcCodeValue], [PnxCodeValue], [SysCodeValue], [NewCodeValue], [VendorCode], [Misc1], [Misc2]) VALUES (N'0 ', 100, N'NJ0080400', N'NJ0080400', N'1504 ', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[SrcCodeMap] ([JurisID], [CodeID], [SrcCodeValueRef], [SrcCodeValue], [PnxCodeValue], [SysCodeValue], [NewCodeValue], [VendorCode], [Misc1], [Misc2]) VALUES (N'0 ', 100, N'NJ0080500', N'NJ0080500', N'1505 ', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[SrcCodeMap] ([JurisID], [CodeID], [SrcCodeValueRef], [SrcCodeValue], [PnxCodeValue], [SysCodeValue], [NewCodeValue], [VendorCode], [Misc1], [Misc2]) VALUES (N'0 ', 100, N'NJ0080600', N'NJ0080600', N'1506 ', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[SrcCodeMap] ([JurisID], [CodeID], [SrcCodeValueRef], [SrcCodeValue], [PnxCodeValue], [SysCodeValue], [NewCodeValue], [VendorCode], [Misc1], [Misc2]) VALUES (N'0 ', 100, N'NJ0080700', N'NJ0080700', N'1507 ', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[SrcCodeMap] ([JurisID], [CodeID], [SrcCodeValueRef], [SrcCodeValue], [PnxCodeValue], [SysCodeValue], [NewCodeValue], [VendorCode], [Misc1], [Misc2]) VALUES (N'0 ', 100, N'NJ0080800', N'NJ0080800', N'1508 ', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[SrcCodeMap] ([JurisID], [CodeID], [SrcCodeValueRef], [SrcCodeValue], [PnxCodeValue], [SysCodeValue], [NewCodeValue], [VendorCode], [Misc1], [Misc2]) VALUES (N'0 ', 100, N'NJ0080900', N'NJ0080900', N'1509 ', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[SrcCodeMap] ([JurisID], [CodeID], [SrcCodeValueRef], [SrcCodeValue], [PnxCodeValue], [SysCodeValue], [NewCodeValue], [VendorCode], [Misc1], [Misc2]) VALUES (N'0 ', 100, N'NJ0081000', N'NJ0081000', N'1510 ', NULL, NULL, NULL, NULL, NULL)
/****** Object: Table [dbo].[PLITYP] Script Date: 05/29/2012 18:30:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PLITYP](
[ITITYP] [nvarchar](4) NULL,
[ITPRTY] [nvarchar](1) NULL,
[ITUNTS] [numeric](1, 0) NULL,
[ITRPT] [nvarchar](1) NULL,
[ITLCD] [numeric](8, 0) NULL,
[ITLCU] [nvarchar](10) NULL,
[ITSPRV] [nvarchar](1) NULL,
[ITAMBL] [nvarchar](1) NULL,
[ITUNTN] [numeric](1, 0) NULL,
[ITPRTN] [nvarchar](1) NULL,
[ITSUPN] [nvarchar](1) NULL,
[ITPFE] [nvarchar](1) NULL,
[ITUTP1] [nvarchar](4) NULL,
[ITUTP2] [nvarchar](4) NULL,
[ITRPLT] [nvarchar](1) NULL,
[ITDCHK] [nvarchar](1) NULL,
[ITDAY#] [numeric](5, 0) NULL,
[IT#OF1] [numeric](3, 0) NULL,
[IT#OF2] [numeric](3, 0) NULL,
[ITABBV] [nvarchar](10) NULL,
[ITDESC] [nvarchar](40) NULL,
[ITPRT3] [nvarchar](1) NULL,
[ITUNT3] [numeric](1, 0) NULL,
[ITUTP3] [nvarchar](4) NULL,
[ITSUP3] [nvarchar](1) NULL,
[IT#OF3] [numeric](3, 0) NULL,
[ITPRT4] [nvarchar](1) NULL,
[ITUNT4] [numeric](1, 0) NULL,
[ITUTP4] [nvarchar](4) NULL,
[ITSUP4] [nvarchar](1) NULL,
[IT#OF4] [numeric](3, 0) NULL,
[ITPRT5] [nvarchar](1) NULL,
[ITUNT5] [numeric](1, 0) NULL,
[ITUTP5] [nvarchar](4) NULL,
[ITSUP5] [nvarchar](1) NULL,
[IT#OF5] [numeric](3, 0) NULL,
[ITPRT6] [nvarchar](1) NULL,
[ITUNT6] [numeric](1, 0) NULL,
[ITUTP6] [nvarchar](4) NULL,
[ITSUP6] [nvarchar](1) NULL,
[IT#OF6] [numeric](3, 0) NULL,
[ITPRT7] [nvarchar](1) NULL,
[ITUNT7] [numeric](1, 0) NULL,
[ITUTP7] [nvarchar](4) NULL,
[ITSUP7] [nvarchar](1) NULL,
[IT#OF7] [numeric](3, 0) NULL,
[ITPRT8] [nvarchar](1) NULL,
[ITUNT8] [numeric](1, 0) NULL,
[ITUTP8] [nvarchar](4) NULL,
[ITSUP8] [nvarchar](1) NULL,
[IT#OF8] [numeric](3, 0) NULL,
[ITLCT] [numeric](6, 0) NULL,
[ITMINH] [numeric](3, 0) NULL,
[ITINAC] [nvarchar](1) NULL,
[ITINAD] [numeric](8, 0) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[PLITYP] ([ITITYP], [ITPRTY], [ITUNTS], [ITRPT], [ITLCD], [ITLCU], [ITSPRV], [ITAMBL], [ITUNTN], [ITPRTN], [ITSUPN], [ITPFE], [ITUTP1], [ITUTP2], [ITRPLT], [ITDCHK], [ITDAY#], [IT#OF1], [IT#OF2], [ITABBV], [ITDESC], [ITPRT3], [ITUNT3], [ITUTP3], [ITSUP3], [IT#OF3], [ITPRT4], [ITUNT4], [ITUTP4], [ITSUP4], [IT#OF4], [ITPRT5], [ITUNT5], [ITUTP5], [ITSUP5], [IT#OF5], [ITPRT6], [ITUNT6], [ITUTP6], [ITSUP6], [IT#OF6], [ITPRT7], [ITUNT7], [ITUTP7], [ITSUP7], [IT#OF7], [ITPRT8], [ITUNT8], [ITUTP8], [ITSUP8], [IT#OF8], [ITLCT], [ITMINH], [ITINAC], [ITINAD]) VALUES (N'AMBA', N'1', CAST(1 AS Numeric(1, 0)), N'N', CAST(20080114 AS Numeric(8, 0)), N'STEVEB', N'N', N'N', CAST(1 AS Numeric(1, 0)), N'9', N'N', N'P', N'', N'', N'B', N'', CAST(1 AS Numeric(5, 0)), CAST(0 AS Numeric(3, 0)), CAST(0 AS Numeric(3, 0)), N'AMB ASSIST', N'ASSISTING AMBULANCE', N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), CAST(143819 AS Numeric(6, 0)), CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(8, 0)))
INSERT [dbo].[PLITYP] ([ITITYP], [ITPRTY], [ITUNTS], [ITRPT], [ITLCD], [ITLCU], [ITSPRV], [ITAMBL], [ITUNTN], [ITPRTN], [ITSUPN], [ITPFE], [ITUTP1], [ITUTP2], [ITRPLT], [ITDCHK], [ITDAY#], [IT#OF1], [IT#OF2], [ITABBV], [ITDESC], [ITPRT3], [ITUNT3], [ITUTP3], [ITSUP3], [IT#OF3], [ITPRT4], [ITUNT4], [ITUTP4], [ITSUP4], [IT#OF4], [ITPRT5], [ITUNT5], [ITUTP5], [ITSUP5], [IT#OF5], [ITPRT6], [ITUNT6], [ITUTP6], [ITSUP6], [IT#OF6], [ITPRT7], [ITUNT7], [ITUTP7], [ITSUP7], [IT#OF7], [ITPRT8], [ITUNT8], [ITUTP8], [ITSUP8], [IT#OF8], [ITLCT], [ITMINH], [ITINAC], [ITINAD]) VALUES (N'GUNR', N'5', CAST(1 AS Numeric(1, 0)), N'N', CAST(20080114 AS Numeric(8, 0)), N'STEVEB', N'N', N'N', CAST(1 AS Numeric(1, 0)), N'5', N'N', N'P', N'', N'', N'B', N'', CAST(1 AS Numeric(5, 0)), CAST(0 AS Numeric(3, 0)), CAST(0 AS Numeric(3, 0)), N'Gun Regist', N'Gun Registration', N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), CAST(155132 AS Numeric(6, 0)), CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(8, 0)))
INSERT [dbo].[PLITYP] ([ITITYP], [ITPRTY], [ITUNTS], [ITRPT], [ITLCD], [ITLCU], [ITSPRV], [ITAMBL], [ITUNTN], [ITPRTN], [ITSUPN], [ITPFE], [ITUTP1], [ITUTP2], [ITRPLT], [ITDCHK], [ITDAY#], [IT#OF1], [IT#OF2], [ITABBV], [ITDESC], [ITPRT3], [ITUNT3], [ITUTP3], [ITSUP3], [IT#OF3], [ITPRT4], [ITUNT4], [ITUTP4], [ITSUP4], [IT#OF4], [ITPRT5], [ITUNT5], [ITUTP5], [ITSUP5], [IT#OF5], [ITPRT6], [ITUNT6], [ITUTP6], [ITSUP6], [IT#OF6], [ITPRT7], [ITUNT7], [ITUTP7], [ITSUP7], [IT#OF7], [ITPRT8], [ITUNT8], [ITUTP8], [ITSUP8], [IT#OF8], [ITLCT], [ITMINH], [ITINAC], [ITINAD]) VALUES (N'GEOV', N'9', CAST(1 AS Numeric(1, 0)), N'N', CAST(20080114 AS Numeric(8, 0)), N'STEVEB', N'N', N'N', CAST(1 AS Numeric(1, 0)), N'9', N'N', N'P', N'', N'', N'B', N'', CAST(1 AS Numeric(5, 0)), CAST(0 AS Numeric(3, 0)), CAST(0 AS Numeric(3, 0)), N'GEO Verify', N'GEO Verification', N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), CAST(155120 AS Numeric(6, 0)), CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(8, 0)))
INSERT [dbo].[PLITYP] ([ITITYP], [ITPRTY], [ITUNTS], [ITRPT], [ITLCD], [ITLCU], [ITSPRV], [ITAMBL], [ITUNTN], [ITPRTN], [ITSUPN], [ITPFE], [ITUTP1], [ITUTP2], [ITRPLT], [ITDCHK], [ITDAY#], [IT#OF1], [IT#OF2], [ITABBV], [ITDESC], [ITPRT3], [ITUNT3], [ITUTP3], [ITSUP3], [IT#OF3], [ITPRT4], [ITUNT4], [ITUTP4], [ITSUP4], [IT#OF4], [ITPRT5], [ITUNT5], [ITUTP5], [ITSUP5], [IT#OF5], [ITPRT6], [ITUNT6], [ITUTP6], [ITSUP6], [IT#OF6], [ITPRT7], [ITUNT7], [ITUTP7], [ITSUP7], [IT#OF7], [ITPRT8], [ITUNT8], [ITUTP8], [ITSUP8], [IT#OF8], [ITLCT], [ITMINH], [ITINAC], [ITINAD]) VALUES (N'DISO', N'2', CAST(1 AS Numeric(1, 0)), N'N', CAST(20080114 AS Numeric(8, 0)), N'STEVEB', N'N', N'N', CAST(1 AS Numeric(1, 0)), N'2', N'N', N'P', N'', N'', N'B', N'', CAST(1 AS Numeric(5, 0)), CAST(0 AS Numeric(3, 0)), CAST(0 AS Numeric(3, 0)), N'Diso Cond', N'Disorderly Conduct', N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), CAST(154941 AS Numeric(6, 0)), CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(8, 0)))
INSERT [dbo].[PLITYP] ([ITITYP], [ITPRTY], [ITUNTS], [ITRPT], [ITLCD], [ITLCU], [ITSPRV], [ITAMBL], [ITUNTN], [ITPRTN], [ITSUPN], [ITPFE], [ITUTP1], [ITUTP2], [ITRPLT], [ITDCHK], [ITDAY#], [IT#OF1], [IT#OF2], [ITABBV], [ITDESC], [ITPRT3], [ITUNT3], [ITUTP3], [ITSUP3], [IT#OF3], [ITPRT4], [ITUNT4], [ITUTP4], [ITSUP4], [IT#OF4], [ITPRT5], [ITUNT5], [ITUTP5], [ITSUP5], [IT#OF5], [ITPRT6], [ITUNT6], [ITUTP6], [ITSUP6], [IT#OF6], [ITPRT7], [ITUNT7], [ITUTP7], [ITSUP7], [IT#OF7], [ITPRT8], [ITUNT8], [ITUTP8], [ITSUP8], [IT#OF8], [ITLCT], [ITMINH], [ITINAC], [ITINAD]) VALUES (N'DISP', N'3', CAST(1 AS Numeric(1, 0)), N'N', CAST(20080114 AS Numeric(8, 0)), N'STEVEB', N'N', N'N', CAST(1 AS Numeric(1, 0)), N'3', N'N', N'P', N'', N'', N'B', N'', CAST(1 AS Numeric(5, 0)), CAST(0 AS Numeric(3, 0)), CAST(0 AS Numeric(3, 0)), N'Dispute', N'Dispute', N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), CAST(154944 AS Numeric(6, 0)), CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(8, 0)))
INSERT [dbo].[PLITYP] ([ITITYP], [ITPRTY], [ITUNTS], [ITRPT], [ITLCD], [ITLCU], [ITSPRV], [ITAMBL], [ITUNTN], [ITPRTN], [ITSUPN], [ITPFE], [ITUTP1], [ITUTP2], [ITRPLT], [ITDCHK], [ITDAY#], [IT#OF1], [IT#OF2], [ITABBV], [ITDESC], [ITPRT3], [ITUNT3], [ITUTP3], [ITSUP3], [IT#OF3], [ITPRT4], [ITUNT4], [ITUTP4], [ITSUP4], [IT#OF4], [ITPRT5], [ITUNT5], [ITUTP5], [ITSUP5], [IT#OF5], [ITPRT6], [ITUNT6], [ITUTP6], [ITSUP6], [IT#OF6], [ITPRT7], [ITUNT7], [ITUTP7], [ITSUP7], [IT#OF7], [ITPRT8], [ITUNT8], [ITUTP8], [ITSUP8], [IT#OF8], [ITLCT], [ITMINH], [ITINAC], [ITINAD]) VALUES (N'DMVE', N'5', CAST(1 AS Numeric(1, 0)), N'N', CAST(20080114 AS Numeric(8, 0)), N'STEVEB', N'N', N'N', CAST(1 AS Numeric(1, 0)), N'5', N'N', N'P', N'', N'', N'B', N'', CAST(1 AS Numeric(5, 0)), CAST(0 AS Numeric(3, 0)), CAST(0 AS Numeric(3, 0)), N'DMV', N'Disabled Motor Vehicle', N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), CAST(154951 AS Numeric(6, 0)), CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(8, 0)))
INSERT [dbo].[PLITYP] ([ITITYP], [ITPRTY], [ITUNTS], [ITRPT], [ITLCD], [ITLCU], [ITSPRV], [ITAMBL], [ITUNTN], [ITPRTN], [ITSUPN], [ITPFE], [ITUTP1], [ITUTP2], [ITRPLT], [ITDCHK], [ITDAY#], [IT#OF1], [IT#OF2], [ITABBV], [ITDESC], [ITPRT3], [ITUNT3], [ITUTP3], [ITSUP3], [IT#OF3], [ITPRT4], [ITUNT4], [ITUTP4], [ITSUP4], [IT#OF4], [ITPRT5], [ITUNT5], [ITUTP5], [ITSUP5], [IT#OF5], [ITPRT6], [ITUNT6], [ITUTP6], [ITSUP6], [IT#OF6], [ITPRT7], [ITUNT7], [ITUTP7], [ITSUP7], [IT#OF7], [ITPRT8], [ITUNT8], [ITUTP8], [ITSUP8], [IT#OF8], [ITLCT], [ITMINH], [ITINAC], [ITINAD]) VALUES (N'DOME', N'1', CAST(1 AS Numeric(1, 0)), N'N', CAST(20080114 AS Numeric(8, 0)), N'STEVEB', N'N', N'N', CAST(1 AS Numeric(1, 0)), N'1', N'N', N'P', N'', N'', N'B', N'', CAST(365 AS Numeric(5, 0)), CAST(0 AS Numeric(3, 0)), CAST(0 AS Numeric(3, 0)), N'Domestic', N'Domestic', N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), CAST(154954 AS Numeric(6, 0)), CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(8, 0)))
INSERT [dbo].[PLITYP] ([ITITYP], [ITPRTY], [ITUNTS], [ITRPT], [ITLCD], [ITLCU], [ITSPRV], [ITAMBL], [ITUNTN], [ITPRTN], [ITSUPN], [ITPFE], [ITUTP1], [ITUTP2], [ITRPLT], [ITDCHK], [ITDAY#], [IT#OF1], [IT#OF2], [ITABBV], [ITDESC], [ITPRT3], [ITUNT3], [ITUTP3], [ITSUP3], [IT#OF3], [ITPRT4], [ITUNT4], [ITUTP4], [ITSUP4], [IT#OF4], [ITPRT5], [ITUNT5], [ITUTP5], [ITSUP5], [IT#OF5], [ITPRT6], [ITUNT6], [ITUTP6], [ITSUP6], [IT#OF6], [ITPRT7], [ITUNT7], [ITUTP7], [ITSUP7], [IT#OF7], [ITPRT8], [ITUNT8], [ITUTP8], [ITSUP8], [IT#OF8], [ITLCT], [ITMINH], [ITINAC], [ITINAD]) VALUES (N'BURE', N'1', CAST(2 AS Numeric(1, 0)), N'N', CAST(20080114 AS Numeric(8, 0)), N'STEVEB', N'N', N'N', CAST(1 AS Numeric(1, 0)), N'3', N'N', N'P', N'', N'', N'B', N'', CAST(7 AS Numeric(5, 0)), CAST(0 AS Numeric(3, 0)), CAST(0 AS Numeric(3, 0)), N'BurglEnter', N'Burglary-Entering', N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), CAST(154844 AS Numeric(6, 0)), CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(8, 0)))
INSERT [dbo].[PLITYP] ([ITITYP], [ITPRTY], [ITUNTS], [ITRPT], [ITLCD], [ITLCU], [ITSPRV], [ITAMBL], [ITUNTN], [ITPRTN], [ITSUPN], [ITPFE], [ITUTP1], [ITUTP2], [ITRPLT], [ITDCHK], [ITDAY#], [IT#OF1], [IT#OF2], [ITABBV], [ITDESC], [ITPRT3], [ITUNT3], [ITUTP3], [ITSUP3], [IT#OF3], [ITPRT4], [ITUNT4], [ITUTP4], [ITSUP4], [IT#OF4], [ITPRT5], [ITUNT5], [ITUTP5], [ITSUP5], [IT#OF5], [ITPRT6], [ITUNT6], [ITUTP6], [ITSUP6], [IT#OF6], [ITPRT7], [ITUNT7], [ITUTP7], [ITSUP7], [IT#OF7], [ITPRT8], [ITUNT8], [ITUTP8], [ITSUP8], [IT#OF8], [ITLCT], [ITMINH], [ITINAC], [ITINAD]) VALUES (N'DWIN', N'1', CAST(1 AS Numeric(1, 0)), N'N', CAST(20080114 AS Numeric(8, 0)), N'STEVEB', N'N', N'N', CAST(1 AS Numeric(1, 0)), N'1', N'N', N'P', N'', N'', N'B', N'', CAST(1 AS Numeric(5, 0)), CAST(0 AS Numeric(3, 0)), CAST(0 AS Numeric(3, 0)), N'DWI', N'DWI', N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), CAST(155000 AS Numeric(6, 0)), CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(8, 0)))
INSERT [dbo].[PLITYP] ([ITITYP], [ITPRTY], [ITUNTS], [ITRPT], [ITLCD], [ITLCU], [ITSPRV], [ITAMBL], [ITUNTN], [ITPRTN], [ITSUPN], [ITPFE], [ITUTP1], [ITUTP2], [ITRPLT], [ITDCHK], [ITDAY#], [IT#OF1], [IT#OF2], [ITABBV], [ITDESC], [ITPRT3], [ITUNT3], [ITUTP3], [ITSUP3], [IT#OF3], [ITPRT4], [ITUNT4], [ITUTP4], [ITSUP4], [IT#OF4], [ITPRT5], [ITUNT5], [ITUTP5], [ITSUP5], [IT#OF5], [ITPRT6], [ITUNT6], [ITUTP6], [ITSUP6], [IT#OF6], [ITPRT7], [ITUNT7], [ITUTP7], [ITSUP7], [IT#OF7], [ITPRT8], [ITUNT8], [ITUTP8], [ITSUP8], [IT#OF8], [ITLCT], [ITMINH], [ITINAC], [ITINAD]) VALUES (N'ERTE', N'1', CAST(1 AS Numeric(1, 0)), N'N', CAST(20080114 AS Numeric(8, 0)), N'STEVEB', N'N', N'N', CAST(1 AS Numeric(1, 0)), N'1', N'N', N'P', N'', N'', N'B', N'', CAST(1 AS Numeric(5, 0)), CAST(0 AS Numeric(3, 0)), CAST(0 AS Numeric(3, 0)), N'ERT', N'ERT', N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'9', CAST(1 AS Numeric(1, 0)), N'', N'N', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(1, 0)), N'', N'', CAST(0 AS Numeric(3, 0)), CAST(155009 AS Numeric(6, 0)), CAST(0 AS Numeric(3, 0)), N'', CAST(0 AS Numeric(8, 0)))
/****** Object: Table [dbo].[PLINCD] Script Date: 05/29/2012 18:30:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PLINCD](
[INORI#] [nvarchar](9) NULL,
[ININC#] [nvarchar](12) NULL,
[INADR#] [numeric](11, 0) NULL,
[INCOMN] [nvarchar](35) NULL,
[INPHON] [numeric](10, 0) NULL,
[INAREA] [nvarchar](4) NULL,
[INSECT] [nvarchar](4) NULL,
[INBEAT] [nvarchar](4) NULL,
[INQUAD] [nvarchar](4) NULL,
[INDIST] [nvarchar](4) NULL,
[INGRID] [nvarchar](4) NULL,
[INUNT1] [nvarchar](6) NULL,
[INBDG1] [nvarchar](9) NULL,
[INBDG2] [nvarchar](9) NULL,
[INUNT2] [nvarchar](6) NULL,
[INBDG3] [nvarchar](9) NULL,
[INBDG4] [nvarchar](9) NULL,
[INITYP] [nvarchar](4) NULL,
[INTYPE] [nvarchar](1) NULL,
[INRPT] [nvarchar](1) NULL,
[INPRTY] [nvarchar](1) NULL,
[INPRIR] [nvarchar](1) NULL,
[INDESC] [nvarchar](60) NULL,
[INDOW] [nvarchar](4) NULL,
[INROR#] [nvarchar](9) NULL,
[INRID#] [nvarchar](9) NULL,
[INDOR#] [nvarchar](9) NULL,
[INDID#] [nvarchar](9) NULL,
[INOPER] [nvarchar](10) NULL,
[INSHFT] [nvarchar](4) NULL,
[INSORC] [nvarchar](4) NULL,
[INPSTS] [nvarchar](4) NULL,
[INBURE] [nvarchar](4) NULL,
[INSTTN] [nvarchar](4) NULL,
[INEXPG] [nvarchar](1) NULL,
[INMUTA] [nvarchar](4) NULL,
[INMORI] [nvarchar](9) NULL,
[INMINC] [nvarchar](12) NULL,
[INPCAS] [nvarchar](12) NULL,
[INFCAS] [nvarchar](12) NULL,
[INECAS] [nvarchar](12) NULL,
[INVLI#] [nvarchar](8) NULL,
[INVLIS] [nvarchar](4) NULL,
[INVLYR] [numeric](4, 0) NULL,
[INVLIT] [nvarchar](4) NULL,
[INENTD] [numeric](8, 0) NULL,
[INENTT] [numeric](6, 0) NULL,
[INCALD] [numeric](8, 0) NULL,
[INCALT] [numeric](6, 0) NULL,
[INDSPD] [numeric](8, 0) NULL,
[INDSPT] [numeric](6, 0) NULL,
[INENRD] [numeric](8, 0) NULL,
[INENRT] [numeric](6, 0) NULL,
[INAR1D] [numeric](8, 0) NULL,
[INAR1T] [numeric](6, 0) NULL,
[INDP1D] [numeric](8, 0) NULL,
[INDP1T] [numeric](6, 0) NULL,
[INAR2D] [numeric](8, 0) NULL,
[INAR2T] [numeric](6, 0) NULL,
[INDP2D] [numeric](8, 0) NULL,
[INDP2T] [numeric](6, 0) NULL,
[ININSD] [numeric](8, 0) NULL,
[ININST] [numeric](6, 0) NULL,
[INCLRD] [numeric](8, 0) NULL,
[INCLRT] [numeric](6, 0) NULL,
[INDAT1] [numeric](8, 0) NULL,
[INTIM1] [numeric](6, 0) NULL,
[INDAT2] [numeric](8, 0) NULL,
[INTIM2] [numeric](6, 0) NULL,
[INDAT3] [numeric](8, 0) NULL,
[INTIM3] [numeric](6, 0) NULL,
[INOSTR] [nvarchar](45) NULL,
[INOXST] [nvarchar](45) NULL,
[INOVEN] [nvarchar](4) NULL,
[INOSTP] [nvarchar](1) NULL,
[INOITP] [nvarchar](4) NULL,
[INOITG] [nvarchar](1) NULL,
[INOPTY] [nvarchar](1) NULL,
[INLCD] [numeric](8, 0) NULL,
[INLCT] [numeric](6, 0) NULL,
[INLCU] [nvarchar](10) NULL,
[INDES2] [nvarchar](60) NULL,
[INPHN2] [numeric](10, 0) NULL,
[INPHN3] [numeric](10, 0) NULL,
[INADN1] [nvarchar](70) NULL,
[INADN2] [nvarchar](70) NULL,
[INADN3] [nvarchar](70) NULL,
[INADN4] [nvarchar](70) NULL,
[INADN5] [nvarchar](70) NULL,
[INADN6] [nvarchar](70) NULL,
[INCTRM] [nvarchar](10) NULL,
[INDTRM] [nvarchar](10) NULL,
[INCLTR] [nvarchar](10) NULL,
[INCLOR] [nvarchar](9) NULL,
[INCLID] [nvarchar](9) NULL,
[INLATD] [numeric](10, 7) NULL,
[INLONG] [numeric](10, 7) NULL,
[JurisID] [varchar](4) NULL,
[RptDTTM] [datetime] NULL,
[DpDttm] [datetime] NULL,
[ArDTTM] [datetime] NULL,
[FinDttm] [datetime] NULL,
[Modifieddttm] [datetime] NULL,
[CreatedDttm] [datetime] NULL,
[IncidentIDRef] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[PLINCD] ([INORI#], [ININC#], [INADR#], [INCOMN], [INPHON], [INAREA], [INSECT], [INBEAT], [INQUAD], [INDIST], [INGRID], [INUNT1], [INBDG1], [INBDG2], [INUNT2], [INBDG3], [INBDG4], [INITYP], [INTYPE], [INRPT], [INPRTY], [INPRIR], [INDESC], [INDOW], [INROR#], [INRID#], [INDOR#], [INDID#], [INOPER], [INSHFT], [INSORC], [INPSTS], [INBURE], [INSTTN], [INEXPG], [INMUTA], [INMORI], [INMINC], [INPCAS], [INFCAS], [INECAS], [INVLI#], [INVLIS], [INVLYR], [INVLIT], [INENTD], [INENTT], [INCALD], [INCALT], [INDSPD], [INDSPT], [INENRD], [INENRT], [INAR1D], [INAR1T], [INDP1D], [INDP1T], [INAR2D], [INAR2T], [INDP2D], [INDP2T], [ININSD], [ININST], [INCLRD], [INCLRT], [INDAT1], [INTIM1], [INDAT2], [INTIM2], [INDAT3], [INTIM3], [INOSTR], [INOXST], [INOVEN], [INOSTP], [INOITP], [INOITG], [INOPTY], [INLCD], [INLCT], [INLCU], [INDES2], [INPHN2], [INPHN3], [INADN1], [INADN2], [INADN3], [INADN4], [INADN5], [INADN6], [INCTRM], [INDTRM], [INCLTR], [INCLOR], [INCLID], [INLATD], [INLONG], [JurisID], [RptDTTM], [DpDttm], [ArDTTM], [FinDttm], [Modifieddttm], [CreatedDttm], [IncidentIDRef]) VALUES (N'NJ0080000', N'200100000001', CAST(22099 AS Numeric(11, 0)), N'', CAST(0 AS Numeric(10, 0)), N' X', N' X', N' X', N'', N'', N' L5', N'', N'', N'', N'', N'', N'', N'ABAN', N'P', N'N', N'1', N'', N'WHATEVER', N' 2', N'NJ0082400', N'', N'NJ0080000', N'', N'QPGMR', N'', N'', N'', N'', N'', N'', N'', N'NJ0080000', N'200100000001', N'', N'', N'', N'', N'', CAST(0 AS Numeric(4, 0)), N'', CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010618 AS Numeric(8, 0)), CAST(153030 AS Numeric(6, 0)), CAST(20010618 AS Numeric(8, 0)), CAST(153030 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010618 AS Numeric(8, 0)), CAST(153030 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010618 AS Numeric(8, 0)), CAST(153030 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), N'146,,RAINEY,RD,', N',,,', N' X', N'S', N'TEST', N'P', N'1', CAST(20010814 AS Numeric(8, 0)), CAST(64533 AS Numeric(6, 0)), N'QSECOFR', N'', CAST(0 AS Numeric(10, 0)), CAST(0 AS Numeric(10, 0)), N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(0.0000000 AS Numeric(10, 7)), CAST(0.0000000 AS Numeric(10, 7)), N'1537', CAST(0x000090C200FF91C8 AS DateTime), CAST(0x000090C200FF91C8 AS DateTime), NULL, NULL, NULL, NULL, N'200100000001')
INSERT [dbo].[PLINCD] ([INORI#], [ININC#], [INADR#], [INCOMN], [INPHON], [INAREA], [INSECT], [INBEAT], [INQUAD], [INDIST], [INGRID], [INUNT1], [INBDG1], [INBDG2], [INUNT2], [INBDG3], [INBDG4], [INITYP], [INTYPE], [INRPT], [INPRTY], [INPRIR], [INDESC], [INDOW], [INROR#], [INRID#], [INDOR#], [INDID#], [INOPER], [INSHFT], [INSORC], [INPSTS], [INBURE], [INSTTN], [INEXPG], [INMUTA], [INMORI], [INMINC], [INPCAS], [INFCAS], [INECAS], [INVLI#], [INVLIS], [INVLYR], [INVLIT], [INENTD], [INENTT], [INCALD], [INCALT], [INDSPD], [INDSPT], [INENRD], [INENRT], [INAR1D], [INAR1T], [INDP1D], [INDP1T], [INAR2D], [INAR2T], [INDP2D], [INDP2T], [ININSD], [ININST], [INCLRD], [INCLRT], [INDAT1], [INTIM1], [INDAT2], [INTIM2], [INDAT3], [INTIM3], [INOSTR], [INOXST], [INOVEN], [INOSTP], [INOITP], [INOITG], [INOPTY], [INLCD], [INLCT], [INLCU], [INDES2], [INPHN2], [INPHN3], [INADN1], [INADN2], [INADN3], [INADN4], [INADN5], [INADN6], [INCTRM], [INDTRM], [INCLTR], [INCLOR], [INCLID], [INLATD], [INLONG], [JurisID], [RptDTTM], [DpDttm], [ArDTTM], [FinDttm], [Modifieddttm], [CreatedDttm], [IncidentIDRef]) VALUES (N'NJ0081800', N'200300005458', CAST(229458 AS Numeric(11, 0)), N'', CAST(8006332677 AS Numeric(10, 0)), N' R4', N' 10', N' R', N'1008', N'1008', N' K17', N'0050', N' 1050', N'', N'', N'', N'', N'BALA', N'P', N'N', N'2', N'', N'LYONS/218-8508', N' 4', N'NJ0089999', N' CT1614', N'NJ0089999', N' CT1614', N'LDKENNIE', N' B', N' TEL', N'', N'', N'', N'', N'', N'NJ0081800', N'200300005458', N'', N'', N'', N'', N'', CAST(0 AS Numeric(4, 0)), N'', CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20030219 AS Numeric(8, 0)), CAST(122819 AS Numeric(6, 0)), CAST(20030219 AS Numeric(8, 0)), CAST(122846 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20030219 AS Numeric(8, 0)), CAST(123635 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20030219 AS Numeric(8, 0)), CAST(124747 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), N'15 WINDY ACRE DR', N',,,', N' R', N'S', N'BALA', N'P', N'2', CAST(20030219 AS Numeric(8, 0)), CAST(124747 AS Numeric(6, 0)), N'LDKENNIE', N'', CAST(0 AS Numeric(10, 0)), CAST(0 AS Numeric(10, 0)), N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(0.0000000 AS Numeric(10, 7)), CAST(0.0000000 AS Numeric(10, 7)), N'1518', CAST(0x0000932500CD8804 AS DateTime), CAST(0x0000932500CDA7A8 AS DateTime), NULL, NULL, NULL, NULL, N'200300005458')
INSERT [dbo].[PLINCD] ([INORI#], [ININC#], [INADR#], [INCOMN], [INPHON], [INAREA], [INSECT], [INBEAT], [INQUAD], [INDIST], [INGRID], [INUNT1], [INBDG1], [INBDG2], [INUNT2], [INBDG3], [INBDG4], [INITYP], [INTYPE], [INRPT], [INPRTY], [INPRIR], [INDESC], [INDOW], [INROR#], [INRID#], [INDOR#], [INDID#], [INOPER], [INSHFT], [INSORC], [INPSTS], [INBURE], [INSTTN], [INEXPG], [INMUTA], [INMORI], [INMINC], [INPCAS], [INFCAS], [INECAS], [INVLI#], [INVLIS], [INVLYR], [INVLIT], [INENTD], [INENTT], [INCALD], [INCALT], [INDSPD], [INDSPT], [INENRD], [INENRT], [INAR1D], [INAR1T], [INDP1D], [INDP1T], [INAR2D], [INAR2T], [INDP2D], [INDP2T], [ININSD], [ININST], [INCLRD], [INCLRT], [INDAT1], [INTIM1], [INDAT2], [INTIM2], [INDAT3], [INTIM3], [INOSTR], [INOXST], [INOVEN], [INOSTP], [INOITP], [INOITG], [INOPTY], [INLCD], [INLCT], [INLCU], [INDES2], [INPHN2], [INPHN3], [INADN1], [INADN2], [INADN3], [INADN4], [INADN5], [INADN6], [INCTRM], [INDTRM], [INCLTR], [INCLOR], [INCLID], [INLATD], [INLONG], [JurisID], [RptDTTM], [DpDttm], [ArDTTM], [FinDttm], [Modifieddttm], [CreatedDttm], [IncidentIDRef]) VALUES (N'NJ0080800', N'200100000002', CAST(251102 AS Numeric(11, 0)), N'', CAST(0 AS Numeric(10, 0)), N' H', N' H', N' H', N'2307', N'2307', N' M15', N'', N'', N'', N'', N'', N'', N'MVAC', N'P', N'Y', N'1', N'', N'', N' 5', N'NJ0089999', N' BF7363', N'', N'', N'AHOLLAND', N' D', N' TEL', N'', N'', N'23-2', N'', N'', N'NJ0080800', N'200100000002', N'', N'', N'', N'', N'', CAST(0 AS Numeric(4, 0)), N'', CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010809 AS Numeric(8, 0)), CAST(123424 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(161540 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), N'MULLICA HILL RD', N',RT 55 OVERPASS,,', N' H', N'X', N'MVAC', N'P', N'1', CAST(20010813 AS Numeric(8, 0)), CAST(161540 AS Numeric(6, 0)), N'AHOLLAND', N'', CAST(0 AS Numeric(10, 0)), CAST(0 AS Numeric(10, 0)), N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(0.0000000 AS Numeric(10, 7)), CAST(0.0000000 AS Numeric(10, 7)), N'1508', CAST(0x000090F600CF33C0 AS DateTime), CAST(0x0000000000000000 AS DateTime), NULL, NULL, NULL, NULL, N'200100000002')
INSERT [dbo].[PLINCD] ([INORI#], [ININC#], [INADR#], [INCOMN], [INPHON], [INAREA], [INSECT], [INBEAT], [INQUAD], [INDIST], [INGRID], [INUNT1], [INBDG1], [INBDG2], [INUNT2], [INBDG3], [INBDG4], [INITYP], [INTYPE], [INRPT], [INPRTY], [INPRIR], [INDESC], [INDOW], [INROR#], [INRID#], [INDOR#], [INDID#], [INOPER], [INSHFT], [INSORC], [INPSTS], [INBURE], [INSTTN], [INEXPG], [INMUTA], [INMORI], [INMINC], [INPCAS], [INFCAS], [INECAS], [INVLI#], [INVLIS], [INVLYR], [INVLIT], [INENTD], [INENTT], [INCALD], [INCALT], [INDSPD], [INDSPT], [INENRD], [INENRT], [INAR1D], [INAR1T], [INDP1D], [INDP1T], [INAR2D], [INAR2T], [INDP2D], [INDP2T], [ININSD], [ININST], [INCLRD], [INCLRT], [INDAT1], [INTIM1], [INDAT2], [INTIM2], [INDAT3], [INTIM3], [INOSTR], [INOXST], [INOVEN], [INOSTP], [INOITP], [INOITG], [INOPTY], [INLCD], [INLCT], [INLCU], [INDES2], [INPHN2], [INPHN3], [INADN1], [INADN2], [INADN3], [INADN4], [INADN5], [INADN6], [INCTRM], [INDTRM], [INCLTR], [INCLOR], [INCLID], [INLATD], [INLONG], [JurisID], [RptDTTM], [DpDttm], [ArDTTM], [FinDttm], [Modifieddttm], [CreatedDttm], [IncidentIDRef]) VALUES (N'NJ0081000', N'200100000004', CAST(99669 AS Numeric(11, 0)), N'', CAST(0 AS Numeric(10, 0)), N' J', N' J', N' J', N'2204', N'2204', N' J15', N'', N'', N'', N'', N'', N'', N'FIRA', N'P', N'Y', N'2', N'', N'', N' 3', N'NJ0080000', N' JN1235', N'', N'', N'AHOLLAND', N' D', N' TEL', N'', N'', N'22-4', N'', N'', N'NJ0081000', N'200100000004', N'', N'', N'', N'', N'', CAST(0 AS Numeric(4, 0)), N'', CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010807 AS Numeric(8, 0)), CAST(75125 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(161558 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), N'809 PASSAIC AVE', N',,,', N' J', N'S', N'FIRA', N'P', N'2', CAST(20010813 AS Numeric(8, 0)), CAST(161558 AS Numeric(6, 0)), N'AHOLLAND', N'', CAST(0 AS Numeric(10, 0)), CAST(0 AS Numeric(10, 0)), N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(0.0000000 AS Numeric(10, 7)), CAST(0.0000000 AS Numeric(10, 7)), N'1510', CAST(0x000090F400817A7C AS DateTime), CAST(0x0000000000000000 AS DateTime), NULL, NULL, NULL, NULL, N'200100000004')
INSERT [dbo].[PLINCD] ([INORI#], [ININC#], [INADR#], [INCOMN], [INPHON], [INAREA], [INSECT], [INBEAT], [INQUAD], [INDIST], [INGRID], [INUNT1], [INBDG1], [INBDG2], [INUNT2], [INBDG3], [INBDG4], [INITYP], [INTYPE], [INRPT], [INPRTY], [INPRIR], [INDESC], [INDOW], [INROR#], [INRID#], [INDOR#], [INDID#], [INOPER], [INSHFT], [INSORC], [INPSTS], [INBURE], [INSTTN], [INEXPG], [INMUTA], [INMORI], [INMINC], [INPCAS], [INFCAS], [INECAS], [INVLI#], [INVLIS], [INVLYR], [INVLIT], [INENTD], [INENTT], [INCALD], [INCALT], [INDSPD], [INDSPT], [INENRD], [INENRT], [INAR1D], [INAR1T], [INDP1D], [INDP1T], [INAR2D], [INAR2T], [INDP2D], [INDP2T], [ININSD], [ININST], [INCLRD], [INCLRT], [INDAT1], [INTIM1], [INDAT2], [INTIM2], [INDAT3], [INTIM3], [INOSTR], [INOXST], [INOVEN], [INOSTP], [INOITP], [INOITG], [INOPTY], [INLCD], [INLCT], [INLCU], [INDES2], [INPHN2], [INPHN3], [INADN1], [INADN2], [INADN3], [INADN4], [INADN5], [INADN6], [INCTRM], [INDTRM], [INCLTR], [INCLOR], [INCLID], [INLATD], [INLONG], [JurisID], [RptDTTM], [DpDttm], [ArDTTM], [FinDttm], [Modifieddttm], [CreatedDttm], [IncidentIDRef]) VALUES (N'NJ0080800', N'200100000001', CAST(380444 AS Numeric(11, 0)), N'', CAST(0 AS Numeric(10, 0)), N' H', N' H', N' H', N'2305', N'2305', N' M13', N'', N'', N'', N'', N'', N'', N'FIRA', N'P', N'Y', N'2', N'', N'', N' 5', N'NJ0089999', N' BF7363', N'', N'', N'AHOLLAND', N' D', N' TEL', N'', N'', N'23-2', N'', N'', N'NJ0080800', N'200100000001', N'', N'', N'', N'', N'', CAST(0 AS Numeric(4, 0)), N'', CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010809 AS Numeric(8, 0)), CAST(123211 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(161558 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), N'450 CLEMS RUN RD', N',,,', N' H', N'S', N'FIRA', N'P', N'2', CAST(20010813 AS Numeric(8, 0)), CAST(161558 AS Numeric(6, 0)), N'AHOLLAND', N'', CAST(0 AS Numeric(10, 0)), CAST(0 AS Numeric(10, 0)), N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(0.0000000 AS Numeric(10, 7)), CAST(0.0000000 AS Numeric(10, 7)), N'1508', CAST(0x000090F600CE97E4 AS DateTime), CAST(0x0000000000000000 AS DateTime), NULL, NULL, NULL, NULL, N'200100000001')
INSERT [dbo].[PLINCD] ([INORI#], [ININC#], [INADR#], [INCOMN], [INPHON], [INAREA], [INSECT], [INBEAT], [INQUAD], [INDIST], [INGRID], [INUNT1], [INBDG1], [INBDG2], [INUNT2], [INBDG3], [INBDG4], [INITYP], [INTYPE], [INRPT], [INPRTY], [INPRIR], [INDESC], [INDOW], [INROR#], [INRID#], [INDOR#], [INDID#], [INOPER], [INSHFT], [INSORC], [INPSTS], [INBURE], [INSTTN], [INEXPG], [INMUTA], [INMORI], [INMINC], [INPCAS], [INFCAS], [INECAS], [INVLI#], [INVLIS], [INVLYR], [INVLIT], [INENTD], [INENTT], [INCALD], [INCALT], [INDSPD], [INDSPT], [INENRD], [INENRT], [INAR1D], [INAR1T], [INDP1D], [INDP1T], [INAR2D], [INAR2T], [INDP2D], [INDP2T], [ININSD], [ININST], [INCLRD], [INCLRT], [INDAT1], [INTIM1], [INDAT2], [INTIM2], [INDAT3], [INTIM3], [INOSTR], [INOXST], [INOVEN], [INOSTP], [INOITP], [INOITG], [INOPTY], [INLCD], [INLCT], [INLCU], [INDES2], [INPHN2], [INPHN3], [INADN1], [INADN2], [INADN3], [INADN4], [INADN5], [INADN6], [INCTRM], [INDTRM], [INCLTR], [INCLOR], [INCLID], [INLATD], [INLONG], [JurisID], [RptDTTM], [DpDttm], [ArDTTM], [FinDttm], [Modifieddttm], [CreatedDttm], [IncidentIDRef]) VALUES (N'NJ0081000', N'200100000001', CAST(24957 AS Numeric(11, 0)), N'', CAST(0 AS Numeric(10, 0)), N' J', N' J', N' J', N'2202', N'2202', N' H15', N'', N'', N'', N'', N'', N'', N'FIRC', N'P', N'Y', N'3', N'', N'', N' 4', N'NJ0080000', N' JN1235', N'', N'', N'AHOLLAND', N' D', N' TEL', N'', N'', N'22-2', N'', N'', N'NJ0081000', N'200100000001', N'', N'', N'', N'', N'', CAST(0 AS Numeric(4, 0)), N'', CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010801 AS Numeric(8, 0)), CAST(93156 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(161558 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), N'215 MANTUA BLVD', N',,,', N' J', N'S', N'FIRC', N'P', N'3', CAST(20010813 AS Numeric(8, 0)), CAST(161558 AS Numeric(6, 0)), N'AHOLLAND', N'', CAST(0 AS Numeric(10, 0)), CAST(0 AS Numeric(10, 0)), N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(0.0000000 AS Numeric(10, 7)), CAST(0.0000000 AS Numeric(10, 7)), N'1510', CAST(0x000090EE009D1610 AS DateTime), CAST(0x0000000000000000 AS DateTime), NULL, NULL, NULL, NULL, N'200100000001')
INSERT [dbo].[PLINCD] ([INORI#], [ININC#], [INADR#], [INCOMN], [INPHON], [INAREA], [INSECT], [INBEAT], [INQUAD], [INDIST], [INGRID], [INUNT1], [INBDG1], [INBDG2], [INUNT2], [INBDG3], [INBDG4], [INITYP], [INTYPE], [INRPT], [INPRTY], [INPRIR], [INDESC], [INDOW], [INROR#], [INRID#], [INDOR#], [INDID#], [INOPER], [INSHFT], [INSORC], [INPSTS], [INBURE], [INSTTN], [INEXPG], [INMUTA], [INMORI], [INMINC], [INPCAS], [INFCAS], [INECAS], [INVLI#], [INVLIS], [INVLYR], [INVLIT], [INENTD], [INENTT], [INCALD], [INCALT], [INDSPD], [INDSPT], [INENRD], [INENRT], [INAR1D], [INAR1T], [INDP1D], [INDP1T], [INAR2D], [INAR2T], [INDP2D], [INDP2T], [ININSD], [ININST], [INCLRD], [INCLRT], [INDAT1], [INTIM1], [INDAT2], [INTIM2], [INDAT3], [INTIM3], [INOSTR], [INOXST], [INOVEN], [INOSTP], [INOITP], [INOITG], [INOPTY], [INLCD], [INLCT], [INLCU], [INDES2], [INPHN2], [INPHN3], [INADN1], [INADN2], [INADN3], [INADN4], [INADN5], [INADN6], [INCTRM], [INDTRM], [INCLTR], [INCLOR], [INCLID], [INLATD], [INLONG], [JurisID], [RptDTTM], [DpDttm], [ArDTTM], [FinDttm], [Modifieddttm], [CreatedDttm], [IncidentIDRef]) VALUES (N'NJ0081000', N'200100000002', CAST(22102 AS Numeric(11, 0)), N'', CAST(0 AS Numeric(10, 0)), N' J', N' J', N' J', N'2202', N'2202', N' H15', N'', N'', N'', N'', N'', N'', N'FIRC', N'P', N'Y', N'3', N'', N'', N' 4', N'NJ0080000', N' JN1235', N'', N'', N'AHOLLAND', N' D', N' TEL', N'', N'', N'22-2', N'', N'', N'NJ0081000', N'200100000002', N'', N'', N'', N'', N'', CAST(0 AS Numeric(4, 0)), N'', CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010801 AS Numeric(8, 0)), CAST(93259 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(161607 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), N'200 MANTUA BLVD', N',,,', N' J', N'S', N'FIRC', N'P', N'3', CAST(20010813 AS Numeric(8, 0)), CAST(161607 AS Numeric(6, 0)), N'AHOLLAND', N'', CAST(0 AS Numeric(10, 0)), CAST(0 AS Numeric(10, 0)), N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(0.0000000 AS Numeric(10, 7)), CAST(0.0000000 AS Numeric(10, 7)), N'1510', CAST(0x000090EE009D5FE4 AS DateTime), CAST(0x0000000000000000 AS DateTime), NULL, NULL, NULL, NULL, N'200100000002')
INSERT [dbo].[PLINCD] ([INORI#], [ININC#], [INADR#], [INCOMN], [INPHON], [INAREA], [INSECT], [INBEAT], [INQUAD], [INDIST], [INGRID], [INUNT1], [INBDG1], [INBDG2], [INUNT2], [INBDG3], [INBDG4], [INITYP], [INTYPE], [INRPT], [INPRTY], [INPRIR], [INDESC], [INDOW], [INROR#], [INRID#], [INDOR#], [INDID#], [INOPER], [INSHFT], [INSORC], [INPSTS], [INBURE], [INSTTN], [INEXPG], [INMUTA], [INMORI], [INMINC], [INPCAS], [INFCAS], [INECAS], [INVLI#], [INVLIS], [INVLYR], [INVLIT], [INENTD], [INENTT], [INCALD], [INCALT], [INDSPD], [INDSPT], [INENRD], [INENRT], [INAR1D], [INAR1T], [INDP1D], [INDP1T], [INAR2D], [INAR2T], [INDP2D], [INDP2T], [ININSD], [ININST], [INCLRD], [INCLRT], [INDAT1], [INTIM1], [INDAT2], [INTIM2], [INDAT3], [INTIM3], [INOSTR], [INOXST], [INOVEN], [INOSTP], [INOITP], [INOITG], [INOPTY], [INLCD], [INLCT], [INLCU], [INDES2], [INPHN2], [INPHN3], [INADN1], [INADN2], [INADN3], [INADN4], [INADN5], [INADN6], [INCTRM], [INDTRM], [INCLTR], [INCLOR], [INCLID], [INLATD], [INLONG], [JurisID], [RptDTTM], [DpDttm], [ArDTTM], [FinDttm], [Modifieddttm], [CreatedDttm], [IncidentIDRef]) VALUES (N'NJ0081000', N'200100000003', CAST(1188 AS Numeric(11, 0)), N'K MART', CAST(0 AS Numeric(10, 0)), N' J', N' J', N' J', N'2207', N'2207', N' G13', N'', N'', N'', N'', N'', N'', N'FIRC', N'P', N'Y', N'3', N'', N'', N' 2', N'NJ0080000', N' JN1235', N'', N'', N'AHOLLAND', N' D', N' TEL', N'', N'', N'', N'', N'', N'NJ0081000', N'200100000003', N'', N'', N'', N'', N'', CAST(0 AS Numeric(4, 0)), N'', CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010806 AS Numeric(8, 0)), CAST(75848 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(161607 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), N'222 BRIDGETON PIKE', N',,,', N' J', N'S', N'FIRC', N'P', N'3', CAST(20010813 AS Numeric(8, 0)), CAST(161607 AS Numeric(6, 0)), N'AHOLLAND', N'', CAST(0 AS Numeric(10, 0)), CAST(0 AS Numeric(10, 0)), N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(0.0000000 AS Numeric(10, 7)), CAST(0.0000000 AS Numeric(10, 7)), N'1510', CAST(0x000090F3008381A0 AS DateTime), CAST(0x0000000000000000 AS DateTime), NULL, NULL, NULL, NULL, N'200100000003')
INSERT [dbo].[PLINCD] ([INORI#], [ININC#], [INADR#], [INCOMN], [INPHON], [INAREA], [INSECT], [INBEAT], [INQUAD], [INDIST], [INGRID], [INUNT1], [INBDG1], [INBDG2], [INUNT2], [INBDG3], [INBDG4], [INITYP], [INTYPE], [INRPT], [INPRTY], [INPRIR], [INDESC], [INDOW], [INROR#], [INRID#], [INDOR#], [INDID#], [INOPER], [INSHFT], [INSORC], [INPSTS], [INBURE], [INSTTN], [INEXPG], [INMUTA], [INMORI], [INMINC], [INPCAS], [INFCAS], [INECAS], [INVLI#], [INVLIS], [INVLYR], [INVLIT], [INENTD], [INENTT], [INCALD], [INCALT], [INDSPD], [INDSPT], [INENRD], [INENRT], [INAR1D], [INAR1T], [INDP1D], [INDP1T], [INAR2D], [INAR2T], [INDP2D], [INDP2T], [ININSD], [ININST], [INCLRD], [INCLRT], [INDAT1], [INTIM1], [INDAT2], [INTIM2], [INDAT3], [INTIM3], [INOSTR], [INOXST], [INOVEN], [INOSTP], [INOITP], [INOITG], [INOPTY], [INLCD], [INLCT], [INLCU], [INDES2], [INPHN2], [INPHN3], [INADN1], [INADN2], [INADN3], [INADN4], [INADN5], [INADN6], [INCTRM], [INDTRM], [INCLTR], [INCLOR], [INCLID], [INLATD], [INLONG], [JurisID], [RptDTTM], [DpDttm], [ArDTTM], [FinDttm], [Modifieddttm], [CreatedDttm], [IncidentIDRef]) VALUES (N'NJ0080100', N'200100000001', CAST(749990 AS Numeric(11, 0)), N'', CAST(0 AS Numeric(10, 0)), N' E', N' E', N' E', N'4314', N'4314', N'', N'A011', N'', N'', N'', N'', N'', N'CRMI', N'P', N'Y', N'3', N'', N'', N' 2', N'NJ0089999', N' WB2782', N'NJ0089999', N' WB2782', N'KROTHWEIN', N' D', N' TEL', N'', N'', N'43-4', N'', N' 2', N'NJ0080500', N'200100000001', N'', N'', N'', N'', N'', CAST(0 AS Numeric(4, 0)), N'', CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(171014 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(175540 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(175540 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(180145 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), N'1571 DELSEA DR', N',,,', N' E', N'S', N'CRMI', N'P', N'3', CAST(20010830 AS Numeric(8, 0)), CAST(181312 AS Numeric(6, 0)), N'KROTHWEIN', N'', CAST(0 AS Numeric(10, 0)), CAST(0 AS Numeric(10, 0)), N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(0.0000000 AS Numeric(10, 7)), CAST(0.0000000 AS Numeric(10, 7)), N'1501', CAST(0x000090FA011AF648 AS DateTime), CAST(0x000090FA012770D0 AS DateTime), NULL, NULL, NULL, NULL, N'200100000001')
INSERT [dbo].[PLINCD] ([INORI#], [ININC#], [INADR#], [INCOMN], [INPHON], [INAREA], [INSECT], [INBEAT], [INQUAD], [INDIST], [INGRID], [INUNT1], [INBDG1], [INBDG2], [INUNT2], [INBDG3], [INBDG4], [INITYP], [INTYPE], [INRPT], [INPRTY], [INPRIR], [INDESC], [INDOW], [INROR#], [INRID#], [INDOR#], [INDID#], [INOPER], [INSHFT], [INSORC], [INPSTS], [INBURE], [INSTTN], [INEXPG], [INMUTA], [INMORI], [INMINC], [INPCAS], [INFCAS], [INECAS], [INVLI#], [INVLIS], [INVLYR], [INVLIT], [INENTD], [INENTT], [INCALD], [INCALT], [INDSPD], [INDSPT], [INENRD], [INENRT], [INAR1D], [INAR1T], [INDP1D], [INDP1T], [INAR2D], [INAR2T], [INDP2D], [INDP2T], [ININSD], [ININST], [INCLRD], [INCLRT], [INDAT1], [INTIM1], [INDAT2], [INTIM2], [INDAT3], [INTIM3], [INOSTR], [INOXST], [INOVEN], [INOSTP], [INOITP], [INOITG], [INOPTY], [INLCD], [INLCT], [INLCU], [INDES2], [INPHN2], [INPHN3], [INADN1], [INADN2], [INADN3], [INADN4], [INADN5], [INADN6], [INCTRM], [INDTRM], [INCLTR], [INCLOR], [INCLID], [INLATD], [INLONG], [JurisID], [RptDTTM], [DpDttm], [ArDTTM], [FinDttm], [Modifieddttm], [CreatedDttm], [IncidentIDRef]) VALUES (N'NJ0080500', N'200100000001', CAST(749990 AS Numeric(11, 0)), N'', CAST(0 AS Numeric(10, 0)), N' E', N' E', N' E', N'4314', N'4314', N'', N'E009', N'', N'', N'', N'', N'', N'CRMI', N'P', N'Y', N'3', N'', N'', N' 2', N'NJ0089999', N' WB2782', N'NJ0089999', N' WB2782', N'WBRAMELL', N' D', N' TEL', N'', N'', N'43-4', N'', N' 1', N'NJ0080500', N'200100000001', N'', N'', N'', N'', N'', CAST(0 AS Numeric(4, 0)), N'', CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(171014 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(172212 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(172212 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(20010813 AS Numeric(8, 0)), CAST(181239 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), CAST(0 AS Numeric(8, 0)), CAST(0 AS Numeric(6, 0)), N'1571 DELSEA DR', N',,,', N' E', N'S', N'CRMI', N'P', N'3', CAST(20010813 AS Numeric(8, 0)), CAST(181239 AS Numeric(6, 0)), N'WBRAMELL', N'', CAST(0 AS Numeric(10, 0)), CAST(0 AS Numeric(10, 0)), N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', CAST(0.0000000 AS Numeric(10, 7)), CAST(0.0000000 AS Numeric(10, 7)), N'1505', CAST(0x000090FA011AF648 AS DateTime), CAST(0x000090FA011E3FB0 AS DateTime), NULL, NULL, NULL, NULL, N'200100000001')
/****** Object: Table [dbo].[SrcIncident] Script Date: 05/29/2012 18:34:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SrcIncident](
[JurisID] [char](4) NOT NULL,
[IncidentIDRef] [char](15) NOT NULL,
[CADNo] [bigint] NULL,
[CADNoRef] [char](15) NOT NULL,
[CaseNo] [bigint] NULL,
[CaseNoRef] [char](15) NULL,
[ReceiveSource] [char](8) NULL,
[OriginalCFS] [char](8) NOT NULL,
[OriginalCFSDesc] [char](100) NULL,
[CFSCode] [char](8) NULL,
[CFSCodeDesc] [varchar](100) NULL,
[CFSClass] [char](2) NULL,
[UCRCode] [varchar](8) NULL,
[MgmtCFSCode] [varchar](50) NULL,
[MgmtCFSDesc] [varchar](100) NULL,
[MgmtCFSClass] [char](2) NULL,
[RptDttm] [datetime] NULL,
[StkDttm] [datetime] NOT NULL,
[DpDttm] [datetime] NULL,
[ArDttm] [datetime] NULL,
[FinDttm] [datetime] NOT NULL,
[CtPFIDRef] [char](10) NULL,
[CtStationIDRef] [char](10) NULL,
[DpPFIDRef] [char](10) NULL,
[DpStationIDRef] [char](10) NULL,
[ArPFIDRef] [char](10) NULL,
[ArStationIDRef] [char](10) NULL,
[FinPFIDRef] [char](10) NULL,
[FinStationIDRef] [char](10) NULL,
[PrimeUnitIDRef] [char](8) NULL,
[ReportCode] [char](1) NULL,
[DPPri] [smallint] NULL,
[CADDisposition] [char](8) NOT NULL,
[CADDispositionDesc] [varchar](60) NULL,
[CaseDisposition] [char](8) NULL,
[CaseDispositionDesc] [varchar](60) NULL,
[PremiseType] [char](2) NULL,
[IsTraffic] [smallint] NULL,
[SolvFactor] [smallint] NULL,
[CallCount] [smallint] NULL,
[IsDomesticVio] [smallint] NULL,
[IsAbandonStr] [smallint] NULL,
[IsSafeEntered] [smallint] NULL,
[Instrument] [char](2) NULL,
[AlarmSys] [char](1) NULL,
[AlarmSys2] [char](1) NULL,
[Larceny] [char](1) NULL,
[Scene] [char](1) NULL,
[DamagedValue] [decimal](10, 2) NULL,
[OffKilled] [smallint] NULL,
[OffAssaulted] [smallint] NULL,
[VictimCount] [smallint] NULL,
[IsArson] [smallint] NULL,
[IsGamble] [smallint] NULL,
[ForceUsed] [char](2) NULL,
[CrimeAgainst] [char](1) NULL,
[IsAbandoned] [smallint] NULL,
[IsCompleted] [smallint] NULL,
[LocationType] [char](2) NULL,
[OccFromDttm] [datetime] NULL,
[OccToDttm] [datetime] NULL,
[AtDttm] [datetime] NULL,
[UcrDisposition] [char](1) NULL,
[UcrClearDttm] [datetime] NULL,
[NibrsDttm] [datetime] NULL,
[NibrsStatus] [char](1) NULL,
[CreatedPFIDRef] [char](10) NULL,
[CreatedDttm] [datetime] NULL,
[ModifiedPFIDRef] [char](10) NULL,
[ModifiedDttm] [datetime] NULL,
[Custom1] [varchar](255) NULL,
[Custom2] [varchar](255) NULL,
[IsImport] [smallint] NULL,
CONSTRAINT [SrcIncident_PK] PRIMARY KEY CLUSTERED
(
[JurisID] ASC,
[IncidentIDRef] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
declare SpSrcIncident Cursor Fast_Forward for
select distinct PnxCodeValue
from SrcCodeMap
join PLINCD on SrcCodeValueRef = INORI#
where CodeID = 100
declare @JurisID varchar(4),@Single varchar(max),@Count bigint = 0
open SpSrcIncident
fetch next from SpSrcIncident into @JurisID
while @@Fetch_Status = 0
begin
set @Single = char(39) + @JurisID + char(39)
insert into GloucMidPolice.dbo.SrcIncident
(
IncidentIDRef
, CADNoRef
, CADNo
, ReceiveSource
, OriginalCFS
, OriginalCFSDesc
, CFSCODE
, CFSCodeDesc
, RptDTTM
, StkDttm
, DpDttm
, ArDttm
, FinDttm
, CadDisposition
, ModifiedPFIDRef
, ModifiedDttm
, DpPFIDRef
, DpPri
, FinPFIDRef
, CtPFIDRef
, PrimeUnitIDRef
, JurisID
)
Exec('SELECT
i.IncidentIDRef
, i.ININC# CADNORef
, LEFT(i.ININC#,4) + RIGHT(i.ININC#,6) CADNO
, a.PnxCodeValue as insorc
, CASE WHEN i.INOITP = '''' OR i.INOITP IS NULL
THEN LTRIM(RTRIM(i.INITYP))
ELSE LTRIM(RTRIM(i.INOITP))
END OriginalCFS
, OrgCFS.ITDESC
, LTRIM(RTRIM(i.INITYP)) CFSCode
, CADCFS.ITDESC
, dbo.GetDateTime(incald, incalt) RptDttm
, dbo.GetDateTime(incald, incalt) stkdttm
, dbo.getdatetime(indspd, indspt) DpDttm
, dbo.getdatetime(inar1d, inar1t) ArDTTM
, dbo.getdatetime(inclrd, inclrt) FinDttm
, isnull(LTRIM(RTRIM(i.INRPT)),''cnv'') as CADDispo-- This is temporary. We update later from PLIDSP
, LTRIM(RTRIM(i.INLCU)) ModifiedPFIDRef -- Needs codemapping - stored as txt vs Badge#
, dbo.getdatetime(inlcd, inlct) Modifieddttm
, LTRIM(RTRIM(i.INDID#)) DpPFIDRef
, LTRIM(RTRIM(i.INPRTY)) DpPriority
, LTRIM(RTRIM(i.INBDG1)) FinPFIDRef -- Table Personnel Table PLOFCR.OMBDG#
, LTRIM(RTRIM(i.INRID#)) CTPFIDRef -- Table Personnel Table PLOFCR.OMBDG#
, LTRIM(RTRIM(i.INUNT1)) PrimeUnitIDRef
, sc.PnxCodeValue
--, create_ID, createddttm, mod_id,
FROM PLINCD i
JOIN SrcCodeMap sc
on LTRIM(RTRIM(i.INORI#)) = sc.SrcCodeValueRef and sc.CodeID = 100
LEFT OUTER JOIN PLITYP OrgCFS
ON CASE WHEN (i.INOITP = '''' OR i.INOITP IS NULL) THEN LTRIM(RTRIM(i.INITYP)) ELSE LTRIM(RTRIM(i.INOITP)) END = LTRIM(RTRIM(ORGCFS.ITITYP)) and ltrim(rtrim(ORGCFS.ITPFE)) = ''P''
left join SrcCodeMap A on A.SrcCodeValue = ltrim(rtrim(i.INSORC)) and A.CodeID = 1003 where sc.PnxCodeValue =' + @Single )
print cast (@JurisID as varchar)
fetch next from SpSrcIncident into @JurisID
set @Count = @Count + @@ROWCOUNT
end
print @Count
close SpSrcIncident
deallocate SpSrcIncident
Thanks,
Charmer
May 29, 2012 at 7:24 am
update top or doing an update in batches is the suggestion when you say an update for a billion rows takes too long/locks table.
to minimize that impact, you do multiple updates of smaller batches...but as Gail mentioned, to do that with UPDATE TOP , you have to be able to determine what was updates and what was not (via a WHERE statement) ie WHERE Destination.Column1 <> Source.Column1
if you cannot determine what was updated via A WHERE statement , my example is one other way to do it.
Lowell, i have posted my ddl here...
if i am wrong with what i am doing, please correct me...
Thanks,
Charmer
May 29, 2012 at 7:36 am
well the details are certainly different now that the DDL is posted.
the DDL details you posted is doing an Insert, not an update, and it's not joining on five tables like your pseudocode example is doing.
Double check and make sure you posted the cursor you want to tweak, please.
it still looks like you can do the work as a single insert, without the cursor, and it looks like there is a WHERE statement we can use as well.
since it's an INSERT, i don't think you need to worry about blocking, since noone would be selecting the new rows.
so now, is the issue you want to get rid of the cursor, or is the issue "need to update in batches to minimize the impact"
Lowell
May 29, 2012 at 7:46 am
Lowell (5/29/2012)
well the details are certainly different now that the DDL is posted.the DDL details you posted is doing an Insert, not an update, and it's not joining on five tables like your pseudocode example is doing.
Double check and make sure you posted the cursor you want to tweak, please.
it still looks like you can do the work as a single insert, without the cursor, and it looks like there is a WHERE statement we can use as well.
since it's an INSERT, i don't think you need to worry about blocking, since noone would be selecting the new rows.
so now, is the issue you want to get rid of the cursor, or is the issue "need to update in batches to minimize the impact"
declare Sp_CADDisposition Cursor for
select distinct s.JurisID from GloucMidPolice.dbo.SrcIncident s
declare @Juris10 varchar(4),@Single9 varchar(max),@Count bigint = 0
open Sp_CADDisposition
fetch next from Sp_CADDisposition into @Juris10
while @@Fetch_Status = 0
begin
set @Single9 = char(39) + @Juris10 + char(39)
exec('update GloucMidPolice.dbo.SrcIncident
set CADDisposition = ltrim(rtrim(iddtyp))
from
PLINCD i
join SrcCodeMap sc
on ltrim(rtrim(INORI#)) = LTRIM(RTRIM(sc.SrcCodeValueRef)) and sc.CodeID= 100
left outer join PLIDSP id
on isnull(ltrim(rtrim(INUNT1)), '' '') = isnull(ltrim(rtrim(IDUNIT)), '' '') and ltrim(rtrim(i.ININC#)) = LTRIM(rtrim(idinc#))
join GloucMidPolice.dbo.SrcIncident si
on ltrim(rtrim(i.ININC#)) = si.IncidentIDRef and sc.PnxCodeValue = si.JurisID
where not ltrim(rtrim(IDDTYP)) is null and si.JurisID =' +@Single9)
set @Count = @Count + @@ROWCOUNT
fetch next from Sp_CADDisposition into @Juris10
end
print @Count
close Sp_CADDisposition
deallocate Sp_CADDisposition
Actually i am doing cursor for so many queries....that is one of them....insert as well as update...i here posted update command with three joins...
while doing for all records, the server was crashed and tempdb was affected...
i thought of doing batch wise...that's why i came to cursor...i know there would be a better way... so i came here for SQL guru's...
Thanks,
Charmer
May 29, 2012 at 7:47 am
well it looks like it can be done without the cursor and also without the dynamic SQL;
this SELECT is the section that would be doing the INSERT;
how many rows does this return?
i get an error about a missing reference, so i'm not sure we have everything we need to test with:
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "CADCFS.ITDESC" could not be bound.
SELECT
i.IncidentIDRef
, i.ININC# CADNORef
, LEFT(i.ININC#,4) + RIGHT(i.ININC#,6) CADNO
, a.PnxCodeValue AS insorc
, CASE
WHEN i.INOITP = '' OR i.INOITP IS NULL
THEN LTRIM(RTRIM(i.INITYP))
ELSE LTRIM(RTRIM(i.INOITP))
END OriginalCFS
, OrgCFS.ITDESC
, LTRIM(RTRIM(i.INITYP)) CFSCode
, CADCFS.ITDESC
, dbo.GetDateTime(incald, incalt) RptDttm
, dbo.GetDateTime(incald, incalt) stkdttm
, dbo.getdatetime(indspd, indspt) DpDttm
, dbo.getdatetime(inar1d, inar1t) ArDTTM
, dbo.getdatetime(inclrd, inclrt) FinDttm
, ISNULL(LTRIM(RTRIM(i.INRPT)),'cnv') AS CADDispo-- This is temporary. We update later from PLIDSP
, LTRIM(RTRIM(i.INLCU)) ModifiedPFIDRef -- Needs codemapping - stored as txt vs Badge#
, dbo.getdatetime(inlcd, inlct) Modifieddttm
, LTRIM(RTRIM(i.INDID#)) DpPFIDRef
, LTRIM(RTRIM(i.INPRTY)) DpPriority
, LTRIM(RTRIM(i.INBDG1)) FinPFIDRef -- Table Personnel Table PLOFCR.OMBDG#
, LTRIM(RTRIM(i.INRID#)) CTPFIDRef -- Table Personnel Table PLOFCR.OMBDG#
, LTRIM(RTRIM(i.INUNT1)) PrimeUnitIDRef
, sc.PnxCodeValue
--, create_ID, createddttm, mod_id,
FROM PLINCD i
INNER JOIN SrcCodeMap sc
ON LTRIM(RTRIM(i.INORI#)) = sc.SrcCodeValueRef AND sc.CodeID = 100
LEFT OUTER JOIN PLITYP OrgCFS
ON CASE
WHEN (i.INOITP = '' OR i.INOITP IS NULL)
THEN LTRIM(RTRIM(i.INITYP))
ELSE LTRIM(RTRIM(i.INOITP))
END = LTRIM(RTRIM(ORGCFS.ITITYP))
AND LTRIM(RTRIM(ORGCFS.ITPFE)) = 'P'
LEFT JOIN SrcCodeMap A
ON A.SrcCodeValue = LTRIM(RTRIM(i.INSORC))
AND A.CodeID = 1003
WHERE sc.PnxCodeValue IN (SELECT
PnxCodeValue
FROM SrcCodeMap
INNER JOIN PLINCD ON SrcCodeValueRef = INORI#
WHERE CodeID = 100
GROUP BY PnxCodeValue
)
Lowell
May 29, 2012 at 8:18 am
Lowell (5/29/2012)
well it looks like it can be done without the cursor and also without the dynamic SQL;this SELECT is the section that would be doing the INSERT;
how many rows does this return?
i get an error about a missing reference, so i'm not sure we have everything we need to test with:
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "CADCFS.ITDESC" could not be bound.
well it returns 4 and half million records...
Lowell, the query you gave is a batch wise operation ? i am just curious, thats why asking...
Thanks,
Charmer
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply