How to use" insert bulk " to insert data for a table

Question

How to use" insert bulk " to insert data for a table

892717952

SSC Eights!

Points: 843
More actions
November 6, 2020 at 4:42 am

#3804831

I used SQL profile to trace SQL Statement and found user use insert bulk to insert data for a table, but the sql statment is too long (over 4000) I just traced the first part of the below sql stament, if I use insert buck to insert data for a table, how to use it? could you please give me an example? and what is the advantage of "insert bulk" ? I just know "BULK INSERT" to insert data. thanks!
insert bulk tb1( ft1 varchar(20), ft2 varchar(20), ft3 varchar(20))

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

Here's a very thorough article on BULK INSERT. In general, it can be faster for large data moves. It may use less locking. It may use fewer log resources. You can read all the details in the article. I would just emphasize that testing is key here.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

892717952 SSC Eights! Points: 843 More actions · Answer 2

Grant Fritchey wrote:

Here's a very thorough article on BULK INSERT. In general, it can be faster for large data moves. It may use less locking. It may use fewer log resources. You can read all the details in the article. I would just emphasize that testing is key here.

thank you for your time!

I know how to use bulk insert. but my question is how to use "insert bulk" , I used SQL profiler and found sql statement using insert bulk into insert data for a table, as that sql statment is too long, and over 4000 characters, I didn't the whole sql statement, so I don't know how to use insert bulk to insert data for a table, thanks!

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

INSERT BULK is from .Net SqlBulkCopy class, so someone has coded an application which is using that class to load in some data

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?redirectedfrom=MSDN&view=dotnet-plat-ext-3.1

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

892717952 wrote:

I used SQL profile to trace SQL Statement and found user use insert bulk to insert data for a table, but the sql statment is too long (over 4000) I just traced the first part of the below sql stament, if I use insert buck to insert data for a table, how to use it? could you please give me an example? and what is the advantage of "insert bulk" ? I just know "BULK INSERT" to insert data. thanks!
insert bulk tb1( ft1 varchar(20), ft2 varchar(20), ft3 varchar(20))

"Insert Bulk" can actually be a huge problem. If "Fast Insert" is enabled on the server and the front-end (or other application) is sending single row inserts that resolve to "Insert Bulk", you're going to end up with a huge amount of "Allocated but unused" space in your MDF file(s). The reason is that "Fast Insert" is fast because it allocates only full Extents and it does so without looking to see if there are any free Extents or Pages that it could use instead. What ends up happening is that you have a full Extent allocated for each and every single row insert that occurred under "Insert Bulk". To say it with more impact, if you save a row that contains just one byte, SQL Server will allocate 8 pages (1 extent) and that 64K bytes allocated to save 1 byte.

MS actually recommends that you turn on the global trace flag that disables "Fast Insert" if you have such a situation.

For more information, please lookup "692" on the page at the following link.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15

"Insert Bulk" isn't actually as rare as a lot of people think. A lot of "monitoring" software uses it. I know that because the database for our monitoring software exploded with "allocated but unused" space to the tune of 8GB on a database that only contained 2GB of data for a total of 10GB where 2GB should have been the right number.

This can also happen when doing "Minimal Logging". We initially didn't notice it because of the 2TB size of the database but when you get to 4 or 5% "allocated but unused" size on such a database, you finally have to do something about it.

Most people don't notice the problem because they're doing index maintenance like crazy. That's actually another much longer story.

My recommendation is that, unless your server instance is a rather dedicated ETL/ELT/IMPORT instance, you should test performance with it on and with it off and, if there's really not much difference, then turn it off and leave it off. On mixed use machines, I just turn it off for the reasons previously discussed.

Also, if you think that Bulk Insert is limited to only 4KB of code, then you've done something wrong especially if it's dynamic SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

892717952 SSC Eights! Points: 843 More actions · Answer 5

Ant-Green wrote:

INSERT BULK is from .Net SqlBulkCopy class, so someone has coded an application which is using that class to load in some data

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?redirectedfrom=MSDN&view=dotnet-plat-ext-3.1%5B/quote%5D

Thank you, this article of the link seems that it introduces bulk insert, not insert bulk, thanks!

892717952 SSC Eights! Points: 843 More actions · Answer 6

Jeff Moden wrote:

892717952 wrote:
I used SQL profile to trace SQL Statement and found user use insert bulk to insert data for a table, but the sql statment is too long (over 4000) I just traced the first part of the below sql stament, if I use insert buck to insert data for a table, how to use it? could you please give me an example? and what is the advantage of "insert bulk" ? I just know "BULK INSERT" to insert data. thanks!
insert bulk tb1( ft1 varchar(20), ft2 varchar(20), ft3 varchar(20))
"Insert Bulk" can actually be a huge problem. If "Fast Insert" is enabled on the server and the front-end (or other application) is sending single row inserts that resolve to "Insert Bulk", you're going to end up with a huge amount of "Allocated but unused" space in your MDF file(s). The reason is that "Fast Insert" is fast because it allocates only full Extents and it does so without looking to see if there are any free Extents or Pages that it could use instead. What ends up happening is that you have a full Extent allocated for each and every single row insert that occurred under "Insert Bulk". To say it with more impact, if you save a row that contains just one byte, SQL Server will allocate 8 pages (1 extent) and that 64K bytes allocated to save 1 byte.
MS actually recommends that you turn on the global trace flag that disables "Fast Insert" if you have such a situation.
For more information, please lookup "692" on the page at the following link.
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15
"Insert Bulk" isn't actually as rare as a lot of people think. A lot of "monitoring" software uses it. I know that because the database for our monitoring software exploded with "allocated but unused" space to the tune of 8GB on a database that only contained 2GB of data for a total of 10GB where 2GB should have been the right number.
This can also happen when doing "Minimal Logging". We initially didn't notice it because of the 2TB size of the database but when you get to 4 or 5% "allocated but unused" size on such a database, you finally have to do something about it.
Most people don't notice the problem because they're doing index maintenance like crazy. That's actually another much longer story.
My recommendation is that, unless your server instance is a rather dedicated ETL/ELT/IMPORT instance, you should test performance with it on and with it off and, if there's really not much difference, then turn it off and leave it off. On mixed use machines, I just turn it off for the reasons previously discussed.
Also, if you think that Bulk Insert is limited to only 4KB of code, then you've done something wrong especially if it's dynamic SQL.

Thank you for your kind help!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

You're welcome and thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)