修改sql表，增加一列并保持原数据不变

最新推荐文章于 2024-03-25 22:52:29 发布

城市判官

最新推荐文章于 2024-03-25 22:52:29 发布

阅读量1.8k

点赞数

分类专栏： DB

本文链接：https://blog.csdn.net/YangangwuWuyangang/article/details/49304389

版权

DB 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

--edit by wyg
print('开始修改考勤签到设置表')
GO
print('开始创建考勤签到设置表yj_attendance_set1')
GO
CREATE TABLE [dbo].[yj_attendance_set1](
[itemid] [int] IDENTITY(1,1) NOT NULL,
[twodayoffs] [bit] NULL,
[morningtime] [nvarchar](30) NULL,
[morningaftertime] [nvarchar](30) NULL,
[afternoontime] [nvarchar](30) NULL,
[afternoonaftertime] [nvarchar](30) NULL,
[nighttime] [nvarchar](30) NULL,
[nightaftertime] [nvarchar](30) NULL,
[latestart] [int] NULL,
[leaveearlystart] [int] NULL,
[lateend] [int] NULL,
[leaveearlyend] [int] NULL,
[beforework] [int] NULL,
[afterwork] [int] NULL,
[ignoreminutes] [int] NULL,
[morningprohibit] [char](5) NULL,
[afternoonprohibit] [char](5) NULL,
[nightprohibit] [char](5) NULL,
[morningaftlastime] [nvarchar](30) NULL,
[afternoonaftlastime] [nvarchar](30) NULL,
[nightaftlastime] [nvarchar](30) NULL,
CONSTRAINT [PK_yj_attendance_set1] PRIMARY KEY CLUSTERED
(
[itemid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
print('创建考勤签到设置表yj_attendance_set1 结束')
GO
print('开始将表yj_attendance_set的数据导入表yj_attendance_set1')
GO
insert into [yj_attendance_set1]
select
    [twodayoffs]
      ,[morningtime]
      ,[morningaftertime]
      ,[afternoontime]
      ,[afternoonaftertime]
      ,[nighttime]
      ,[nightaftertime]
      ,[latestart]
      ,[leaveearlystart]
      ,[lateend]
      ,[leaveearlyend]
      ,[beforework]
      ,[afterwork]
      ,[ignoreminutes]
      ,[morningprohibit]
      ,[afternoonprohibit]
      ,[nightprohibit]
      ,[morningaftlastime]
      ,[afternoonaftlastime]
      ,[nightaftlastime]
       from yj_attendance_set
       GO
print('将表yj_attendance_set的数据导入表yj_attendance_set1 结束')
GO
print(' 开始删除表yj_attendance_set')
drop table yj_attendance_set
GO
print(' 删除表yj_attendance_set 结束')
Go
print('开始修改表yj_attendance_set1名称')
Go
EXEC sp_rename yj_attendance_set1, yj_attendance_set
Go
print('开始修改表yj_attendance_set,添加userid一列')
Go
alter table dbo.yj_attendance_set add [userid] [int] NULL
GO
print('修改表yj_attendance_set,添加userid一列结束')
Go
print('修改表yj_attendance_set1名称结束')
GO
print('修改结束')