Sql-server – Bulk insert top 1000 rows from a big flat file

bcpbulk-insertsql server

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?

Best Answer

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.

Related Question