ApexSQL Pump File Import - Can a Primary Key column be "ignored" during mapping before a file import?

I have a txt file from a client with the structure: Column A, Column B, Column C. The table I want to import this to has an ID column set to primary key. SQL Server generates a new id when a row is added. So my table structure looks like: ID, Column A, Column B, Column C.

There is no ID column or data in the source file, so is it possible to not map this column and ignore it before importing a file? At the moment when I try to Process the import the summary page returns the High level warning "Primary key "ID" is not mapped".

Parents
  • Hi Stuart, 

    All import processes needs to have an key included for data integrity reasons. 

    What you can do here is include the ID column in the source file for the process to work. 

  • Hi Nodia, 

    The source files supplied to us don't use ID columns. The tables we want to import to have an ID column which is the primary key of that table. SQL Server generates an ID when a row is added. When mapping the columns in ApexSQL Pump, we can't map the table's ID column because there isn't an ID column in the source file, which means we can't import the file. Is there a way to work around the high level warning "Primary key "ID" is not mapped"?

  • Hello Stuart,


    Hope you're doing good Today,

    In order to import the file, the column should be added to the file without values, meaning only the header, for example:

    ID,FirstName,LastName
    ,John,Wayne
    ,Marty,McFly

    Hope this helps.

  • Hi Ariel,

    Thanks for your suggestion. I added ID to the header and it had the following effect:

    ID        |ColumnA|ColumnB|ColumnC|
    ValueA|ValueB    |ValueC   |

    As a work around I added an extra | to the start of ValueA, so:

    ID|ColumnA|ColumnB|ColumnC|
        |ValueA    |ValueB   |ValueC   |

    When trying to import one test row, one or more errors occur, pointing to issues with the source data.

    I believe this is because the ID column configuration in SQL Server. All of our ID columns in our tables are set as primary keys, auto increment and don't allow nulls. The not allow null is probably preventing the row being imported, because of the null in the source data.

    Can you think of any other possible solutions?

  • Good Day Stuart,

    Hope you're doing good Today,

    Specifically which error are you getting? I've done a test by Creating a table with an ID set as primary key, identity and not null and after mapping the columns I was able to import the data without issues.

    I've also used the same delimiters as your example and no issues in my testing.

    Have a good one.

Reply
  • Good Day Stuart,

    Hope you're doing good Today,

    Specifically which error are you getting? I've done a test by Creating a table with an ID set as primary key, identity and not null and after mapping the columns I was able to import the data without issues.

    I've also used the same delimiters as your example and no issues in my testing.

    Have a good one.

Children
  • Hi Ariel,

    We've actually found a solution which involves adding the ID column to the source data like you and Nodier suggested. We are going to create and add our own ID values in the source file. This then solves the mapping issue and allows the import to proceed. We perform this data import task monthly and one of the first steps is truncating the table. Our IDs will always start from 1 and because of the earlier truncation, there will be no duplicate IDs. I'm happy we have found a solution now, thank you and Nodier for your help.

  • Hello Stuart,

    These are great news, 

    Happy to know the solution provided was helpful.

    Have a great rest of your day and week.