SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL: DTS Import, problem auto-generating PK

    Hello,

    I'm trying to copy table data from one source to another, and cannot seem to figure out how to get the new identity PK to auto-generate (old table does not have this column, so I can't just copy it over). I was assuming if I copied data over, it would automatically generate the PK, but it is not, and is giving me this error: "Cannot insert the value NULL into column 'programID'; table 'sandbox.dbo.Programs'; column does not allow NULLs. INSERT FAILS". My settings for the data transformation are currently:

    Keep Null Values (unchecked)
    Check Constraints (checked)
    Table Lock (unchecked)
    Enable Identity Insert (unchecked) -- I've also tried import with this checked, per another thread, but still get same error
    Always commit final batch (unchecked)

    Any help with this would be greatly appreciated!

    Thanks

  2. #2
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    Moscow, Russia
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, in DTS Import/Export wizard, select "Create destination table" radio button on [Column Mappings and Transformations] window/step, push [Edit SQL] button and add DDL for required PK column with IDENTITY property. Check Column Mappings and Transformations and CREATE TABLE for IDENTITY syntax

  3. #3
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, Tangerine Dream, that worked!

    I used the Create Table option, copied in the table creation script, and then I had to add a DROP TABLE command before CREATE TABLE since my table already existed.

    Thanks again!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •