appending data using dts
-
I have records in an access table that I'm copying a sql server table.
say there are 23,000 records in the access table. I've created a dts to transfer these records and it works find. Then, another 100 records are added to the access table. I want to be able to only add the 100 new records to the sql server table using dts! How could I do this?
-
Loads of ways
1. Are the records timestamped? If so you can keep track of the maximum value in the timestamp field from the imported records, store it somewhere in a local params table, then next time you do the import put a where clause that says WHERE timestamp_field > last_max_timestamp_value
2. Is there a primary key on the table? If so use a data driven query task that inserts the records if they don't already exist.
3. Don't like using data driven query tasks? Stage all the data from access into a local copy of the table. Write a stored proc that gets all records from the staged data where the primary key isn't in the target table, and insert them inito the target. If you use this option let me know and I'll show you a neat and efficient way of getting all records from tableA that aren't in tableB. This option requires you to copy all the data locally. At the rate of data growth you should only be gaining 36500 rows a year which DTS should be able to handle.
Given the choice I always go for option 1. As a rule of thumb I always timestamp records in any system I build in case people want to extract them to somewhere else using option 1.
Hope this helps.
Regards,
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Jamie, thanks for your reply! I like your option 2. How would I insert if they don't exist. The table does have a primary key. Would it be in the where statement, if so, how? Thanks inadvance!
Taffy
-
Data driven query (DDQ) tasks are built to do exactly what you want to do - trouble is performance is bad because it does row-level processing using uncompiled script code.
Essentially what they do is compare source data to data existing in a target table to see if the PK already exists. Depending on this comparison the source rows are placed into "buckets" and you can decide whether you INSERT, DELETE, UPDATE or discard those rows. Obviously you want to INSERT.
Any basic DDQ example will show you exactly how to do this. Check books online or http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_elemtsk1_97lj.asp
good luck.
Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
I see Jamie, I'm getting a better understanding for this (DDQ) stuff. I'm going to play with it for a while and see if I can get into this DDQ--looks like VBScript to me!
Thanks again Jamie!
Taffy
-
Spot on. You have the choice of VBScript or JavaScript!
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
You could also do it with openrowset() or make the access database a linked server. I have a nightly job that adds new staff members from an access table to my SQL server table. Source is the primary key and is an identity field so I know that new records have a larger source than any of the current records:
insert into staff (source, name, otherfields)
select source, name, otherfields FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0', '\\Callserver\Product Upload\currentHBdb.mdb';'admin';'', staff) as a
WHERE a.source > (select max(source) from staff)
Hope this gives you more ideas.
Peter
-
Great guys, I should pay you for all this knowledge!
Taffy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply