Database update SQL Server script summary _mssql

Table replication:
1. INSERT into SELECT statement
Statement form: Insert into Table2 (field1,field2,...) Select Value1,value2,... from Table1
The target table Table2 must exist, and because the target table Table2 already exists, we can insert a constant in addition to the fields Table1 the source table.
Examples are as follows:

Copy Code code as follows:

--Create a test table
Create TABLE Users1
(
UserID int Identity (1,1) primary key NOT NULL,
UserName varchar (10),
UserAddress varchar (20)
)
Go
Create TABLE Users2
(
ID int Identity (1,1) primary key NOT NULL,
Name varchar (10),
Address varchar (20)
)
Go
--Create test data
Insert into Users1 values (' Zhao ', ' ASDs ')
Insert into Users1 values (' money ', ' ASDs ')
Insert into Users1 values (' Sun ', ' ASDs ')
Insert into Users1 values (' Lee ', ' ASDs ')
Go
SELECT * FROM Users2
--insert into a SELECT statement to replicate table data
Insert into Users2 (name,address) Select username,useraddress from Users1
Go
--Displays the updated results
SELECT * FROM Users2
Go
--Delete test table
Drop TABLE Users1
Drop TABLE Users2

2. SELECT into from statement
Statement form: SELECT vale1, value2 into Table2 from Table1
The target table Table2 is not present because the table Table2 is created automatically at insert time and the specified field data in Table1 is copied to Table2
Examples are as follows:

Copy Code code as follows:

--Create a test table
Create TABLE Users1
(
UserID int Identity (1,1) primary key NOT NULL,
UserName varchar (10),
UserAddress varchar (20)
)
Go
--Create test data
Insert into Users1 values (' Zhao ', ' ASDs ')
Insert into Users1 values (' money ', ' ASDs ')
Insert into Users1 values (' Sun ', ' ASDs ')
Insert into Users1 values (' Lee ', ' ASDs ')
Go
--select into from statement creates a table Users2 and copies data
Select Username,useraddress into Users2 from Users1
Go
--Displays the results before and after the update
SELECT * FROM Users1
SELECT * FROM Users2
Go
--Delete test table
Drop TABLE Users1
Drop TABLE Users2

Table changes:
3.ALTER TABLE Statement
The ALTER table statement is used to add, modify, or delete columns in an existing table.
The form of the statement is:

Copy Code code as follows:

To add a column to a table:
ALTER TABLE table_name
ADD column_name datatype
Delete columns in a table
ALTER TABLE table_name
DROP COLUMN column_name
Change the data type of a column in a table
ALTER TABLE table_name
ALTER COLUMN column_name datatype

Examples are as follows:

Copy Code code as follows:

--Create a test table
Create TABLE Users
(
UserID int Identity (1,1) primary key NOT NULL,
UserName varchar (10),
UserAddress varchar (20)
)
Go
--Add a new column data type named "Birthday" to the Users table as DateTime
ALTER TABLE Users ADD birthday datetime
Go
--Change the data type of the "Birthday" column in the Users table to nvarchar (20)
Alter TABLE Users alter COLUMN birthday nvarchar (20)
Go
--Delete the "Birthday" column in the person table:
ALTER TABLE Users DROP COLUMN Birthday
Go
--Delete test table
Drop TABLE Users

Using sp_rename stored procedures [SQLCE not supported]
sp_rename stored procedures can modify the names of user objects in the current database, such as tables, columns, indexes, and stored procedure waits. But under SqlCe, the test can only change the table name.
The syntax is as follows:
sp_rename[@objname =] ' object_name ',
[@newname =] ' new_name '
[, [@objtype =] ' object_type ']
where [@objtype =] ' object_type ' is the type of object to be renamed, and its value can be thought
Column in ' column '
' Database ' databases
' Index ' indexes
' Userdatatype ' user-defined type
' Object '
Value ' object ' refers to all objects in the system table sysobjects, such as tables, views, stored procedures, triggers, rules, constraints, and so on. The ' object ' value is the default value.
Examples are as follows:

Copy Code code as follows:

Example 1: Change the column p_id name of the Orders table called products_id
exec sp_rename ' orders. [p_id] ', ' product_id ', ' column '
Example 2: Change the name of the Orders table to P_orders
exec sp_rename ' orders ', ' p_orders '