InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 13:16:00 GMT20http://www.sqlservercentral.com/Forums/Topic1381156-392-1.aspxWow that table needs some help. If at all possible you should consider changing that table. You should be storing datetime data in datetime instead of breaking apart all the pieces across various nvarchar columns. Then there is the normalization. Of course from some of the description this may be a holding table for importing from an external file prior to being imported and massaged into usable data. If that is the case then disregard my rant.I appreciate the table ddl. But the insert is useless. It looks like it from right clicking the table in SSMS and creating the insert script. I have never really understood the point of that "feature" because it creates completely useless information. The best way to post this is to just create an insert statement.[code]insert table select col1, col2, col3 union allselect col1, col2, col3...[/code]Also if you use the IFCode shortcuts (on the left side when posting) you can wrap your code in the code tags and it will make it a lot cleaner. (like my code above)Thu, 08 Nov 2012 10:31:14 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1381156-392-1.aspxWhen 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.[u]Here is my create [/u]USE [SLADB]GO/****** Object: Table [dbo].[ProdDataTB] Script Date: 11/08/2012 16:25:25 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GO[u]Insert into[/u]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),>)GO[u]Sample data[/u]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 5457Can anybody please recomend a fast and effective way of getting the above into my table ?JayThu, 08 Nov 2012 09:27:37 GMTjerome.morrishttp://www.sqlservercentral.com/Forums/Topic1381156-392-1.aspx[quote][b]jerome.morris (11/5/2012)[/b][hr]The amount off calls raiesed by an operator, so I thought counting the ID would give me this which it does, but I want the operator with the most calls at the top going down to the 20th worst offender is that makes senseJ[/quote]With no real details the best I can do is throw together some generic sql.[code];with cte as( select Cols, COUNT(*) as CallCount From SomeTable Group By Cols)select top 20 * from cte order by CallCount [/code]Mon, 05 Nov 2012 09:50:08 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1381156-392-1.aspxThe amount off calls raiesed by an operator, so I thought counting the ID would give me this which it does, but I want the operator with the most calls at the top going down to the 20th worst offender is that makes senseJMon, 05 Nov 2012 09:36:00 GMTjerome.morrishttp://www.sqlservercentral.com/Forums/Topic1381156-392-1.aspx[quote][b]jerome.morris (11/5/2012)[/b][hr]Hi all, I was wondering if this is possible, I use the query below which is working great if be it a little slow as table is huge. I only want to return the top 20 worst offending Operator_Names from the queryHow do I do this, do I use count ?SELECT Operator_Name, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_EngineerName, Docket_Category, Docket_SubCategory, Duration, Module, Section, Waittime, Monitor_Time, spare8, Docket_EngStart, Docket_EngFinish, Docket_DateRaised, Docket_Date, ContractFROM DocketTBWHERE (Docket_Status = 'CL') AND (Contract = '1') AND (Operator_Name IS NOT NULL) AND (Operator_Name NOT LIKE 'None')[/quote]The easiest way is to use ROW_NUMBER. You need to figure out what makes the top 20. I don't see an order by in your query but that is what will define the "top 20".Mon, 05 Nov 2012 09:31:28 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1381156-392-1.aspxHi all, I was wondering if this is possible, I use the query below which is working great if be it a little slow as table is huge. I only want to return the top 20 worst offending Operator_Names from the queryHow do I do this, do I use count ?SELECT Operator_Name, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_EngineerName, Docket_Category, Docket_SubCategory, Duration, Module, Section, Waittime, Monitor_Time, spare8, Docket_EngStart, Docket_EngFinish, Docket_DateRaised, Docket_Date, ContractFROM DocketTBWHERE (Docket_Status = 'CL') AND (Contract = '1') AND (Operator_Name IS NOT NULL) AND (Operator_Name NOT LIKE 'None')Mon, 05 Nov 2012 09:24:05 GMTjerome.morris