Required SQL statement (3) data insertion and SQL statement

Required SQL statement (3) data insertion and SQL statement

1. Small specifications for using insert statements

1) it is best to add N before a Chinese String
2) The column name is expanded with brackets like this [column name]
 
2. Conventional writing

Insert into tableName
([Column1], [column2])
Values
(N 'Chinese', '11ds ')
 
3. Insert multiple rows into one statement

Insert into Table Name ([Column 1], [Column 2])
Select 'value 1', 'value 2' union all -- here, the difference between union and union all
-- It is mainly for processing repeated values. union filters out duplicate rows, while union all inserts all rows.
Select 'value 3', 'value 4' union
Select 'value 5', 'value 6'
 
4. Copy data from the original table to a new table that does not exist.

Select * into newtable from oldtable
-- How to copy only the table structure?
Select * into newtable from oldtable where 1 <> 1
Select top 0 * into newtable from oldtable
 
5. Insert data from other tables to an existing table and copy data from other tables.

Insert into tablename (column, column2)
Select column, column2 from oldtable
 
6. Force write to forcibly write the ID field.

-- We cannot input values for columns with auto-increment settings by default.
-- Use the following statement to forcibly write data.
 
-- 1) enable add (remove the restriction)
Set indentity_insert tablename On
-- 2) You can manually insert an id.
Insert into indicates (id, name) values ('20170901', 'sophomore ')
-- 3) Disable manual insertion
Set indentity_insert tablename off