inserting records from another table and checking duplicates

  • Good morning all,

    Please i need help as per an issue, i want to insert some records from one table to another, the tables are the same structure, but before inserting, it should check wether the record exists first in the other table, if it exists, it should not be added, but if it does not exist, then it can be inserted.

    I want to write tsql for this but don't know how to go about it.

    Any hint or comment will help.

    Thanks

    Timotech

  • Please post the DDL and some sample data along with the expected output. Also specify the columns that need to be checked for the existence of duplicates. If you provide all this, you will quickly get some tested solution for your query..


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • timotech (12/14/2011)


    Good morning all,

    Please i need help as per an issue, i want to insert some records from one table to another, the tables are the same structure, but before inserting, it should check wether the record exists first in the other table, if it exists, it should not be added, but if it does not exist, then it can be inserted.

    I want to write tsql for this but don't know how to go about it.

    Any hint or comment will help.

    Check out the MERGE statement (if you are using SQL Server 2008 or up):

    http://technet.microsoft.com/en-us/library/bb510625.aspx

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thanks Kingston,

    Sorry that i'm getting to you this late, this are the DDL and the sample datas:

    --This is the source Table

    CREATE TABLE [dbo].[LagosTelNov](

    [DATE] [datetime] NULL,

    [TIME] [nvarchar](max) NULL,

    [LENGHT] [float] NULL,

    [BRAND] [nvarchar](max) NULL,

    [BRAND_AD] [nvarchar](max) NULL,

    [POSITION] [nvarchar](max) NULL,

    [STA_CODE] [nvarchar](max) NULL,

    [ADVERT0] [nvarchar](max) NULL,

    [PROD_CODE] [nvarchar](max) NULL,

    [CATE] [nvarchar](max) NULL

    ) ON [PRIMARY]

    GO

    Select 2011-11-01,'20:41:27',60,'0053','L8','LAB','A57','283','GSM002','GSM'

    Select 2011-11-01,'21:32:24',60,'0053','L8','LAB','A57','283','GSM002','GSM'

    Select 2011-11-01,'21:41:57',60,'0053','L8','LAB','A57','283','GSM002','GSM'

    Select 2011-11-01,'22:38:57',60,'0053','L8','LAB','A57','283','GSM002','GSM'

    Select 2011-11-01,'19:55:03',30,'0004','05','LAB','A57','A57','WPG001','WPG'

    Select 2011-11-01,'20:27:56',35,'0052','05','LAB','A57','117','PHA002','PHA'

    Select 2011-11-01,'20:37:15',60,'0053','J2','LAB','A57','283','GSM002','GSM'

    Select 2011-11-01,'20:38:07',35,'0053','I6','LAB','A57','283','GSM002','GSM'

    --THis is the Target Table

    CREATE TABLE [dbo].[tbl_Television](

    [AdDate] [datetime] NULL,

    [AdTime] [nvarchar](8) NULL,

    [Duration] [nvarchar](6) NULL,

    [FK_BrandId] [nvarchar](4) NULL,

    [FK_BrandADId] [nvarchar](2) NULL,

    [Position] [nvarchar](10) NULL,

    [FK_StationId] [nvarchar](5) NULL,

    [FK_AdvertizerId] [nvarchar](4) NULL,

    [FK_ProductId] [nvarchar](6) NULL,

    [FK_CategoryId] [nvarchar](3) NULL

    ) ON [PRIMARY]

    GO

    Select 2011-11-01,'05:45:11',15,'0004','04','LAB','A57','A57','WPG001','WPG'

    Select 2011-11-01,'05:44:27',15,'0004','04','LAB','A57','A57','WPG001','WPG'

    Select 2011-11-01,'07:05:11',30,'0006','32','LAB','A57','88','DRI006','DRI'

    Select 2011-11-01,'08:39:24',45,'0052','A2','LAB','A57','281','GSM002','GSM'

    Select 2011-11-01,'08:46:25',60,'0090','C8','LAB','A57','407','GSM002','GSM'

    Select 2011-11-01,'08:27:24',30,'0003','04','LAB','A57','98','FOD008','FOD'

    Select 2011-11-01,'08:50:54',45,'0052','A2','LAB','A57','281','GSM002','GSM'

    Select 2011-11-01,'15:59:33',30,'0004','05','LAB','A57','A57','WPG001','WPG'

    Select 2011-11-01,'16:12:42',30,'0004','05','LAB','A57','A57','WPG001','WPG'

    Select 2011-11-01,'18:36:22',60,'0007','09','LAB','A57','105','FOD010','FOD'

    Select 2011-11-01,'18:51:53',30,'0004','05','LAB','A57','A57','WPG001','WPG'

    Thanks

    Timotech

  • Thanks Kingston,

    Sorry that i'm getting to you this late, this are the DDL and the sample datas:

    --This is the source Table

    CREATE TABLE [dbo].[LagosTelNov](

    [DATE] [datetime] NULL,

    [TIME] [nvarchar](max# NULL,

    [LENGHT] [float] NULL,

    [BRAND] [nvarchar]#max# NULL,

    [BRAND_AD] [nvarchar]#max# NULL,

    [POSITION] [nvarchar]#max# NULL,

    [STA_CODE] [nvarchar]#max# NULL,

    [ADVERT0] [nvarchar]#max# NULL,

    [PROD_CODE] [nvarchar]#max# NULL,

    [CATE] [nvarchar]#max# NULL

    # ON [PRIMARY]

    GO

    Select 2011-11-01,'20:41:27',60,'0053','L8','LAB','A57','283','GSM002','GSM'

    Select 2011-11-01,'21:32:24',60,'0053','L8','LAB','A57','283','GSM002','GSM'

    Select 2011-11-01,'21:41:57',60,'0053','L8','LAB','A57','283','GSM002','GSM'

    Select 2011-11-01,'22:38:57',60,'0053','L8','LAB','A57','283','GSM002','GSM'

    Select 2011-11-01,'19:55:03',30,'0004','05','LAB','A57','A57','WPG001','WPG'

    Select 2011-11-01,'20:27:56',35,'0052','05','LAB','A57','117','PHA002','PHA'

    Select 2011-11-01,'20:37:15',60,'0053','J2','LAB','A57','283','GSM002','GSM'

    Select 2011-11-01,'20:38:07',35,'0053','I6','LAB','A57','283','GSM002','GSM'

    --THis is the Target Table

    CREATE TABLE [dbo].[tbl_Television]#

    [AdDate] [datetime] NULL,

    [AdTime] [nvarchar]#8# NULL,

    [Duration] [nvarchar]#6# NULL,

    [FK_BrandId] [nvarchar]#4# NULL,

    [FK_BrandADId] [nvarchar]#2# NULL,

    [Position] [nvarchar]#10# NULL,

    [FK_StationId] [nvarchar]#5# NULL,

    [FK_AdvertizerId] [nvarchar]#4# NULL,

    [FK_ProductId] [nvarchar]#6# NULL,

    [FK_CategoryId] [nvarchar]#3# NULL

    # ON [PRIMARY]

    GO

    Select 2011-11-01,'05:45:11',15,'0004','04','LAB','A57','A57','WPG001','WPG'

    Select 2011-11-01,'05:44:27',15,'0004','04','LAB','A57','A57','WPG001','WPG'

    Select 2011-11-01,'07:05:11',30,'0006','32','LAB','A57','88','DRI006','DRI'

    Select 2011-11-01,'08:39:24',45,'0052','A2','LAB','A57','281','GSM002','GSM'

    Select 2011-11-01,'08:46:25',60,'0090','C8','LAB','A57','407','GSM002','GSM'

    Select 2011-11-01,'08:27:24',30,'0003','04','LAB','A57','98','FOD008','FOD'

    Select 2011-11-01,'08:50:54',45,'0052','A2','LAB','A57','281','GSM002','GSM'

    Select 2011-11-01,'15:59:33',30,'0004','05','LAB','A57','A57','WPG001','WPG'

    Select 2011-11-01,'16:12:42',30,'0004','05','LAB','A57','A57','WPG001','WPG'

    Select 2011-11-01,'18:36:22',60,'0007','09','LAB','A57','105','FOD010','FOD'

    Select 2011-11-01,'18:51:53',30,'0004','05','LAB','A57','A57','WPG001','WPG'

    Thanks

    Timotech

  • Thanks Koen,

    I did this but its complaining incorrect syntax near the keyword 'AS'

    MERGE Products AS TARGET

    USING UpdatedProducts AS SOURCE

    ON (TARGET.ProductID = SOURCE.ProductID)

    --When records are matched, update

    --the records if there is any change

    WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName

    And TARGET.Rate <> SOURCE.Rate THEN

    UPDATE SET TARGET.ProductName = SOURCE.ProductName,

    TARGET.Rate = SOURCE.Rate

    --When no records are matched, insert

    --the incoming records from source

    --table to target table

    WHEN NOT MATCHED BY TARGET THEN

    INSERT (ProductID, ProductName, Rate)

    VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)

    --When there is a row that exists in target table and

    --same record does not exist in source table

    --then delete this record from target table

    SELECT @@ROWCOUNT;

    GO

    This is the Tables for testing

    --Create a target table

    CREATE TABLE Products

    (

    ProductID INT PRIMARY KEY,

    ProductName VARCHAR(100),

    Rate MONEY

    )

    GO

    --Insert records into target table

    INSERT INTO Products

    VALUES

    (1, 'Tea', 10.00),

    (2, 'Coffee', 20.00),

    (3, 'Muffin', 30.00),

    (4, 'Biscuit', 40.00)

    GO

    --Create source table

    CREATE TABLE UpdatedProducts

    (

    ProductID INT PRIMARY KEY,

    ProductName VARCHAR(100),

    Rate MONEY

    )

    GO

    --Insert records into source table

    INSERT INTO UpdatedProducts

    VALUES

    (1, 'Tea', 10.00),

    (2, 'Coffee', 20.00),

    (3, 'Muffin', 35.00),

    (5, 'Pizza', 60.00)

    GO

    THanks

    Timotech

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply