0

I have a Database that is corrupted, backup and restore does not work, the table contains 180 million records with a total size of 1TB.

I need to copy the data from the table to another, but it is very slow because the tables contain 3 columns with VARCHAR, I've already tried Linked Server, Bulk copy, BCP and SSIS, but they are all taking a long time, to copy 50 million records It's taking 30 hours.

I did the test without the 3 VARCHAR(MAX) columns and it runs very fast.

  

CREATE TABLE [dbo].TABLE_01
(
    Column_1 [bigint] NOT NULL,
    Column_2 [varchar](50) NOT NULL,
    Column_3 [datetime] NOT NULL,
    Column_4 [varchar](39) NULL,
    Column_5 [int] NULL,
    Column_6 [varchar](100) NULL,
    Column_7 [varchar](100) NULL,
    Column_8 [int] NULL,
    Column_9 [varchar](30) NULL,
    Column_10 [int] NULL,
    Column_11 [varchar](100) NULL,
    Column_12 [varchar](100) NULL,
    Column_13 [int] NULL,
    Column_14 [varchar](30) NULL,
    Column_15 [int] NULL,
    Column_16 [varchar](30) NULL,
    Column_17 [varchar](250) NULL,
    Column_18 [int] NULL,
    Column_19 [varchar](30) NULL,
    Column_20 [varchar](250) NULL,
    Column_21 [varchar](max) NULL,
    Column_22 [varchar](max) NULL,
    Column_23 [varchar](max) NULL
)

Is there another way to do this procedure faster?

Thanks.

Improve this question
8
  • Do you need to copy them off the server? Or would it be ok to copy them to another database on the same server?
    – J.D.
    Nov 14 at 16:07
  • Using SSIS package learn.microsoft.com/en-us/sql/integration-services/… Using Nifi Using ODBC connection, like C# apps, middle ware or other solution
    – Amirhossein
    Nov 14 at 16:21
  • @J.D. If it's faster, it could be on the same server.
    – Carlos D
    Nov 14 at 16:26
  • 1
    It absolutely is faster to not move data across servers.
    – J.D.
    Nov 14 at 16:46
  • 1
    Fastest is going to be on the same instance, but between two databases stored on different drives.
    – Charlieface
    Nov 15 at 16:11

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.