CODEWITHSUNDEEP
sqlsql-server
jengacode
jengacode

Reputation: 128

Insert duplicate rows from temporary table

I am confused how to insert values from the declared table to the selected table. I used except to prevent insert of the first duplicate, but I want to make the second row of the duplicate be inserted.

Storage of the table How can I put the second value to the table above? Temporary table My point to achieve here is to insert the second value in the primary table to manipulate is time_mode value.

This is my query

INSERT INTO temp_time(SwipeID,tdate,ttime,time_mode,raw_data,[Shift],eid,machineip)
    SELECT a.SwipeID,a.tdate,a.ttime,a.time_mode,a.raw_data,1, eid FROM @temp_time
    EXCEPT
    SELECT SwipeID,tdate,ttime,time_mode,raw_data,Shift,eid,machineip from temp_time

From the query above, only one value is inserted. My clients changed their minds that they want the duplicated values to be reflected. Changing the values on time_mode can be changed by the system i made. If I use the query again using insert without the except value, there would be 3 rows for the primary table which causes a problem because what I wanted to reflect is only the 2 rows.

Upvotes: 0

Views: 1178

Answers (2)

plalx
plalx

Reputation: 43728

I think I finally understood your problem correctly. Assuming you already have ran your first query that inserted the data without duplicates in your second table and now you want to insert the rest of the original duplicates.

In that case here's how you may do it, by eliminating the previous rows that you already have inserted:

WITH dupes AS (
    SELECT *, ROW_NUMBER() OVER(
            PARTITION BY SwipeID, tdate, ttime, time_mode, raw_data,[Shift], eid, machineip
            ORDER BY (SELECT(0))
        ) AS row_num
    FROM SourceTable
)
INSERT INTO DestinationTable (/*columns*/)
SELECT /*values you need*/
FROM dupes
WHERE row_num > 1;

Upvotes: 1

yılmaz
yılmaz

Reputation: 1836

Assuming you are using MYSQL;

INSERT INTO your_first_table(SwipeID,tdate,ttime,time_mode,raw_data,shift,eid,machineip)
    SELECT a.SwipeID,a.tdate,a.ttime,a.time_mode,a.raw_data,1,a.eid,a.machineip FROM your_second_table a ORDER BY a.SwipeID DESC LIMIT 1, 1

Upvotes: 0

Related Questions