SQL Bulk Delete and BULK insert

Bulk Delete:

DELETE from MyTable WHERE ID in (;

BULK INSERT:

INSERT into MyTable (id,name) VALUES (1, ' 123 ');
INSERT into MyTable (id,name) VALUES (2, ' 456 ');
INSERT into MyTable (id,name) VALUES (3, ' 789 ');

The second method uses union ALL to insert the operation:
INSERT into MyTable (id,name)
SELECT 4, ' 000 '
UNION All
SELECT 5, ' 001 '
UNION All
SELECT 6, ' 002 ';
It's said to be faster than the first!

The third method of
INSERT into MyTable (id,name) VALUES (7, ' 003 '), (8, ' 004 '), (9, ' 005 ');

Example:

Table: Leafjob (
Leafnum INT not NULL PRIMARY KEY,
Machine VARCHAR (15));

Delete: Delete from leafjob where Leafnum in (1,2,4);

Insert:

INSERT into Leafjob (leafnum, machine) VALUES (1, ' r1leaf3 '), (2, ' r1leaf22 ');

Insert into Leafjob (leafnum, machine) Select 4, ' The ' union ALL select 1, ' R1LEAF3 ' union ALL Select 2, ' R1leaf22 ';

Insert into Leafjob (leafnum, machine) Select 1, ' R1LEAF3 ' union select 2, ' R1leaf22 ';

Note: Performance issues require specific testing. Example tested under MySQL, version:4.1.20

Delete data in column A of table a equals 1

The code is as follows	Copy Code
Delete from a where exists (Select 1 from a where a=1)

The above method only applies to the simple small data volume of bulk data deletion, if it is a large number of data removal we can refer to the following methods

The code is as follows

Copy Code

Create PROCEDURE Batch_delete @TableName nvarchar (100),--table name @FieldName nvarchar (100),--delete field name @DelCharIndexID nvarchar (1000) As DECLARE @PointerPrev int DECLARE @PointerCurr int DECLARE @TId NVARCHAR, @sql NVARCHAR (1000) Set @PointerPrev = 1 while (@PointerPrev < LEN (@DelCharIndexID)) Begin Set @PointerCurr = CharIndex (', ', @DelCharIndexID, @PointerPrev) if (@PointerCurr >0) Begin SET @TId = cast (SUBSTRING (@DelCharIndexID, @PointerPrev, @PointerCurr-@PointerPrev) as NVARCHAR (50)) SET @sql = ' Delete from ' + @TableName + ' Where ' + @FieldName + ' = ' + @TID + ' Exec (@Sql) Print (' ======= ' [email protected]+ ' =======sql ' [email protected]) SET @PointerPrev = @PointerCurr + 1 Print (@PointerPrev) End Else Begin Print (' Break ') Break End End --delete the last one because there is no comma behind the last one, so jump out of the loop and need to be deleted SET @TId = cast (SUBSTRING (@DelCharIndexID, @PointerPrev, LEN (@DelCharIndexID)-@PointerPrev + 1) as NVARCHAR (50)) SET @sql = ' Delete from ' + @TableName + ' Where ' + @FieldName + ' = ' + @TID + ' Exec (@Sql) Print (' ======= ' [email protected]+ ' =======sql ' [email protected]) GO

SQL Bulk Delete and BULK insert