Insert Into Temporary Table With Order By Clause

1

Hi

As per the title, I need to insert records into a temporary table in a specific order. I have a pretty complex query that inserts into a temporary table and then claculates time differences between each record. For this to work, the records need to be inserted in the correct order. I originally had this working for a while, as although SQL doesn't appear to accept the syntax of 'order by' in an insert statement, the records which are collected for the insert statement are in the correct order already. However, I recently had a contractor set up some indexing for me. Since this has been done, the records are no longer inserted in the correct order, resulting in incorrect calculations. Is there a way to insert into my temporary table with an order by clause?

Thanks

Steve

2

Code as follows...

DECLARE @Start datetime DECLARE @End datetime DECLARE @Movements table (MID int IDENTITY, Mproduct varchar(MAX), MPalletRef varchar(MAX), MLocation varchar(50), MAction varchar(MAX), MPickType varchar(50), MTimestamp datetime, MUsername varchar(50)) DECLARE @Shifts table (SID int IDENTITY, SUsername varchar(MAX), SDate date, SShift varchar(20), SShiftStart datetime, SShiftEnd datetime, SMovements int) DECLARE @ShiftData table (ID int IDENTITY, Product varchar(MAX), PalletRef varchar(50), Location varchar (50), Action varchar(MAX), PickType varchar(MAX), Timestamp datetime, Duration time, Consecutive int) DECLARE @CompiledData table (Username varchar(MAX), Site varchar(50), Date date, Shift varchar(50), Average varchar(10), Consecutive int, StandardRetrieval int, FreeRetrieval int, RDTRetrieval int, PutAway int, TotalTime varchar(10), RDTNonUsage decimal(6,1), TotalSeconds int, AverageSeconds int) DECLARE @Summary table (SUMUsername varchar(MAX), Site varchar(50), Shift varchar(50), ShiftsWorked int, Average varchar(10), Consecutive int, StandardRetrieval int, FreeRetrieval int, RDTRetrieval int, PutAway int, TotalTime varchar(10), RDTNonUsage decimal(6,1)) DECLARE @Date datetime DECLARE @Counter int DECLARE @ShiftRecord int SET @Start=@StartDate+'06:00' SET @End=DATEADD(DAY,1,@EndDate)+ '06:44:59:999' INSERT INTO @Movements (Mproduct, MPalletRef, MLocation, MAction, MPickType, MTimestamp, MUsername) (SELECT (SELECT Code + ' ' + Item FROM Products WHERE Product_id=(SELECT Product_id FROM Batch_Link WHERE Batch_id=Audit_Pallets.Batch_id))AS Product, PalletRef, (SELECT Location FROM Locations WHERE Location_id=Audit_Pallets.Location)AS Location, CASE WHEN Action='Pallet Relocated' THEN 'Put Away' ELSE 'Retrieve' END, CASE WHEN Action='Pallet Relocated' THEN 'Put Away' WHEN Action='Pallet Picked (RDT)' THEN 'Retrieve (RDT)' WHEN Action='Pallet Picked (SP)' THEN 'Retrieve (SP)' WHEN Action='Pallet Picked (FP)' THEN 'Retrieve (FP)' ELSE Action END, Timestamp, Username FROM Audit_Pallets WHERE Username IN (SELECT Name FROM ApplicationAuthentication.dbo.aspnet_Users WHERE Shift IS NOT NULL AND UserId IN (SELECT UserId FROM ApplicationAuthentication.dbo.aspnet_Membership WHERE IsApproved='1')) AND TimeStamp BETWEEN @Start AND @End) SET @Date=CONVERT(Date,@Start) WHILE @Date<=@End BEGIN INSERT INTO @Shifts (SUsername, SDate, SShift, SShiftStart, SShiftEnd) (SELECT DISTINCT Name, @Date, Shift, CASE WHEN Shift='AM' THEN CONVERT(datetime,@Date)+'05:45' WHEN Shift='PM' THEN CONVERT(datetime,@Date)+'17:45' WHEN Shift='Morning' THEN CONVERT(datetime,@Date)+'05:45' WHEN Shift='Afternoon' THEN CONVERT(datetime,@Date)+'13:45' WHEN Shift='Night' THEN CONVERT(datetime,@Date)+'21:45' END, CASE WHEN Shift='AM' THEN CONVERT(datetime,@Date)+'18:15' WHEN Shift='PM' THEN DATEADD(Day,1,CONVERT(datetime,@Date))+'06:15' WHEN Shift='Morning' THEN CONVERT(datetime,@Date)+'14:45' WHEN Shift='Afternoon' THEN CONVERT(datetime,@Date)+'22:45' WHEN Shift='Night' THEN DATEADD(Day,1,CONVERT(datetime,@Date))+'06:45' END FROM ApplicationAuthentication.dbo.aspnet_Users WHERE Name IN (SELECT MUsername FROM @Movements)) SET @Date=DATEADD(Day,1,@Date) END UPDATE @Shifts SET SMovements= CASE WHEN SShift='AM' THEN (SELECT COUNT(MTimestamp)FROM @Movements WHERE MUsername=SUsername AND MTimestamp BETWEEN CONVERT(datetime,SDate)+'06:00' AND CONVERT(datetime,SDate)+'18:15') WHEN SShift='PM' THEN (SELECT COUNT(MTimestamp)FROM @Movements WHERE MUsername=SUsername AND MTimestamp BETWEEN CONVERT(datetime,SDate)+'18:00' AND DATEADD(Day,1,CONVERT(datetime,SDate))+'06:15') WHEN SShift='Morning' THEN (SELECT COUNT(MTimestamp)FROM @Movements WHERE MUsername=SUsername AND MTimestamp BETWEEN CONVERT(datetime,SDate)+'06:00' AND CONVERT(datetime,SDate)+'14:45') WHEN SShift='Afternoon' THEN (SELECT COUNT(MTimestamp)FROM @Movements WHERE MUsername=SUsername AND MTimestamp BETWEEN CONVERT(datetime,SDate)+'14:00' AND CONVERT(datetime,SDate)+'22:45') WHEN SShift='Night' THEN (SELECT COUNT(MTimestamp)FROM @Movements WHERE MUsername=SUsername AND MTimestamp BETWEEN CONVERT(datetime,SDate)+'22:00' AND DATEADD(Day,1,CONVERT(datetime,SDate))+'06:45') END DELETE FROM @Shifts WHERE SMovements<20 SET @ShiftRecord=(SELECT MIN(SID)FROM @Shifts) WHILE @ShiftRecord IS NOT NULL BEGIN INSERT INTO @ShiftData ( Product, PalletRef, Location, Action, PickType, Timestamp) (SELECT Mproduct, MPalletRef, MLocation, MAction, MPickType, MTimestamp FROM @Movements WHERE MUsername=(SELECT SUsername FROM @Shifts WHERE SID=@ShiftRecord) AND MTimestamp BETWEEN (SELECT SShiftStart FROM @Shifts WHERE SID=@ShiftRecord) AND (SELECT SShiftEnd FROM @Shifts WHERE SID=@ShiftRecord)) SET @Counter=(SELECT COUNT(MID)FROM @Movements) WHILE @Counter>0 BEGIN UPDATE @ShiftData SET Duration=ISNULL((SELECT Timestamp FROM @ShiftData WHERE ID=@Counter+1)-Timestamp,'00:03:00'), Consecutive= CASE WHEN (SELECT Action FROM @ShiftData WHERE ID=@Counter-1)=Action THEN '1' ELSE '0' END WHERE ID=@Counter SET @Counter=@Counter-1 END INSERT INTO @CompiledData (Username, Site, Date, Shift, Average, Consecutive, StandardRetrieval, FreeRetrieval, RDTRetrieval, PutAway, TotalTime, RDTNonUsage, TotalSeconds, AverageSeconds) (SELECT (SELECT SUsername FROM @Shifts WHERE SID=@ShiftRecord), (SELECT Site FROM ApplicationAuthentication.dbo.aspnet_Users WHERE Name=(SELECT SUsername FROM @Shifts WHERE SID=@ShiftRecord)), (SELECT SDate FROM @Shifts WHERE SID=@ShiftRecord), (SELECT SShift FROM @Shifts WHERE SID=@ShiftRecord), SUBSTRING(CONVERT(varchar(50),(CAST(CAST(AVG(CAST(CAST(Duration AS datetime)AS FLOAT))AS datetime)AS TIME)),13),1,8)AS Average, (SELECT SUM(Consecutive))AS Consecutive, (SELECT COUNT(Action) FROM @ShiftData WHERE PickType='Retrieve (SP)')AS StandardRetrieval, (SELECT COUNT(Action) FROM @ShiftData WHERE PickType='Retrieve (FP)')AS FreeRetrieval, (SELECT COUNT(Action) FROM @ShiftData WHERE PickType='Retrieve (RDT)')AS RDTRetrieval, (SELECT COUNT(Action) FROM @ShiftData WHERE PickType='Put Away')AS Putaway, SUBSTRING(CONVERT(varchar(50),(CAST(CAST(SUM(CAST(CAST(Duration AS datetime)AS FLOAT))AS datetime)AS TIME)),13),1,8)AS TotalTime, CAST((CAST(SUM(Consecutive)AS decimal(6,1)))/(COUNT(Action))*100 AS decimal(6,1))AS RDTNonUsage, CONVERT(INT, DATEDIFF(SECOND, '19000101',SUBSTRING(CONVERT(varchar(50),(CAST(CAST(SUM(CAST(CAST(Duration AS datetime)AS FLOAT))AS datetime)AS TIME)),13),1,8))), CONVERT(INT, DATEDIFF(SECOND, '19000101',SUBSTRING(CONVERT(varchar(50),(CAST(CAST(AVG(CAST(CAST(Duration AS datetime)AS FLOAT))AS datetime)AS TIME)),13),1,8))) FROM @ShiftData) DELETE FROM @ShiftData DELETE FROM @Shifts WHERE SID=@ShiftRecord SET @ShiftRecord=(SELECT MIN(SID)FROM @Shifts) END INSERT INTO @Summary (SUMUsername, Site, Shift) (SELECT DISTINCT Username, Site, Shift FROM @CompiledData) UPDATE @Summary SET ShiftsWorked=(SELECT COUNT(Username)FROM @CompiledData WHERE Username=SUMUsername), StandardRetrieval=(SELECT SUM(StandardRetrieval)FROM @CompiledData WHERE Username=SUMUsername), FreeRetrieval=(SELECT SUM(FreeRetrieval)FROM @CompiledData WHERE Username=SUMUsername), RDTRetrieval=(SELECT SUM(RDTRetrieval)FROM @CompiledData WHERE Username=SUMUsername), PutAway=(SELECT SUM(PutAway)FROM @CompiledData WHERE Username=SUMUsername), TotalTime=CONVERT(varchar(6), (SELECT SUM(TotalSeconds)FROM @CompiledData WHERE Username=SUMUsername)/3600) + ':'+ RIGHT('0' + CONVERT(varchar(2), ((SELECT SUM(TotalSeconds)FROM @CompiledData WHERE Username=SUMUsername) % 3600) / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (SELECT SUM(TotalSeconds)FROM @CompiledData WHERE Username=SUMUsername) % 60), 2), Average=CONVERT(CHAR(8), DATEADD(SECOND,(SELECT AVG(AverageSeconds)FROM @CompiledData WHERE Username=SUMUsername),''),114), Consecutive=(SELECT SUM(Consecutive)FROM @CompiledData WHERE Username=SUMUsername), RDTNonUsage =(SELECT SUM(RDTNonUsage)FROM @CompiledData WHERE Username=SUMUsername)/(SELECT COUNT(RDTNonUsage)FROM @CompiledData WHERE Username=SUMUsername) SELECT * FROM @Summary ORDER BY SUMUsername

