2

I have the following table :

enter image description here

As you can see I have duplicated date and time values like 2012-01-01 09:29:00.000 and 2012-01-01 09:29:00.008.

I need your help to remove the redundancy by adding milliseconds to duplicated hours and obtain the following result :

enter image description here

So I created a table TableSource and I tried to insert the new outputs values ​​without redondonce but I have not succeeded.

I think the following query can solve the problem but it work only on My-sql :

INSERT INTO TableDestination
SELECT * FROM TableSource
    ON DUPLICATE KEY
UPDATE  NeededDateRow = DATEADD(millisecond,1 , NeededDateRow)
Yaroslav
  • 6,476
  • 10
  • 48
  • 89
Tlachtga
  • 149
  • 1
  • 1
  • 8
  • Do you want a specific number of milliseconds added to the duplicated datetimes? Or could it be a random number? (And out of curiosity: why do you want no duplicate datetimes in your column?) – Josien Jul 26 '12 at 14:54
  • I just need to add milliseconds to differentiate dates ca can be random. I dont want duplicate values because i use it in an oder query as primary key and this causes a problem in the dates duplicated. thx. – Tlachtga Jul 26 '12 at 15:06
  • @Tlachtga Check my answer below, it should help you solve your issue – rs. Jul 26 '12 at 15:09

2 Answers2

1

You can use this:

    DECLARE @table table (id int, dt datetime)
DECLARE @table2 table (id int, dt datetime)
INSERT INTO @table 
SELECT 1, '2012/01/01'
UNION
SELECT 2, '2012/01/01'
UNION
SELECT 3, '2012/01/01'
UNION
SELECT 1, '2012/01/02'

SELECT * , DATEADD(millisecond, 10 * (ROW_NUMBER() OVER (PARTITION BY dt ORDER BY dt) - 1), dt) As NewDate
FROM @table 

INSERT INTO @table2
SELECT ID, DATEADD(millisecond, 10 * (ROW_NUMBER() OVER (PARTITION BY dt ORDER BY dt) - 1)  , dt) FROM @table 

SELECT * from @table2
rs.
  • 26,707
  • 12
  • 68
  • 90
  • thx for your answer, the select query seems work but when i try to update i get the following error: Windowed functions can only appear in the SELECT or ORDER BY clauses. – Tlachtga Jul 26 '12 at 15:17
  • 1
    check my updated answer, you can insert using select and it will insert date column with correct order into TableDestination. – rs. Jul 26 '12 at 15:20
  • It works great thank you Lots! I will preferre your first solution by updating the same table, it is not possible to do? – Tlachtga Jul 26 '12 at 15:36
  • Sorry i think not, you cannot use row_number in update directly. – rs. Jul 26 '12 at 15:41
  • Ok no problem thx, i will use delete from table1 and insert into table1 select * from table2 – Tlachtga Jul 26 '12 at 15:47
0

There is no equivalent INSERT ... ON DUPLICATE KEY in MSSQL, However , MERGE and WHEN NOT MATCHED might be work for you.

you can refer this answer

and I just draft the sql syntax (untested) 

MERGE TableDestination AS T
USING TableSource AS S
ON (T.NeededDateRow = S.NeededDateRow)
WHEN NOT MATCHED
    THEN INSERT (NeededDateRow) VALUES (DateAdd(millisecond,1,NeededDateRow))
Community
  • 1
  • 1
Turbot
  • 5,095
  • 1
  • 22
  • 30
  • thx but i have already see that answer, and the merge query havn't work for me because in my case i need to use WHEN MACHED THEN INSERT (NeededDateRow) VALUES (DateAdd(millisecond,1,NeededDateRow) WHEN NOT MATCHED THEN INSERT (NeededDateRow) and the insert not work with WHEN MATCHED. – Tlachtga Jul 26 '12 at 15:21