Bulk insert top 1000 rows from a big flat file 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?
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:\&gt; 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


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


References


bcp Utility (Microsoft Technet)  
Create a Format File (SQL Server) (Microsoft Technet)  
Use a Format File to Map Table Columns to Data-File Fields (SQL Server) (Microsoft Technet)



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