sql statement insert statement

1. Standardize some small specifications for using insert statements

1) It is best to add an N before the Chinese string

2) Expand the column name with square brackets Like this [Column name]

2. Conventional writing

Insert into tableName

([column1], [column2])

values

(N'Chinese','11ds')

3. Multiple rows and one statement to insert multiple rows

insert into table name ( [Column 1], [Column 2])

                                                                                                                                                                                                                       ‐ ion will filter out Duplicate rows, and union all will be inserted in all

select 'value 3', 'value 4' union select 'value 5', 'value 6'

4. Copy the original table to the new table Copy the data in 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 into an existing table and copy data from other tables

insert into tablename(column,column2)

Select column,column2 from oldtable

6. Force write Force write to the identification field.

--For columns that have been set to auto-grow, we cannot enter values ​​for them by default.

--You can use the following statement to force writing.

--1) Turn on adding, (remove restrictions on adding)

Set indentity_insert tablename On

--2) You can manually insert the id

insert into indicates (id, name) values ​​('1002', 'Sophomore')

--3) Turn off manual insertion

Set indentity_insert tablename off