What is Bulk Insert in sql?

Bulk insert is very similar to BCP command but we can not do export with the command. The major difference between BCP and Bulk Insert:-

Bulk Insert runs in the same process of SQL Server, so it can avail to all performance benefits of SQL Server.

You can define Bulk insert as part of transaction. That means you can use the Bulk
Insert command in BEGIN TRANS and COMMIT TRANS statements.

Below is a detailed syntax of BULK INSERT. You can run this from “SQL Server
Management Studio”, TSQL or ISQL.


BULK INSERT [[‘database_name‘.][‘owner‘].]
{‘table_name‘ | ‘view_name‘ FROM ‘data_file’ }
[WITH (
[BATCHSIZE [ = batch_size ]]
[[,] CHECK_CONSTRAINTS ]
[[,] CODEPAGE [ = ‘ACP’ | ‘OEM’ | ‘RAW’ | ‘code_page’ ]]
[[,] DATAFILETYPE [ = {‘char’|’native’|
’widechar’|’widenative’ }]]
[[,] FIELDTERMINATOR [ = ‘field_terminator’ ]]
[[,] FIRSTROW [ = first_row ]]
[[,] FIRETRIGGERS [ = fire_triggers ]]
[[,] FORMATFILE [ = ‘format_file_path’ ]]
[[,] KEEPIDENTITY ]
[[,] KEEPNULLS ]
[[,] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ]]
[[,] LASTROW [ = last_row ]]
[[,] MAXERRORS [ = max_errors ]]
[[,] ORDER ( { column [ ASC | DESC ]}[ ,…n ])]
[[,] ROWS_PER_BATCH [ = rows_per_batch ]]
[[,] ROWTERMINATOR [ = ‘row_terminator’ ]]
[[,] TABLOCK ]
)]

Below is a simplified version of bulk insert which we have used to import a comma
separated file in to “SalesPersonDummy”. The first row is the column name so we specified
start importing from the second row. The other two attributes define how the fields and
rows are separated.
bulk insert adventureworks.sales.salespersondummy from 'c:\salesperson.txt' with
(
FIRSTROW=2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Is that Bulk Insert available in MYSQL?

__________________
-Murali..

yeah it is possible to do bulk insert in MYSQL

Hi Venkat,

Can u provide me the syntax to use bulk insert?, it will be more helpful for me?

__________________
-Murali..

May be here are the two ways of inserting bulk data to the tables in MYSQL.

INSERT INTO ... SELECT statement;

INSERT INTO table1(id,name) VALUES (1,'test'),(2,'test2'),(3,'test3');

__________________
-Murali..

Does we have bulk delete in sql ?

Hi,

Delete Command with where CLAUSE will delete specified records,
but Delete Command without where CLAUSE will delete all rows from the table.

For Ex:
DELETE FROM Table Where <Condition>;
DELETE FROM Table;

If u want to delete all the datas permanently from the table use TRUNCATE Command.

May be this helps u in the
Difference between DELETE and TRUNCATE commands.

Did u mean the Bulk Delete insense of deleting all rows from the table?

__________________
-Murali..