importing data from MS Access
-
OK. This is driving me batty. I have been toying with importing data from my access database into my new test database (SQL Server 2000). Things were going well with most tables, until I hit this one table. It keeps giving me the following error:
Error at destination for row number 7543. Cannot insert the value NULL into COLUMN 'subtest id', table 'TEST_DB.dbo.Subtest_table'; column does not allow NULLs.
My Subtest_table has 4 columns: subtest_id int (auto-number), subtest_name char(20), subtest_short_deatils text, subtest_long_details text.
While importing, I am trying to take in just one field: SUBTEST_DESCRIPTION, and drop it into my "Subtest_table" table. I have already enabled identity insert, so I know that my auto-numbers are filling ok. Additionally, the error message states that the failing row number is 7543. 7543 is the last row with data in it in my Access field SUBTEST_DESCRIPTION. I checked for NULL entries in the SUBTEST_DESCRIPTION fields, but found none.
Any thoughts on why this will not import for me?
Thanks in advance!
-Merc
-
If you have defined an identity property on your subtext_id column, you don't need to import it from Access. SQL Server will automatically generate the sequence.
How do you do the import?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I am using the "ignore" functionality for the id column, and only trying to bring in the description field.
-Merc
-
Sorry, haven't read well enough. 
Anyway, how do you import the data?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Right-clicking any given table, using the import functionality listed under "all tasks". I select the MSAccess database to import from, then select my SQL server DB to import to. I manually select the fields I want to import from the Access DB using the query builder.
-JD
-
Are you getting the same error when you choose the 'Table copy' option instead of 'Use a query...'?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Make sure that you have also unchecked the "enable identity insert" checkbox or it will try to insert Null into the identity field that you are ignoring.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Rawhide nailed it on the head. The "enable identity insert" checkbox was all that I needed to change.
Thanks a bunch!
-Merc
-
Have you tried Upsizing Wizard in access insted of using SQL server?
-Lars
Please only reply to this newsgroup. All mails would be bounced back. -
If I have a regular import job then I use DTS.
If I need to do a quick one off then I tend to do my data cleaning and preparation in Access then use the linked tables facility so that Access controls the migration into SQL Server.
I find it is more straight forward to perform complex data-cleansing tasks in Access rather than go to the fuss and polaver of coming up with the necessary DTS script.
-
When I started at my current company in 2000, one of the developers here liked to do that. We import a lot of data from other people's databases and it frequently has some funky stuff that has to be cleaned up and Access is good for that because it can use VBA to do some of the more complex cleanup jobs.
However, Access is a resource hog and it takes a lot longer to process large amounts of data than SQL Server does. One of the first changes I made was to get rid of all of the Access pre-processing. It's all done in SQL Server now. I have yet to come across anything that I could do in Access that I can't do in SQL Server.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Granted that Access can be a resource hog but I found that the query properties eased that a lot i.e. record locks, use transactions etc.
I found that if you have Access "Use Transactions" set to Yes then you get locking problems.
Correct me if I'm wrong but the ActiveX script in DTS uses VBScript, which is a much simplified form of VB.
Some of the work I had to do involved using some pretty complex statistical and trigonometric functions and I didn't want to write the VBScript equivalent.
Also, a large amount of data cleansing involved string manipulation. I could be paranoid
but I have an aversion to doing string manipulation on a server unless it is very simple. -
You can use VBScript or JScript.
None of our stuff was as complicated as yours. It was mostly simple string manipulation.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
I think the best way is to use Access 2002 (XP) upsizing wizard to send the table ... then update your new table from this table .. by an update statement.. ...
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply