1

I have a massive flat file having hundreds of millions rows. I only want to insert the top 1000 of them. How can I do this?

  • You can try working with the -b argument, limiting your batch size to 1000, then you can use the -e to write error rows to a error file. I personally don't know of a way to limit the amount of rows. more info on simple-talk.com/sql/database-administration/… – Stijn Wynants Jun 1 '17 at 7:09
  • 6
    How about extracting just the top of the file, writing that part into another a file and importing it? This is known as file splitting and quite easy with, say, Powershell. – vonPryz Jun 1 '17 at 7:12
  • 3
    @vonPryz: That sounds to me legitimate enough to be suggested as an answer. – Andriy M Jun 1 '17 at 8:13
6

bcp Import

You can import data from your huge data file using Microsoft SQL Server's bcp utility from the command prompt.

bcp Description

The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns

Example Syntax

C:\> bcp YOUR_TABLE -d YOUR_DB -i INPUT_FILE -S YOUR_SERVER -F 1 -L 1000 -U YOUR_LOGIN -P YOUR_PASSWORD -f FORMAT_FILE

This translate to:

  • bcp
    bulk copy program
  • YOUR_TABLE
    The table you will be importing your data to
  • -d YOUR_DB
    The name of the database you are connecting to
  • -i INPUT_FILE
    The full pathname and filename to your huge file (e.g. C:\temp\bigfile.txt)
  • -S YOUR_SERVER
    The name of the server you are connecting to
  • -F number
    The first line to import
  • -L number
    The last line to import
  • -U YOUR_LOGIN
    The SQL Server Login (alternatively you can use -T for a trusted connection)
  • -P YOUR_PASSWORD
    The password for the SQL Server Login
  • -f FORMAT_FILE
    Depending on your table definition you will probably require a format file which defines the structure of the table you are importing data to and the delimiters in the INPUT_FILE. Further information for the format file can be found here and here.

Your command should then look like this:

bcp MEGA_TABLE -d Test_DB -i C:\temp\huge_data.txt -S localhost -F 1 -L 1000 -U sa -P secretnottelling -f c:\temp\huge_data.fmt

bcp format file

Depending on the complexity of your table and input file, you might have to create a separate bcp format file to accommodate missing columns in either the input file, target table or both. This can be achieved by exporting your table without a format file:

bcp YOUR_TABLE out c:\temp\sample_data.txt -S localhost -T -d YOUR_TABLE

bcp will ask you some questions about your fields and terminators for the format file and then will ask you to provide a name:

Enter the file storage type of field ID [int-null]:
Enter prefix-length of field ID [1]:
Enter field terminator [none]:

[multiple iterations]

Enter the file storage type of field Fnct_ID [int-null]:
Enter prefix-length of field Fnct_ID [1]:
Enter field terminator [none]: ;

Do you want to save this format information in a file? [Y/n] y
Host filename [bcp.fmt]: C:\temp\huge_data.fmt

Starting copy...

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

You now have a format file for your target table which can be used in the bcp import.

The format file will look a bit like this one:

12.0
8
1       SQLINT              1       4       ";"    1     ID                   ""
2       SQLNCHAR            2       200     ";"    2     Question             Latin1_General_CS_AS
3       SQLNCHAR            2       200     ";"    3     Link                 Latin1_General_CS_AS
4       SQLNCHAR            2       100     ";"    4     Tbl                  Latin1_General_CS_AS
5       SQLNCHAR            2       2000    ";"    5     Info                 Latin1_General_CS_AS
6       SQLINT              1       4       ";"    6     Tbl_ID               ""
7       SQLINT              1       4       ";"    7     Src_ID               ""
8       SQLINT              1       4       ";"    8     Fnct_ID              ""

This is just a basic primer on how to use the bcp utility to import data from a file.

Answer

  1. Create a format file (*.fmt) for your target table usign bcp
  2. Import the data using the -F and -L parameters
  3. Check the import

References

In absence of a SQL Server version in your question I referenced the SQL Server 2012 documentation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.