3

First SQL Server does not save in any order. You can use a ORDER BY on your select for INSERT INTO but the order is determined by indexes. What I would suggest is when creating an ordered temporary table is to use an identity or row_number. For that mater ROW_NUMBER might work without a temporary table.

4

Hi
Thanks for the reply. Sorry, but I'm not sure I understand. Even if I use an id column on the temporary table, the records will still be in the wrong order because I can't specify how to order them on insert. Could you show me some example code please? Much appreciated.
Steve

5

You can't order rows on INSERT in SQL. AS djj55 said, rows are inserted in the order of the clustered index, if there is one. Otherwise they are inserted wherever SQL likes and in no particular order. You can control ordering on SELECT of course, which is what the ORDER BY clause is for. But, not on INSERT.

6

Thanks for clearing this up, but this doesn't resolve my issue. In order for my query to return the results I need, the table needs to be in a specific order when I run an update against it. Is this possible to achieve this?

7

The order in which a query returns results has nothing to do with the order in which an update runs. As with INSERT, UPDATE processes rows according to what SQL feels will perform the best at the moment.

Basically, you need to start thinking in terms of sets rather than sequences. RDBMSes are based on set theory and sets are by definition unordered collections.

Can you share some specific examples, with data, of what you are talking about?

8

what I am trying to do is create a report that monitors the productivity of our fork lift truck drivers. I basically have a table where a row is created every time one of the operatives uses a handheld device to stow / pick / relocate a pallet. It looks something like this...

