How to insert csv file (900MB) data in SQL server quickly?

Question

I have tried inserting using itertuples but my file is too big. I even split the file in 4 different files even then its too big. one-fourth file takes more than 30 minutes. Is there a easier and quicker way to import data in SQL server?

Thanks in advance.

Does this answer your question? [Writing a csv file into SQL Server database using python](https://stackoverflow.com/questions/21257899/writing-a-csv-file-into-sql-server-database-using-python) — Thekingis007, Nov 17 '21 at 12:47
Of course,there is. Please try any ETL-tool: SQL Server Integration Services, Informatica or something similar — Sergey, Nov 17 '21 at 12:47
Rather than using python, you would likely want to take a look at SQL Server's Bulk operations. Using `BULK INSERT` or `bcp` might well be much faster. — Thom A, Nov 17 '21 at 12:48
@Larnu : Bulk insert isnt working as the db is not localhost. — tp_, Nov 17 '21 at 13:03

score 0 · Answer 1 · answered Nov 18 '21 at 19:25

For faster importing big data, SQL SERVER has a BULK INSERT command. I tested on my local server, importing process for 870 MB CSV file (10 million records) to SQL SERVER executed time been 12.6 second.

BULK INSERT dbo.import_test_data 
FROM 'C:\Users\Ramin\Desktop\target_table.csv'

Full syntax this command:

BULK INSERT
   { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
      FROM 'data_file'
     [ WITH
    (
   [ [ , ] BATCHSIZE = batch_size ]
   [ [ , ] CHECK_CONSTRAINTS ]
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ [ , ] DATAFILETYPE =
      { 'char' | 'native'| 'widechar' | 'widenative' } ]
   [ [ , ] DATA_SOURCE = 'data_source_name' ]
   [ [ , ] ERRORFILE = 'file_name' ]
   [ [ , ] ERRORFILE_DATA_SOURCE = 'errorfile_data_source_name' ]
   [ [ , ] FIRSTROW = first_row ]
   [ [ , ] FIRE_TRIGGERS ]
   [ [ , ] FORMATFILE_DATA_SOURCE = 'data_source_name' ]
   [ [ , ] KEEPIDENTITY ]
   [ [ , ] KEEPNULLS ]
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
   [ [ , ] LASTROW = last_row ]
   [ [ , ] MAXERRORS = max_errors ]
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
   [ [ , ] TABLOCK ]

   -- input file format options
   [ [ , ] FORMAT = 'CSV' ]
   [ [ , ] FIELDQUOTE = 'quote_characters']
   [ [ , ] FORMATFILE = 'format_file_path' ]
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]
    )]

How to insert csv file (900MB) data in SQL server quickly?

1 Answers1