-3

Is there any best way to upload 14 million records into SQL Server database, without splitting the file.

Thanks in advance.

  • I have tried with import export wizard tool, but the database freezes.
  • I am expecting an alternate source to upload the 14 million records to SQL Server.
jarlh
  • 42,561
  • 8
  • 45
  • 63
Teja
  • 1
  • 6
  • 3
    Are you certain it's excel? Is the extension actually `csv`? – Nick.Mc Nov 30 '22 at 04:11
  • Some people may hate me for this, but I have often found that using MS Access as the middleman often helps make it cleaner and foolproof. In Access, import the Excel file into a local Access table (I will often also make all the fields 'small text' rather than dates etc, to ensure I get all data as-is). When that's done, using an ODBC connection to SQL Server) either export the table to the server/database, or link an existing SQL Server table in Access, then insert from your imported data into the SQL Server database. – seanb Nov 30 '22 at 04:12
  • @Nick.McDermaid is also probably correct - I think Excel has a limit of a little over a million rows? or is it many Excel worksheets? – seanb Nov 30 '22 at 04:17
  • If the extension is CSV, it's a text file, not an excel file. HOw long did you wait for the tool to unfreze? It would've been previewing,not importing – Nick.Mc Nov 30 '22 at 04:27
  • Are you committed to using the Import/Export Wizard? Why not use `BULK INSERT` statements in T-SQL or `bcp.exe` from the command line? – AlwaysLearning Nov 30 '22 at 04:42
  • Also, which SQL Server version are you _actually_ using? CSV support was signficantly improved in SQL Server 2017 with the addition of RFC 4180 support (finally, after only a short 12 years). – AlwaysLearning Nov 30 '22 at 04:42
  • The Import Flat File tool is generally better / less buggy for stuff like this. – J.D. Nov 30 '22 at 05:38
  • We need to establish whther this is text or excel first. – Nick.Mc Dec 01 '22 at 12:22

1 Answers1

1

The fastest way is using the Bulk Insert. You can insert the data into table or Common table expression CTE or table variable or temp table then I'd remove the duplicate rows across the distinct and Joins command from t-sql.

BULK INSERT TableName
FROM 'C:\SomeDirectory\my table.csv'
WITH
(
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n'
)
GO

And here is the full parameter description.

enter image description here

Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115