Username Action TimeStamp
Liam Roberts Pallet relocated 15/12/2015 14:56:23
Antoni Krawczyk Pallet relocated 15/12/2015 14:56:21
Eric Lewis Pallet relocated 15/12/2015 14:56:06
Glenn Taylor Pallet Picked (RDT) 15/12/2015 14:55:53
Robbie Smith Pallet relocated 15/12/2015 14:55:39
Liam Roberts Pallet relocated 15/12/2015 14:55:22
Eric Lewis Pallet relocated 15/12/2015 14:54:52
Wojciech Nawrocki Pallet relocated 15/12/2015 14:54:41
Antoni Krawczyk Pallet relocated 15/12/2015 14:54:09
Glenn Taylor Pallet relocated 15/12/2015 14:54:05

Sorry about the poor formatting, but you get the idea. I take the records for a particular user over a set time period and dump them into a temporary table. What I need to do from here is calculate the length of time between each action, chronologically, and from there calculate average time per action, amongst other things. I was doing this with some success by running an update on the temp table to calculate the length of time between each record and the following record because originally (prior to the indexing being set up), it seemed as though the records were being read in chronological order, as the results looked very realistic. From what you're telling me, this may well have just been a combination of blind luck and assumption. Perhaps I'm going about this completely wrong.

9

Yes, exactly! Are you running SQL Server 2012 or higher? If so, you can use the Window functions LAG/LEAD which are perfect for your application. If you're 2008 R2 or below, you can achieve the same effect with a self-join though performance will not be as good.

10

I'm using 2014, so I'll take a look at the LAG/LEAD functions. Thanks for your help.

11

As I understand it, you don't need to go to any extremes.

Put an identity on the table being inserted into. In your situation, you'd probably even want to cluster on it. Add an ORDER BY on the SELECT that INSERTs to that table. I believe SQL Server does guarantee that assigned identity values will match the sort order on the SELECT, even in cases where the physical order of the rows does not.

12

Thanks Scott, I'll try this also.
Steve

13

We actually went through this with Conor Cunningham on the MVP forum. There is no such guarantee according to him. This is, however, a guarantee if you use ROW_NUMBER() with the correct ORDER BY.