SQL Table association assignment, system table, table data Delete

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