SQL Bulk operations (presence of updates, non-existent inserts)

Tags: sql2012-09-06 18:13 2408 People read comments (0) favorite reports Classification:SQL Server (123)

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

[SQL]View PlainCopy

2. Update a SET a.name=
4. (
6. Select B.NAME from #tmpTB2 b where b.id=a.id
8. )
10. From #tmpTB3 A
14. Insert INTO #tmpTB3
16. SELECT * from #tmpTB2 B
18. where b.ID not in (select ID from #tmpTB3)

The above are: existing updates, non-existent inserts

The following is a batch update of an identity field:

[SQL]View PlainCopy

2. UPDATE A SET a.provider_isactive=
4. (
6. SELECT case
8. (
10. SELECT 1 from
12. (
14. SELECT DISTINCT provider_system_provider_id
16. From Your_db_name. [dbo]. [Your_table_name2] P
18. WHERE p.data_source = ' Ika ' and soft_delete_flag = ' n ' and provider_active_flag = ' y ' /c6>
20. ) B WHERE b.provider_system_provider_id = left (a.external_provider_id,7)
22. ) is NULL and then 0 ELSE 1 END as provider_isactive
24. )
26. From Your_db_name. [dbo]. [Your_table_name1] A
30. --SELECT COUNT (*) from Your_db_name. [dbo]. [Your_table_name1] A WHERE provider_isactive=0

SQL Bulk operations (presence of updates, non-existent inserts)