SQL Server simple example

Example 1: Create a table and insert 100 records (idintprimarykeynotnullidentity (10000,100), tea_namevarchar (50), tea_ageintdefault20) into the table through the Stored Procedure @ tea_namevarchar (5

Example 1: create a table and insert 100 records to the table through the stored procedure: create table teachers (id int primary key not null identity (10000,100), tea_name varchar (50 ), tea_age int default 20) create procedure insert1000teachers as declare @ tea_name varchar (5

Example
1. Create a table and insert 100 records to the table through the stored procedure.
Create table teachers (id int primary key not null identity (10000,100), tea_name varchar (50), tea_age int default 20)
Create procedure insert1000teachers
As
Declare @ tea_name varchar (50)
Declare @ tea_age int
Declare @ index int
Declare @ count int
Set @ index = 1
Set @ count = 100
While @ index <@ count
Begin
Set @ tea_age = 30 * rand ()
Set @ tea_name = 'teacher'
Set @ tea_name = @ tea_name + convert (varchar, @ index)
Insert into teachers (tea_name, tea_age)
Values (@ tea_name, @ tea_age)
Set @ index = @ index + 1
End
2. Create a database
Create database test1
On
Primary
(
Name = t1_dat,
Filename = 'd:/test_database/1/t1_dat.mdf ',
Size = 1,
Filegrowth = 15%,
Maxsize = 50
),
(
Name = t2_dat,
Filename = 'd:/test_database/1/t2_dat.ndf ',
Size = 1,
Maxsize = UNLIMITED,
Filegrowth = 1
)
Log on
(
Name = t_log,
Filename = 'd:/test_database/1/t_log.ldf ',
Size = 1,
Maxsize = UNLIMITED,
Filegrowth = 1
)
3. Add a data file to the specified database.
Alter database test1
Add
File
(
Name = t3_dat,
Filename = 'd:/test_database/1/t3_dat.ndf ',
Size = 1,
Maxsize = UNLIMITED,
Filegrowth = 1
)
4. Add a group
Alter database test1
Add
Filegroup filegroup1
5. Add a data file to the specified group.
Alter database test1
Add file
(
Name = t4_dat,
Filename = 'd:/test_database/1/t4_dat.ndf'
)
To filegroup filegroup1
6. Rename an object
Change Field name: sp_rename 'Teachers. tea_name ', 'tea _ name1', 'column'
Change table name: sp_rename 'dbo. Teachers', 'teachers1'
7. view the Stored Procedure content:
Sp_helptext insert1000teachers
8. Stored Procedure Implementation: find the name of the stored procedure based on the content in the Stored Procedure
Create procedure getProcedureByBodyWords
@ Patpro varchar (100), -- any content in the Stored Procedure
@ Proname varchar (50) -- Part or full name of the stored procedure, or null
As
Begin
Select name from sysobjects where id in
(Select id from syscomments where text like '%' + @ patpro + '% ')
And name like case when @ proname is null then '% 'else' %' + @ proname + '%' end
End
If the stored procedure is named insert1000teachers, you can obtain the full name as follows:
Exec getProcedureByBodyWords 'insert', 'insert1000'
9. Use a trigger to back up data in the data table:
Create a test data table:
One is used to insert data, test3
Another backup, test3_bak
Create table test3 (id int primary key not null identity (1, 1), uname varchar (20), uage int );
Create table test3_bak (id int primary key not null identity (1, 1), bid int, uname varchar (20), uage int, active char (1 ));

The backup trigger is triggered only when the backup is updated or inserted.
Alter trigger test3_bak_insert_update
On test3
For insert, update
As
Declare @ id int
Declare @ uname varchar (20)
Declare @ uage int
Begin
Select @ id = id, @ uname = uname, @ uage = uage from inserted
If @ id <> 0
Begin
Update test3_bak set active = '0' where bid = @ id
Insert into test3_bak (bid, uname, uage, active)
Values (@ id, @ uname, @ uage, '1 ')
End
End
Test data:
Insert into test3 (uname, uage) values ('flb', 20)
Insert into test3 (uname, uage) values ('flb1 ', 21)
Insert into test3 (uname, uage) values ('flb2', 22)

Update test3 set uage = 100 where id = 27
Delete from test3 where id = 20

You can use the following query to trace the data changes of the two tables:
Select * from test3
Select * from test3_bak

Feng Libin's blog