updatedemo8.sql

来自「T-SQL示例大全」· SQL 代码 · 共 50 行

/* 文件名称: UpdateDemo8.sql */
USE 北风贸易
GO

SET NOCOUNT ON
GO

EXEC sp_dboption '北风贸易','select into/bulkcopy','TRUE'

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME = 'MyFoxman')

   DROP TABLE MyFoxMan

GO

-- 新建立一个数据表 MyFoxman
SELECT * INTO MyFoxman
    FROM 章立民工作室

-- 声明变量
DECLARE @vlToday datetime,
                  @vlAvgSalary money,
                  @vlDepartment nvarchar(10),
                  @vlName nvarchar(10),
                  @vlSalary money        

-- 更新字段与变量
UPDATE MyFoxman
   SET @vlToday = GETDATE(),
          @vlAvgSalary = (SELECT AVG(目前薪资) FROM MyFoxman),
          @vlDepartment = 部门,
          @vlName = 姓名 = '章立民',
          @vlSalary = 目前薪资 = @vlAvgSalary * 2
   WHERE 姓名 = '章立民' AND
                 地址 LIKE '%长安街%'
      
SELECT '@vlToday         = ',@vlToday
SELECT '@vlAvgSalary     = ',@vlAvgSalary
SELECT '@vlAvgSalary * 2 = ',@vlAvgSalary * 2
SELECT '@vlDepartment    = ',@vlDepartment
SELECT '@vlName          = ',@vlName
SELECT '@vlSalary        = ',@vlSalary

SELECT 姓名,部门,目前薪资,地址
  FROM MyFoxman
  WHERE 姓名 = @vlName AND 部门 = @vlDepartment 

EXEC sp_dboption '北风贸易','select into/bulkcopy','FALSE'