Summary of SQL server script update for databases. For more information, see.
Summary of SQL server script update for databases. For more information, see.
Table replication:
1. insert into select statement
Statement format: Insert into Table2 (field1, field2,...) select value1, value2,... from Table1
The target table Table2 must exist. Because the target table Table2 already exists, We can insert constants in addition to the fields in the source table Table1.
Example:
The Code is 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 ('lil', 'asds ')
GO
Select * from Users2
-- Insert into select statement to copy table data
Insert into Users2 (Name, Address) select UserName, UserAddress from Users1
GO
-- Display updated results
Select * from Users2
GO
-- Delete the test table
Drop TABLE Users1
Drop TABLE Users2
2. select into from statement
Statement format: SELECT vale1, value2 into Table2 from Table1
The target table Table2 does not exist because table Table2 is automatically created during insertion and the specified field data in Table1 is copied to Table2.
Example:
The Code is 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 ('lil', 'asds ')
GO
-- Select into from statement create table Users2 and copy data
Select UserName, UserAddress INTO Users2 from Users1
GO
-- Display the results before and after the update
Select * from Users1
Select * from Users2
GO
-- Delete the 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.
Statement format:
The Code is as follows:
Add columns to the 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 columns in a table
Alter table table_name
Alter column column_name datatype
Example:
The Code is 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 named "Birthday" to the Users table. The data type is datetime.
Alter table Users ADD Birthday datetime
GO
-- Change the data type of the "Birthday" column to nvarchar (20) in the Users table)
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 the test table
Drop TABLE Users
Use Sp_rename stored procedure [SQLCE not supported]
The Sp_rename stored procedure can modify the names of user objects in the current database, such as tables, columns, indexes, and stored procedure waiting. However, you can only change the table name in the SqlCe test.
Syntax:
Sp_rename [@ objname =] 'object _ name ',
[@ Newname =] 'new _ name'
[, [@ Objtype =] 'object _ type']
[@ Objtype =] 'object _ type' is the type of the object to be renamed, and its value can be
'Column 'Column
'Database' Database
'Index' Index
'Userype ype 'user-defined type
'Object' Object
The value 'object' indicates all objects in the system table sysobjects, such as tables, views, stored procedures, triggers, rules, and constraints. The 'object' value is the default value.
Example:
The Code is as follows:
Example 1: Change the column p_id of the orders table to products_id.
Exec sp_rename 'Orders. [p_id] ', 'product _ id', 'column'
Example 2: Change the orders table name to p_orders.
Exec sp_rename 'Orders ', 'P _ orders'
