locked
SqlBulkCopy RRS feed

  • Question

  •  

    Is it possible to use SQlBulkCopy to copy records from a source table that does not have a guid column as the primary key column to a destination table that does have a guid column for a primary key columns not allowing nulls? If I try to use it on my destination table as is  I get the error RecNum(guid column) does not allow nulls operation aborted. Is there a way for a table to generate the guid values when a sqlbulkcopy inserts records?

     

    Thanks Jon Stroh

    Sunday, December 7, 2008 7:15 AM

Answers

  • Hi
    newid() cant be used in this case, because the source is not SQL server.
    1. Alter the destination table's guid column to accept NULL values. If the column has primary key constraint, remove the constraint.
    2. Complete the bulk copy.
    3. Generate GUID values(using newid()) and insert into guid column
    4. Alter the column again to not to accept NULL values. Create the primary key if it was dropped in step 1 .
    Tuesday, December 9, 2008 6:06 AM

All replies


  • Hi,
    You can use NEWID() function in the SELECT statemetn to generate guids. for example

    SELECT newid() as id, name,DOB from persons.

    Thanks
    Monday, December 8, 2008 10:56 AM
  • Thanks for your reply.

     

    I tried to add your suggestion to my Select statement but I get an odbc error. I didn't add in my last post that the Source table is a Paradox table connected through odbc and it is the table that does not contain a guid column.

    Here is the error when executing

    ERROR [42000] [Microsoft][ODBC Paradox Driver] Undefined function 'newid' in expression.

     

    Hope I am just not understanding how to implement your suggestion Smile

    Here is my code as I am trying to run it.

    // Get a sourceConnection to the FarriersBAsic8 database.

    string connectionStringParadox = GetParadoxConnectionString(); At bottom of post Smile

    //Get Destination connection String

    string destConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|FBasic8.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

    //Query string for ODBC table below

    string queryString = "SELECT newid() as shouldIcreate a new column here? `RecNum`,`Invoice Number`, `Date`, `Horse Name`, `Procedure Code`, `Procedure`, `Qty`, `AMOUNT`, `Cost` FROM `LineItem`";

    // `RecNum`,

    OdbcCommand command = new OdbcCommand(queryString);

    //Update Queries

    lineItemTableAdapter1.DeleteWhereInvoiceNumberisNullQuery();

    using (OdbcConnection connection = new OdbcConnection(connectionStringParadox))

    {

    command.Connection = connection;

    connection.Open();

    readerInv = command.ExecuteReader();

    // Perform an initial count on the destination table.

    OdbcCommand commandRowCount = new OdbcCommand("SELECT COUNT(*)FROM LineItem.DB");

    OdbcConnection connectionRowCount = new OdbcConnection(connectionStringParadox);

    commandRowCount.Connection = connectionRowCount;

    connectionRowCount.Open();

    long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar());

    MessageBox.Show("Total LineItem records" + Convert.ToString(countStart), Convert.ToString(countStart));

    using (SqlConnection destinationConnection = new SqlConnection(destConnectionString))

    {

    destinationConnection.Open();

    // Set up the bulk copy object.

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))

    {

    //Map columns, Source is first then destination

    SqlBulkCopyColumnMapping mapID =

    new SqlBulkCopyColumnMapping("RecNum", "RecordNumber");

    bulkCopy.ColumnMappings.Add(mapID);

    SqlBulkCopyColumnMapping mapInvNum =

    new SqlBulkCopyColumnMapping("Invoice Number", "InvNum");

    bulkCopy.ColumnMappings.Add(mapInvNum);

    SqlBulkCopyColumnMapping mapPaid =

    new SqlBulkCopyColumnMapping("Date", "DateWorkedOn");

    bulkCopy.ColumnMappings.Add(mapPaid);

    SqlBulkCopyColumnMapping mapInvTot =

    new SqlBulkCopyColumnMapping("Horse Name", "HorseName");

    bulkCopy.ColumnMappings.Add(mapInvTot);

    SqlBulkCopyColumnMapping mapStat =

    new SqlBulkCopyColumnMapping("Procedure Code", "ProcedureCode");

    bulkCopy.ColumnMappings.Add(mapStat);

    SqlBulkCopyColumnMapping mapDate =

    new SqlBulkCopyColumnMapping("Procedure", "ShoeingProcedure");

    bulkCopy.ColumnMappings.Add(mapDate);

    SqlBulkCopyColumnMapping mapNotes =

    new SqlBulkCopyColumnMapping("Qty", "Qty");

    bulkCopy.ColumnMappings.Add(mapNotes);

    SqlBulkCopyColumnMapping mapSatestx =

    new SqlBulkCopyColumnMapping("AMOUNT", "Amount");

    bulkCopy.ColumnMappings.Add(mapSatestx);

     

    SqlBulkCopyColumnMapping mapcost =

    new SqlBulkCopyColumnMapping("Cost", "Cost");

    bulkCopy.ColumnMappings.Add(mapcost);

     

     

    bulkCopy.DestinationTableName = "LineItem";//was LineItemInsert before test

    bulkCopy.NotifyAfter = 25;

    bulkCopy.WriteToServer(readerInv);

    bulkCopy.Close();

     

    private static string GetParadoxConnectionString()

    // To avoid storing the sourceConnection string in your code,

    // you can retrieve it from a configuration file.

    {

    return "Data Source=" + @"Driver={Microsoft Paradox Driver (*.db )};collatingsequence=ASCII;defaultdir=C:\Fbasic;driverid=538;fil=Paradox 5.X;filedsn=C:\Fbasic\FbasicDSN;maxbuffersize=2048;maxscanrows=8;pagetimeout=5;paradoxnetpath=C:\Windows\system32;paradoxnetstyle=4.x;paradoxusername=admin;safetransactions=0;threads=3;uid=admin;usercommitsync=Yes";

    }

     

    Monday, December 8, 2008 3:51 PM
  • Hi
    newid() cant be used in this case, because the source is not SQL server.
    1. Alter the destination table's guid column to accept NULL values. If the column has primary key constraint, remove the constraint.
    2. Complete the bulk copy.
    3. Generate GUID values(using newid()) and insert into guid column
    4. Alter the column again to not to accept NULL values. Create the primary key if it was dropped in step 1 .
    Tuesday, December 9, 2008 6:06 AM
  • Thank you for that answer. I am coming over from Borland Delphi, and altering a database table is horibly cumbersome, so I didn't thinkit would be easy to do. So I just created a duplicate table with out primary key to accept nulls then did basicaly 2 bulkcopy's. I will alter the primary table now in code like you described to speed things up. Thanks!

     

    Tuesday, December 9, 2008 2:37 PM