RE: Top 20 count – SQLServerCentral

SSCrazy

Points: 2829

November 8, 2012 at 9:27 am

When everything else fails I will ask you guys. This has been annoying me for weeks now and although I can get my c# code to complete the task at hand, the other system I need to tackle are not running Win 64bit so I am always faced with the dreaded out of memory problem.

I have a large Fixed width text file (226mb min) and around 97 columns of data. I need to get all this data into my SQL DB table.

Here is my create

USE [SLADB]

/****** Object: Table [dbo].[ProdDataTB] Script Date: 11/08/2012 16:25:25 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[ProdDataTB](

[MachineName] [nvarchar](10) NULL,

[ModeName] [nvarchar](10) NULL,

[FileName] [nvarchar](10) NULL,

[JobName] [nvarchar](10) NULL,

[UserName] [nvarchar](10) NULL,

[TransactionKey] [nvarchar](10) NULL,

[StartDate_Time] [nvarchar](25) NULL,

[StartDate] [nvarchar](10) NULL,

[StartTime] [time](7) NULL,

[StartYYYY] [nvarchar](4) NULL,

[StartMM] [nvarchar](2) NULL,

[StartDD] [nvarchar](2) NULL,

[StartHH] [nvarchar](2) NULL,

[StartNN] [nvarchar](2) NULL,

[StartSS] [nvarchar](2) NULL,

[Spare1] [nvarchar](6) NULL,

[EndDate_Time] [nvarchar](30) NULL,

[EndDate] [nvarchar](10) NULL,

[EndTime] [time](7) NULL,

[EndYYYY] [nvarchar](4) NULL,

[EndMM] [nvarchar](2) NULL,

[EndDD] [nvarchar](2) NULL,

[EndHH] [nvarchar](2) NULL,

[EndNN] [nvarchar](2) NULL,

[EndSS] [nvarchar](2) NULL,

[Spare2] [nvarchar](6) NULL,

[RunTime] [float] NULL,

[DelayTime] [float] NULL,

[MachineStopTime] [float] NULL,

[OperatorStopTime] [float] NULL,

[MachineFaultTime] [float] NULL,

[OperatorFaultTime] [float] NULL,

[OldPiecesFed] [float] NULL,

[OldPiecesCompleted] [float] NULL,

[NumMachineStops] [float] NULL,

[NumOperatorStops] [float] NULL,

[NumDelays] [float] NULL,

[Feeder_00_Count] [float] NULL,

[Feeder_01_Count] [float] NULL,

[Feeder_02_Count] [float] NULL,

[Feeder_03_Count] [float] NULL,

[Feeder_04_Count] [float] NULL,

[Feeder_05_Count] [float] NULL,

[Feeder_06_Count] [float] NULL,

[Feeder_07_Count] [float] NULL,

[Feeder_08_Count] [float] NULL,

[Feeder_09_Count] [float] NULL,

[Feeder_10_Count] [float] NULL,

[Feeder_11_Count] [float] NULL,

[Feeder_12_Count] [float] NULL,

[Feeder_13_Count] [float] NULL,

[Feeder_14_Count] [float] NULL,

[Feeder_15_Count] [float] NULL,

[Feeder_16_Count] [float] NULL,

[Feeder_17_Count] [float] NULL,

[Input_Feeder_Count] [float] NULL,

[Input_SubFdr_1_Count] [float] NULL,

[Input_SubFdr_2_Count] [float] NULL,

[Input_SubFdr_3_Count] [float] NULL,

[Input_SubFdr_4_Count] [float] NULL,

[Input_SubFdr_5_Count] [float] NULL,

[Avg_Chassis_Speed] [float] NULL,

[Shift] [nvarchar](5) NULL,

[Total_Pcs_Outsorted] [float] NULL,

[Total_Pcs_Outsorted_Good] [float] NULL,

[Total_Pcs_Outsorted_Maybe] [float] NULL,

[Total_Pcs_Outsorted_Bad] [float] NULL,

[Total_Pcs_Outsorted_Unk] [float] NULL,

[Bin_01] [float] NULL,

[Bin_02] [float] NULL,

[Bin_03] [float] NULL,

[Bin_04] [float] NULL,

[Bin_05] [float] NULL,

[Bin_06] [float] NULL,

[Bin_07] [float] NULL,

[Bin_08] [float] NULL,

[IST_Bin1] [float] NULL,

[IST_Bin2] [float] NULL,

[IST_Bin3] [float] NULL,

[IST_Bin4] [float] NULL,

[IST_RunOut] [float] NULL,

[Mtr1_NoPrint] [float] NULL,

[Mtr2_NoPrint] [float] NULL,

[Mtr3_NoPrint] [float] NULL,

[Mtr4_NoPrint] [float] NULL,

[Edge_Mark1] [float] NULL,

[Edge_Mark2] [float] NULL,

[Edge_Mark3] [float] NULL,

[No_Seal] [float] NULL,

[Empty_Cycles] [float] NULL,

[Filled_Cycles] [float] NULL,

[MidRunTime] [nvarchar](8) NULL,

[SubShiftExt] [nvarchar](2) NULL,

[ShiftDateAdjust] [nvarchar](10) NULL,

[PiecesFed] [float] NOT NULL,

[PiecesCompleted] [float] NULL,

[Spare3] [nvarchar](68) NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

CONSTRAINT [PK_ProdDataTB] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Insert into

INSERT INTO [SLADB].[dbo].[ProdDataTB]

([MachineName]

,[ModeName]

,[FileName]

,[JobName]

,[UserName]

,[TransactionKey]

,[StartDate_Time]

,[StartDate]

,[StartTime]

,[StartYYYY]

,[StartMM]

,[StartDD]

,[StartHH]

,[StartNN]

,[StartSS]

,[Spare1]

,[EndDate_Time]

,[EndDate]

,[EndTime]

,[EndYYYY]

,[EndMM]

,[EndDD]

,[EndHH]

,[EndNN]

,[EndSS]

,[Spare2]

,[RunTime]

,[DelayTime]

,[MachineStopTime]

,[OperatorStopTime]

,[MachineFaultTime]

,[OperatorFaultTime]

,[OldPiecesFed]

,[OldPiecesCompleted]

,[NumMachineStops]

,[NumOperatorStops]

,[NumDelays]

,[Feeder_00_Count]

,[Feeder_01_Count]

,[Feeder_02_Count]

,[Feeder_03_Count]

,[Feeder_04_Count]

,[Feeder_05_Count]

,[Feeder_06_Count]

,[Feeder_07_Count]

,[Feeder_08_Count]

,[Feeder_09_Count]

,[Feeder_10_Count]

,[Feeder_11_Count]

,[Feeder_12_Count]

,[Feeder_13_Count]

,[Feeder_14_Count]

,[Feeder_15_Count]

,[Feeder_16_Count]

,[Feeder_17_Count]

,[Input_Feeder_Count]

,[Input_SubFdr_1_Count]

,[Input_SubFdr_2_Count]

,[Input_SubFdr_3_Count]

,[Input_SubFdr_4_Count]

,[Input_SubFdr_5_Count]

,[Avg_Chassis_Speed]

,[Shift]

,[Total_Pcs_Outsorted]

,[Total_Pcs_Outsorted_Good]

,[Total_Pcs_Outsorted_Maybe]

,[Total_Pcs_Outsorted_Bad]

,[Total_Pcs_Outsorted_Unk]

,[Bin_01]

,[Bin_02]

,[Bin_03]

,[Bin_04]

,[Bin_05]

,[Bin_06]

,[Bin_07]

,[Bin_08]

,[IST_Bin1]

,[IST_Bin2]

,[IST_Bin3]

,[IST_Bin4]

,[IST_RunOut]

,[Mtr1_NoPrint]

,[Mtr2_NoPrint]

,[Mtr3_NoPrint]

,[Mtr4_NoPrint]

,[Edge_Mark1]

,[Edge_Mark2]

,[Edge_Mark3]

,[No_Seal]

,[Empty_Cycles]

,[Filled_Cycles]

,[MidRunTime]

,[SubShiftExt]

,[ShiftDateAdjust]

,[PiecesFed]

,[PiecesCompleted]

,[Spare3])

VALUES

(<MachineName, nvarchar(10),>

,<ModeName, nvarchar(10),>

,<FileName, nvarchar(10),>

,<JobName, nvarchar(10),>

,<UserName, nvarchar(10),>

,<TransactionKey, nvarchar(10),>

,<StartDate_Time, nvarchar(25),>

,<StartDate, nvarchar(10),>

,<StartTime, time(7),>

,<StartYYYY, nvarchar(4),>

,<StartMM, nvarchar(2),>

,<StartDD, nvarchar(2),>

,<StartHH, nvarchar(2),>

,<StartNN, nvarchar(2),>

,<StartSS, nvarchar(2),>

,<Spare1, nvarchar(6),>

,<EndDate_Time, nvarchar(30),>

,<EndDate, nvarchar(10),>

,<EndTime, time(7),>

,<EndYYYY, nvarchar(4),>

,<EndMM, nvarchar(2),>

,<EndDD, nvarchar(2),>

,<EndHH, nvarchar(2),>

,<EndNN, nvarchar(2),>

,<EndSS, nvarchar(2),>

,<Spare2, nvarchar(6),>

,<RunTime, float,>

,<DelayTime, float,>

,<MachineStopTime, float,>

,<OperatorStopTime, float,>

,<MachineFaultTime, float,>

,<OperatorFaultTime, float,>

,<OldPiecesFed, float,>

,<OldPiecesCompleted, float,>

,<NumMachineStops, float,>

,<NumOperatorStops, float,>

,<NumDelays, float,>

,<Feeder_00_Count, float,>

,<Feeder_01_Count, float,>

,<Feeder_02_Count, float,>

,<Feeder_03_Count, float,>

,<Feeder_04_Count, float,>

,<Feeder_05_Count, float,>

,<Feeder_06_Count, float,>

,<Feeder_07_Count, float,>

,<Feeder_08_Count, float,>

,<Feeder_09_Count, float,>

,<Feeder_10_Count, float,>

,<Feeder_11_Count, float,>

,<Feeder_12_Count, float,>

,<Feeder_13_Count, float,>

,<Feeder_14_Count, float,>

,<Feeder_15_Count, float,>

,<Feeder_16_Count, float,>

,<Feeder_17_Count, float,>

,<Input_Feeder_Count, float,>

,<Input_SubFdr_1_Count, float,>

,<Input_SubFdr_2_Count, float,>

,<Input_SubFdr_3_Count, float,>

,<Input_SubFdr_4_Count, float,>

,<Input_SubFdr_5_Count, float,>

,<Avg_Chassis_Speed, float,>

,<Shift, nvarchar(5),>

,<Total_Pcs_Outsorted, float,>

,<Total_Pcs_Outsorted_Good, float,>

,<Total_Pcs_Outsorted_Maybe, float,>

,<Total_Pcs_Outsorted_Bad, float,>

,<Total_Pcs_Outsorted_Unk, float,>

,<Bin_01, float,>

,<Bin_02, float,>

,<Bin_03, float,>

,<Bin_04, float,>

,<Bin_05, float,>

,<Bin_06, float,>

,<Bin_07, float,>

,<Bin_08, float,>

,<IST_Bin1, float,>

,<IST_Bin2, float,>

,<IST_Bin3, float,>

,<IST_Bin4, float,>

,<IST_RunOut, float,>

,<Mtr1_NoPrint, float,>

,<Mtr2_NoPrint, float,>

,<Mtr3_NoPrint, float,>

,<Mtr4_NoPrint, float,>

,<Edge_Mark1, float,>

,<Edge_Mark2, float,>

,<Edge_Mark3, float,>

,<No_Seal, float,>

,<Empty_Cycles, float,>

,<Filled_Cycles, float,>

,<MidRunTime, nvarchar(8),>

,<SubShiftExt, nvarchar(2),>

,<ShiftDateAdjust, nvarchar(10),>

,<PiecesFed, float,>

,<PiecesCompleted, float,>

,<Spare3, nvarchar(68),>)

Sample data

APS08 C5_LAKE_WE08075_002_0807500236ADAMSAG 26013Sun Jan 01 07:21:59 2012 01/01/201207:21:59 20120101072159 Sun Jan 01 07:30:42 2012 01/01/201207:30:42 20120101073042 196 27 88 212 88 212 498 483 3 0 800000487000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001147000011470000000000000000000000000000000013463 2 0 0 2 0 0 2 0 0 0 488 245 498 483

APS05 C5_LAKE_WE08066_005_0806600536HUGHEPE 28708Sun Jan 01 07:08:38 2012 01/01/201207:08:38 20120101070838 Sun Jan 01 07:47:25 2012 01/01/201207:47:25 20120101074725 1467 45 295 520 295 520 5476 5457 12 2 1500005467000000000000000000000000000000000000000000000109000042480000000000000000000000000000000000000000000000000000000000000000000000000000000000009894000000000000989400000000000000000000000014090 18 0 0 14 4 11 2 0 0 5 5467 275 5476 5457

Can anybody please recomend a fast and effective way of getting the above into my table ?

Jay