1. table-to-Table association assignment (for tables and tables with associated fields, data transfer)
Two-table associative assignment
UPDATE #B SET #B. d= #A. B from #B inner joins #A on #B. c= #A. A
Multi-Table Associative assignment
Update a set A.E=C.N from a LEFT join B on A.E=B.J left join C on B.K=C.M
2. Two ways to delete (TRUNCATE TABLE <--> Delete from)
TRUNCATE TABLE #USER
Delete extremely fast, no log so unrecoverable, and can only be used to delete all table data
DELETE from #USER WHERE ...
Delete generates log information that can be used for data recovery with additional conditions for selective deletion
3. Building SQL query statements using system table data
Querying table fields and field types
SELECT name,system_type_id from SYS. COLUMNS WHERE object_id=object_id(n ' cost details ', n ' U ')
Construct SQL query statements to implement the specification of fields
SELECT ' (case when IsNull (b. ' +name+ ', ' "+ (case system_type_id If 167 then" when 108 then ' 0 ' and ' 1899-12-30 ' ELSE ') ' End + ') = ' + ' + (case system_type_id If 167 then "when 108 then ' 0 ' when" Then ' 1899-12-30 ' ELSE ' END) + "then ' + (case system_type_id when 167 and then '-' when 108 then ' 0 ' when the ' 1899-12-30 ' when "Then ' 1899-12-30 ' ELSE '- ' End ' + ' else B. ' +name+ ' end ' as ' +name+ ', ' from SYS. COLUMNS
WHERE object_id=object_id(n ' Inpatient fee details ', n ' U ')
SQL Table association assignment, system table, table data Delete