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 import data from your huge data file using Microsoft SQL Server's bcp utility from the command prompt.
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
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:
bulk copy program
The table you will be importing your data to
The name of the database you are connecting to
The full pathname and filename to your huge file (e.g. C:\temp\bigfile.txt)
The name of the server you are connecting to
The first line to import
The last line to import
The SQL Server Login (alternatively you can use -T for a trusted connection)
The password for the SQL Server Login
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 : Enter field terminator [none]: [multiple iterations] Enter the file storage type of field Fnct_ID [int-null]: Enter prefix-length of field Fnct_ID : 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.
- Create a format file (*.fmt) for your target table usign bcp
- Import the data using the -F and -L parameters
- Check the import
- 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.