update or insert record
-
i have to update and insert new record from csv text file to sql server file if data notexist insert record if change in price update price and have to maintain currenttimestamp at destination
like source:
book price
a 10
b 15
c 8
destination
book price currentimestamp
a 10
b 5
o/p
book price currenttimestamp
a 10
b 15
c 8
in currenttime stamp i put insert int gettime()
-
Here you go
BEGIN
SET NOCOUNT ON
DECLARE @tblTarget TABLE
(Book VARCHAR(256),
Price INT,
DateUpdated DATETIME)
DECLARE @tblSource TABLE
(Book VARCHAR(256),
Price INT)
INSERT INTO @tblTarget VALUES('A',10,NULL)
INSERT INTO @tblTarget VALUES('B',5,NULL)
INSERT INTO @tblSource VALUES('A',10)
INSERT INTO @tblSource VALUES('B',15)
INSERT INTO @tblSource VALUES('C',8)
INSERT INTO @tblTarget (Book,Price,DateUpdated)
SELECT DISTINCT Book,Price,GETDATE()
FROM @tblSource AS Source
WHERE NOT EXISTS
(
SELECT 1
FROM @tblTarget AS Target
WHERE LTRIM(RTRIM(Source.Book)) = LTRIM(RTRIM(Target.Book))
-- AND ISNULL(Source.Price,-999) = ISNULL(Target.Price,-999)
 
UPDATE @tblTarget
SET DateUpdated = GETDATE(),
Price = Source.Price
FROM @tblTarget AS Target
INNER JOIN @tblSource AS Source
ON Target.Book = Source.Book
AND Target.Price <> Source.Price
SELECT * FROM @tblTarget
END
GO
Ram
-
Monika,
Since you posted in the DTS forum, I assume you want info about how to get the data from the csv file into SQL Server.
I would use a Data Transformation task to import the data from the csv file into a staging table. Then use Ram's code in an Execute SQL task to insert and update the destination table using the staging table instead of the TABLE variables.
Greg
Greg
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply