As some of you may know, the almighty Microsoft Azure has some flaws. One of them is not being able to do a Bulk Insert in Azure SQL.
Now there are some options on how to resolve this.
- Start inserting them one by one… good luck with a 1000 records.
- My way: BCP or Bulk Copy Program.
There are only 3 simple steps you need to do for Bulk Inserting in Azure SQL:
1) Bulk Insert your data into a local MSSQL database.
2) Copy data to local file.
- Databasename where you’ve bulk inserted your data: <databasename>
- Tablename you want copy: <tablename>
- Local folder to copy to: <localfolder>
- Filename you want it to have:<filename>
Open command prompt and enter the following line of code:
BCP <databasename>.dbo.<tablename> OUT <localfolder>\<filename>.txt -T -c
-T: Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.
-c: Performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \r\n(newline character) as the row terminator. -c is not compatible with -w.
3) Bulk insert data into Azure SQL
- Databasename where you want to Bulk Insert: <databasename>
- Tablename where you want to insert: <tablename>
- Local folder where the file is in: <localfolder>
- Filename of the file you want to copy: <filename>
- Servername (example: azerty.database.windows.net): <servername>
- Username you have on that server: <username>
- Password of the user on that server: <password>
BCP <databasename>.dbo.<tablename> IN <localfolder>\<filename>.txt -S <servername>; -U <username>@<servername> -P <password> -c
This will copy your file into Azure SQL with a max of 1000 records at a time.
-S: Specifies the instance of SQL Server to which to connect. If no server is specified, the bcp utility connects to the default instance of SQL Server on the local computer. This option is required when a bcp command is run from a remote computer on the network or a local named instance. To connect to the default instance of SQL Server on a server, specify only server_name. To connect to a named instance of SQL Server, specify server_name\instance_name.
-U: Specifies the login ID used to connect to SQL Server.
-P: Specifies the password for the login ID. If this option is not used, the bcp command prompts for a password. If this option is used at the end of the command prompt without a password, bcp uses the default password (NULL).