Insert the exception of the second by adding the first of the existing entries

Question

I want to find a more effective way than my solution. So here is the problem: I want to have a bulk insert from a select statement. (Background: I am using MSSQL 2005)

Selection example:

SELECT number, amount, year, modifiedDate, itm, city, c, d, e, f.... FROM X JOIN Y .... 

So, if the result

 ... Num Amount Year ModifiedDate Itm City ... ... 1 100 2011 01-01-2011 2 Amsterdam .. .. 1 100 2011 01-02-2011 5 Den Haag .. .. 2 4560 2011 01-02-2011 6 Amsterdam .. .. 33 456 2010 01-02-2011 12 Leiden .. .. 22 456 2010 01-02-2011 12 Leiden .. .. .... 

At destination I want:

 ... Num Amount Year ModifiedDate Itm City ... ... 1 100 2011 01-02-2011 5 Den Haag .. .. 2 4560 2011 01-02-2011 6 Amsterdam .. .. 33 456 2010 01-02-2011 12 Leiden .. .. 22 456 2010 01-02-2011 12 Leiden .. .. .... 

Without this entry: (1 100 2011 01-01-2011 2 Amsterdam)

I want to insert the first of these two into the destination table. I have other entries coming out of the selected one that also needs to be inserted. So, in this case, I want to take the first one, where the number, amount and year are the same order using modifiedDate DESC. This is what I want to do. I already made a decision using Cursor, but there should be a better way.

Answer 1

Thanks for all the answers. I have another idea, which is much faster than the cursor of the old fashion, and I think that it matches what I was looking for, so I also share it here.

 --Test Data DECLARE @sourceTable TABLE(number int,amount int, yr int, modifiedDate datetime, city nvarchar(100)) DECLARE @destTable TABLE(number int,amount int, yr int, modifiedDate datetime, city nvarchar(100)) INSERT INTO @sourceTable (number, amount, yr, modifiedDate, city ) VALUES (1,100,2011,'01 Jan 2011', 'aaa') INSERT INTO @sourceTable (number, amount, yr, modifiedDate, city ) VALUES (1,100,2011,'02 Jan 2011', 'bbb') INSERT INTO @sourceTable (number, amount, yr, modifiedDate, city ) VALUES (2,4560,2011,'02 Jan 2011', 'ccc') INSERT INTO @sourceTable (number, amount, yr, modifiedDate, city ) VALUES (33,456,2010,'02 Jan 2011', 'ddd') INSERT INTO @sourceTable (number, amount, yr, modifiedDate, city ) VALUES (22,456,2010,'02 Jan 2011', 'ddd') --Query INSERT INTO @destTable SELECT * FROM @sourceTable WHERE CAST(number AS NVARCHAR(100)) + '_' + CAST(amount AS NVARCHAR(100)) + '_' + CAST(yr AS NVARCHAR(100)) + '_' + CONVERT(NVARCHAR(100), modifiedDate,121) IN ( SELECT CAST(number AS NVARCHAR(100)) + '_' + CAST(amount AS NVARCHAR(100)) + '_' + CAST(yr AS NVARCHAR(100)) + '_' + CONVERT(NVARCHAR(100), MAX(modifiedDate),121) FROM @sourceTable GROUP BY number, amount, yr ) --Results SELECT * FROM @destTable 

Answer 2

This will help you get closer to what you need:

 DECLARE @sourceTable TABLE(number int,amount int, yr int, modifiedDate datetime, itm int, City varchar(20)) DECLARE @destTable TABLE(number int,amount int, yr int, modifiedDate datetime, itm int, City varchar(20)) INSERT INTO @sourceTable (number, amount, yr, modifiedDate, Itm, City ) VALUES (1,100,2011,'01-01-2011',2,'Amsterdam') INSERT INTO @sourceTable (number, amount, yr, modifiedDate, Itm, City ) VALUES (1,100,2011,'01-02-2011',5,'Den Haag') INSERT INTO @sourceTable (number, amount, yr, modifiedDate, Itm, City ) VALUES (2,4560,2011,'01-02-2011',6,'Amsterdam') INSERT INTO @sourceTable (number, amount, yr, modifiedDate, Itm, City ) VALUES (33,456,2010,'01-02-2011',12,'Leiden') INSERT INTO @sourceTable (number, amount, yr, modifiedDate, Itm, City ) VALUES (22,456,2010,'01-02-2011',12,'Leiden') ;WITH CTE AS ( SELECT number, amount, yr, modifiedDate , ROW_NUMBER() OVER (PARTITION BY number, amount, yr ORDER BY modifiedDate DESC) AS itemRank FROM @sourceTable GROUP BY number, amount, yr, modifiedDate ) INSERT INTO @destTable (number, amount, yr, modifiedDate, Itm, City ) SELECT st.number, st.amount, st.yr, st.modifiedDate, st.Itm, st.City FROM @sourceTable st INNER JOIN cte ON st.number = cte.number AND st.amount = cte.amount AND st.yr = cte.yr AND st.modifiedDate = cte.modifiedDate WHERE itemRank = 1 ORDER BY modifiedDate DESC SELECT * FROM @destTable 

Answer 3

If I understand what you are trying to do correctly, I think you need to group in the first three columns and select the MAX value from the modifiedDate column.

Let me know if I misunderstood.

 SELECT number, amount, year, MAX(modifiedDate) FROM X GROUP BY number, amount, year 

If you are also looking for help regarding the INSERT query, please provide more details on what you are doing now.

Answer 4

Grouping all your columns will result in this.

Example:

 --Test Data DECLARE @sourceTable TABLE(number int,amount int, yr int, modifiedDate datetime) DECLARE @destTable TABLE(number int,amount int, yr int, modifiedDate datetime) INSERT INTO @sourceTable (number, amount, yr, modifiedDate ) VALUES (1,100,2011,'01 Jan 2011') INSERT INTO @sourceTable (number, amount, yr, modifiedDate ) VALUES (1,100,2011,'01 Jan 2011') INSERT INTO @sourceTable (number, amount, yr, modifiedDate ) VALUES (1,100,2011,'03 Jan 2011') INSERT INTO @sourceTable (number, amount, yr, modifiedDate ) VALUES (1,100,2011,'02 Jan 2011') --Query INSERT INTO @destTable(number, amount, yr, modifiedDate ) SELECT number, amount, yr, modifiedDate FROM @sourceTable GROUP BY number, amount, yr, modifiedDate ORDER BY modifiedDate DESC --Results SELECT * FROM @destTable