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