December 14, 2011 at 2:21 am
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
December 14, 2011 at 2:27 am
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..
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 14, 2011 at 2:33 am
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
December 16, 2011 at 6:24 am
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
December 16, 2011 at 6:25 am
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
December 16, 2011 at 6:41 am
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