0

I'm migrating 2 million rows from a SQL Server table over to a Postgres database. I'm using the SQL Server Import/Export Wizard and everything was going fine until I got to 1.5 million records (took all night).

I'm not sure why it crashed (see error below) but I need to get the rest of the records over there. I can append them if it wasn't a resource issue. If it was, then I can export the remaining 500k as a new table and then merge them later in pgAdmin.

My question is: when selecting the remaining 500k rows to finish the job, how do I correctly exclude the 1.5 million records that DID copy? There is no primary key and there are no unique values.

I tried this, listed in a post about selecting a range of rows. Problem is I don't know for sure if its correctly gathering the remaining records. Postgres indicates that 1574503 rows out of the 2M were successfully migrated. Stats indicate the Postgres database is at 495mb now.

SELECT *
FROM 
    (SELECT 
         ROW_NUMBER() OVER (ORDER BY (SELECT NULL AS noorder)) AS RowNum, 
         *
     FROM 
         invoices) AS alias
WHERE 
    RowNum BETWEEN 1574504 AND 2000000

In case it helps, here is the SQL Server error info from the when it crashed:

Error 0xc020844b: Data Flow Task 1: An exception has occurred during data insertion, the message returned from the provider is: The connection has been disabled.

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - invoices" (139) failed with error code 0xC020844B while processing input "Destination Input" (142). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - Query returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jarvis
  • 31
  • 5
  • Compare against the primary key. Select the records in the source table that don't exist in the destination table based on primary key. – squillman Jan 11 '22 at 19:22
  • Thanks @squillman, but there's no primary key. Should have created one. I guess I could and then start over. – Jarvis Jan 11 '22 at 20:23
  • If you have data that could create a primary key you should still be able to do the comparison, even though the key doesn't actually exist in the schema definition. – squillman Jan 11 '22 at 21:08
  • Otherwise, if you'd be reliant on something like an IDENTITY column then it might be better to start over if you can't reliably determine what has gone over and what has not. – squillman Jan 11 '22 at 21:14
  • I've started it over as new table just case there's no quick resolution. Any idea what might cause that error? Connection being "disabled"? Maybe maintenance on the remote end? Just wondering if it was more likely to be an event on their end or a problem with my database that's likely to just recur. – Jarvis Jan 11 '22 at 21:21
  • 1
    "The connection has been disabled" is a very generic provider message that could be all kinds of things... – squillman Jan 11 '22 at 21:34
  • Mistake #1: *"There is no primary key and there are no unique values."* A table needs a way to uniquely identify a row, otherwise there is no way to know what's what. The only time I would consider doing this is for a logging table. I don't know Postgres well, but with the correct setup SQL Server can bulk insert 2m rows much faster than that, so I'd imagine Postgres could also – Charlieface Jan 12 '22 at 02:08
  • what provider did you use ? – Venkataraman R Jan 13 '22 at 03:08

0 Answers0