CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012temp-tables
Kapil
Kapil

Reputation: 1931

How to skip duplicate data while insert into new table based on another table column data

Below is my query which fetches records from organisation table and inserts into userdetails table based on the producer code but some producer codes are having duplicates values and Userlogin will not allow duplicates there fore I'm using GROUP BY producer code.

But when I try to run the below query I am getting the following error :

Column 'organisation.OrganisationID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Query:

IF OBJECT_ID('_temp_new_user_details') IS NOT NULL
     DROP TABLE _temp_new_user_details;

SELECT TOP 0 UserID
INTO _temp_new_user_details
FROM UserProductMapping 

INSERT INTO UserDetails (userlogin, Organisationid, emailaddress, username, userpassword, LastModifiedDate, SavedBy, ReceiveEmail, [Disabled])
OUTPUT inserted.UserID INTO _temp_new_user_details --all USerIDs will be saved into _temp_new_user_details
    SELECT 
        '1Qchubb'+ producercode, organisationid, '[email protected]',
        producercode + N'ii', '123', @CurrentDate, 1, 0, 0
    FROM 
        organisation 
    WHERE 
        producercode != N'00000' 
        AND producercode != '' 
        AND Active = 1 
    GROUP BY 
        producercode

Upvotes: 0

Views: 68

Answers (1)

Alfin E. R.
Alfin E. R.

Reputation: 928

By assuming 1 producer code only have 1 organization id

SELECT 
    '1Qchubb'+ producercode, organisationid, '[email protected]',
    producercode + N'ii', '123', @CurrentDate, 1, 0, 0
    ROW_NUMBER() OVER(PARTITION BY PRODUCERCODE ORDER BY PRODUCERCODE, ORGANIZATIONID) AS RNK
INTO #TEMP
FROM 
    organisation 
WHERE 
    producercode != N'00000' 
    AND producercode != '' 
    AND Active = 1 
GROUP BY 
    producercode

INSERT INTO UserDetails (userlogin, Organisationid, emailaddress, username, userpassword, LastModifiedDate, SavedBy, ReceiveEmail, [Disabled])
OUTPUT inserted.UserID INTO _temp_new_user_details
SELECT * FROM #TEMP WHERE RNK = 1

By assuming 1 producer code has multiple organization id, which distinct based on unique producercode and organizationid

SELECT 
    '1Qchubb'+ producercode, organisationid, '[email protected]',
    producercode + N'ii', '123', @CurrentDate, 1, 0, 0
    ROW_NUMBER() OVER(PARTITION BY PRODUCERCODE, ORGANIZATIONID ORDER BY PRODUCERCODE, ORGANIZATIONID) AS RNK
INTO #TEMP
FROM 
    organisation 
WHERE 
    producercode != N'00000' 
    AND producercode != '' 
    AND Active = 1 
GROUP BY 
    producercode

INSERT INTO UserDetails (userlogin, Organisationid, emailaddress, username, userpassword, LastModifiedDate, SavedBy, ReceiveEmail, [Disabled])
OUTPUT inserted.UserID INTO _temp_new_user_details
SELECT * FROM #TEMP WHERE RNK = 1

Upvotes: 1

Related Questions