Automating import from access db | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Automating import from access db

I have been trying quite unsuccessfully to import just one table from access db to sql server db and schedule continues data appendages but I ran into an error that says duplicate inserts not allowed.
That basically means that I have a unique primary key and can’t be data for it can’t duplicated.
Since I had this error, I have been trying unsuccessfully to use dts wizard to schedule this task without success.
I have also tried to use linked server methodology but don’t have any success either.
I was wondering if someone can please assist me.
Thanks in advance
How about create destination table without primary key? Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
That will be some kind of mess, don’t you think?
If not now, in the near future it will be.
The appends are not incremental unless you have truncated the Access table with new data then importing. You see if you import the data the first time from access, then append it again with new access records both old and new records will be appended. Yes the primary key is there for a reason (Data Integrity) basically you can validate your data from the access table you can also time stamp the data and handle your imports that way. I dont think DTS has a built in method for Incremental updating, I know it does have a method but only for OLAP incremental updates.
You are right, I was thinking in first time load or only once job.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
DTS won’t be as easy as you would like it to be for this. I think I would stage the data and then use TSQL to populate what you need. Unless you have a lot of manipulation or cleansing to do. You can build queries as the input instead of the table and then do distinct selects WHERE NOT EXIST in your existing table. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks for all of you guys input on this.
I have been busy trying to "think up" a method that would work. Here is what I have done so far.
I did an import with dts to a table called (for our purpose here) table1.
This table has no primary key.
Then I created another table called (again for our purpose here) table2.
This table has a primary key with same field names as table1. Then I used dts designer to do something similar to Satya’s suggestion with t-sql. First I automated the import of data from access to table1.
Next step was to do an insert something like:
insert into table2 (column1 to columnN)
select column1 to columnN
from table1
where table1.key not in (select key from table2)
order by table1.key
As you can see from this statement, it is similar to Satya’s where not exist (except that Satya’s is more efficient, performance-wise).
It selects from table1 only records that don’t exist in table2.
Finally, I delete what is in table1, delete from table1 and I schedule this process to occur daily. Logically, this makes sense.
Problem I noticed yesterday was that even though data is inserted into table2 from table1, and even though after insert, data in table1 is deleted, new data being inserted into access database are not being imported; I don’t know why. I then tried another method: Since I created tables 1 and 2, again I left table1 without a primary key and table2 with primary key. I used dts to schedule daily import from access to table1.
I then used the same t-sql and said:
insert into table2 (column1 to columnN)
select column1 to columnN
from table1
where table1.key not in (select key from table2)
order by table1.key When I got here this morning, this worked well as newly inserted data into access db was imported into table1 and in turn inserted into table2.
My worry is that since I couldn’t find a way to delete data inserted into table1, integrity may be an issue. I like the first step but I don’t know why new data is not being inserted into table2 from table1.
]]>