如何自动更新列然后在SQL Server中插入数据 [英] How can Automaticaly Update Column then Insert data in sql server
本文介绍了如何自动更新列然后在SQL Server中插入数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
USE [ddrr]
GO
/ * *****对象:表[dbo]。[ssse]脚本日期: 10/30/2013 08:45:43 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ dbo]。[ssse](
[Id] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[StartDte] [ date ] NULL ,
[Enddate] [ date ] NULL ,
[Statu] [ nvarchar ]( 50 ) NULL ,
CONSTRAINT [PK_ssse] PRIMARY KEY CLUSTERED
(
[ Id] 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
我正在插入数据并再次通过查询插入数据然后检查上一条记录状态列名称是活动还是非活动,然后状态列名称处于非活动状态然后插入数据其他方式不插入数据< br $>
自动更新状态列检查结束日期和当前日期,
结束日期可以小于当前日期然后状态列更新显示为非活动
解决方案
DECLARE @ previousstatus nvarchar ( 50 )
DECLARE @ presentstatus nvarchar ( 50 )
DECLARE @ Startdate nvarchar ( 50 )
DECLARE @ Enddate nvarchar ( 50 )
SET @ Startdate = getdate() - 7
SET @ Enddate = getdate() - 2
CREATE 表 #Temp(
[Id] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[StartDte] [ date ] ñ ULL ,
[Enddate] [ date ] NULL ,
[statu] [ nvarchar ]( 50 ) NULL ,
CONSTRAINT [PK_ssse] PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (PAD_INDEX = OFF ,STATISTICS_NORECOMPUTE = OFF ,IGNORE_DUP_KEY = OFF ,ALLOW_ROW_LOCKS = ON ,ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
选择 @ previousstatus = statu FROM #Temp 其中 ID =(选择 max(id)来自 #Temp)
IF @ Enddate < ; cast(getdate() as date )或 ISNULL( @ previousstatus ,' ' )= ' '
SET @ presentstatus = ' 无效'
ELSE
SET @ presentstatus = ' 有效'
IF @ previousstatus = ' 无效' 或 ISNULL( @ previousstatu s ,' ')= ' '
BEGIN
插入 INTO #Temp(StartDte,enddate,statu) VALUES ( @ Startdate , @ Enddate , @ presentstatus )
END
SELECT * FROM #Temp
Drop TABLE #Temp
USE [ddrr]
GO
/****** Object: Table [dbo].[ssse] Script Date: 10/30/2013 08:45:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ssse](
[Id] [int] IDENTITY(1,1) NOT NULL,
[StartDte] [date] NULL,
[Enddate] [date] NULL,
[Statu] [nvarchar](50) NULL,
CONSTRAINT [PK_ssse] PRIMARY KEY CLUSTERED
(
[Id] 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
I am Inserting the Data and Again Inserting data through Query then Check the previous record Status Column Name Is Active or Inactive, then Status Column Name is Inactive then Insert data other wise not insert data
Automatically Update Status Column check the end Date and Current date ,
End date can less then Current date then Status Column Update Show in Inactive
解决方案
DECLARE @previousstatus nvarchar(50) DECLARE @presentstatus nvarchar(50) DECLARE @Startdate nvarchar(50) DECLARE @Enddate nvarchar(50) SET @Startdate = getdate() - 7 SET @Enddate = getdate() - 2 CREATE TABLE #Temp( [Id] [int] IDENTITY(1,1) NOT NULL, [StartDte] [date] NULL, [Enddate] [date] NULL, [Statu] [nvarchar](50) NULL, CONSTRAINT [PK_ssse] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Select @previousstatus = statu FROM #Temp where ID = (Select max(id) From #Temp) IF @Enddate < cast(getdate() as date) or ISNULL(@previousstatus,'') = '' SET @presentstatus = 'Inactive' ELSE SET @presentstatus = 'Active' IF @previousstatus = 'Inactive' or ISNULL(@previousstatus,'') = '' BEGIN Insert INTO #Temp(StartDte,enddate,statu) VALUES(@Startdate,@Enddate,@presentstatus) END SELECT * FROM #Temp Drop TABLE #Temp
这篇关于如何自动更新列然后在SQL Server中插入数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文