Post reply

importing data from MS Access

  • January 7, 2004 at 12:51 pm

    #64715

    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

  • January 7, 2004 at 2:19 pm

    #489047

    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]

  • January 7, 2004 at 2:50 pm

    #489051

    I am using the "ignore" functionality for the id column, and only trying to bring in the description field.

     

    -Merc

  • January 7, 2004 at 3:20 pm

    #489056

    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]

  • January 7, 2004 at 4:01 pm

    #489061

    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

  • January 8, 2004 at 12:56 am

    #489084

    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]

  • January 8, 2004 at 2:48 am

    #489093

    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]

  • January 8, 2004 at 5:03 am

    #489105

    Rawhide nailed it on the head.  The "enable identity insert" checkbox was all that I needed to change.

    Thanks a bunch!

    -Merc

  • January 9, 2004 at 12:36 am

    #489289

    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.

  • January 9, 2004 at 2:26 am

    #489305

    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.

    LinkedIn Profile

  • January 9, 2004 at 8:53 am

    #489392

    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]

  • January 9, 2004 at 9:09 am

    #489397

    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.

    LinkedIn Profile

  • January 9, 2004 at 9:22 am

    #489403

    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]

  • January 11, 2004 at 3:43 am

    #489531

    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