Hi,
I have a table of say 5 columns containing 100 records. The records are extracted from DB2. Now records need to be fetched on a nightly basis to the table. Only those records that are new should be fetched. If the records are the same then do not fetch them. What is the simplest way of implementing this?
Thanks.You may add a column to set a flag to inducate which record is new.|||Set up a second work table that looks like the first...
You'll then have to deal with 3 potential actions,
INSERT: New records on the file
DELETES: Records that don't exists
UPDATES: Records that are on the file, but attributes have changed
Do you have a PK on the table now?|||For most cases, datetime field is a good candidate to determine a record is old or new.|||Yes I have a primary key (It's a composite key made up of 2 columns)
[]
QUOTE]Originally posted by Brett Kaiser
Set up a second work table that looks like the first...
You'll then have to deal with 3 potential actions,
INSERT: New records on the file
DELETES: Records that don't exists
UPDATES: Records that are on the file, but attributes have changed
Do you have a PK on the table now? [/QUOTE]|||You only insert records that do not already exist - Could you be more specific as to what you need ? Also, what are you using now to import the records from db2 ?|||I use ODBC driver to get records from DB2. The records have a PK and attributes. Say attribute1,2,3...5. Now I only need to call insert a record if the PK has changed OR if attribute 1 for the records has changed. If attribute 2,3,4,5 have changed, then that does not matter. I do not call that records New.|||What programming language are you using ?|||T-SQL.|||Ummm, don't you want to INSERT when the PK changes, but UPDATE when column 1 changes? Otherwise, you get relational integrity issues. This should be handled in two different steps after the data is loaded into a staging table.
blindman|||He wants this...
USE Northwind
GO
CREATE TABLE myTable99 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))
CREATE TABLE myTable00 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
GO
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL
SELECT '1','2','d','e','f' UNION ALL
SELECT '1','3','g','h','i' UNION ALL
SELECT '1','4','j','k','l'
--DELETED
INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
SELECT '1','2','x','y','z' UNION ALL -- UPDATE
SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
SELECT '2','3','a','b','c' --INSERT
GO
SELECT * FROM myTable99
SELECT * FROM myTable00
GO
--DO DELETES FIRST
DELETE FROM a
FROM myTable99 a
LEFT JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- INSERT
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
FROM myTable00 a
LEFT JOIN myTable99 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- UPDATE
UPDATE a
SET Col3 = b.Col3
, Col4 = b.Col4
, Col5 = b.Col5
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
AND ( a.Col3 <> b.Col3
OR a.Col4 <> b.Col4
OR a.Col5 <> b.Col5)
GO
SELECT * FROM myTable99
SELECT * FROM myTable00
GO
DROP TABLE myTable00
DROP TABLE myTable99
GO|||Brett . Thanks a lot. I am getting close. But this is how I need it.
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL
INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','d','z' UNION ALL --NO CHANGE
Col3,4,5 are attributes. If PK changes then insert new record. If for same PK, attribute 2,3 changes but attribute 1 is unchanged then do not INSERT (No change). I need a history of the records and so I do not do updates. I just insert new records.|||OK...I wouldn't do ot this way...
I wouldn't keep all the history in 1 table...I would use a trigger and a history table...
Hey, but they're the reqs...you're going to have to find the PK with the max timestamp every time for the current row..
EDIT: And what, prey tell, does the concept of "DELETE" mean anymore to this process?
USE Northwind
GO
CREATE TABLE myTable99 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, Col6 datetime DEFAULT GetDate()
, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2,col6))
CREATE TABLE myTable00 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1))
GO
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL
SELECT '1','2','d','e','f' UNION ALL
SELECT '1','3','g','h','i' UNION ALL
SELECT '1','4','j','k','l'
--DELETED
INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
SELECT '1','2','x','y','z' UNION ALL -- UPDATE
SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
SELECT '2','3','a','b','c' --INSERT
GO
SELECT * FROM myTable99
SELECT * FROM myTable00
GO
-- INSERT
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
FROM myTable00 a
LEFT JOIN myTable99 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL
-- "UPDATE"
INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT b.Col1, b.Col2, b.Col3, b.Col4, b.Col5
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
AND ( a.Col3 <> b.Col3
OR a.Col4 <> b.Col4
OR a.Col5 <> b.Col5)
GO
SELECT * FROM myTable99
SELECT * FROM myTable00
GO
DROP TABLE myTable00
DROP TABLE myTable99
GO|||...and what, prey tell, does the concept of "PRIMARY KEY" mean anymore to this process?
blindman|||Hey..it got extended to include the "property" (if you will) of time...
smoke'em if you got 'em...
I still think you should go with a historical table and triggers though...If you stll need to see all of the history, just create a view...|||I understand that I need to do the Deletes first. I could do either an Insert or an Update after that,correct? There is no order for Inserts and Updates,correct?
Also can I execute this code in one stored procedure? I generally execute one query in one stored proc and then check for error condition. Here is my error condition -
SELECT @.ERRORNUM = @.@.ERROR, @.LOCALROWCOUNT = @.@.ROWCOUNT
IF @.ERRORNUM = 0
BEGIN
IF @.LOCALROWCOUNT >= 1
BEGIN
SELECT @.RETURNVALUE = 0
END
ELSE
BEGIN
SELECT @.RETURNVALUE = 0
RAISERROR ('FETCH FAILS: No row matching the specified criteria is found.',16, 1)
END
END
ELSE
BEGIN
SELECT @.RETURNVALUE = 1
END
RETURN @.RETURNVALUE
--
If I executes multiple queries, like the Delete/Insert/Update in one stored proc then how does this error condition change. Should I have the @.error checking after every query?
