动态创建表然后给该表插入数据这里有点麻烦,虽然没有错,但是也没创建插入成功望高手指点~~~~本人实属不擅长存储过程~
declare @a datetime --现在时间
declare @b datetime --前段时间
declare @c nvarchar(8)--前段备份时间
declare @d nvarchar(8)--现在备份时间
declare @tablename nvarchar(50)--新建备份表
declare @insertSql nvarchar(1000)--执行备份表
set @a = (select dateadd(mm,0,dateadd(dd,-(datepart(dd,getdate())-1),getdate()))) --本月时间
set @b = (select dateadd(mm,-3,dateadd(dd,-(datepart(dd,getdate())-1),getdate()))) --前三个月的时间
set @c = (select convert(char,dateadd(mm,-3,dateadd(dd,-(datepart(dd,getdate())-1),getdate())),112)) --前三个月的时间
set @d = (select convert(char,dateadd(mm,0,dateadd(dd,-(datepart(dd,getdate())-1),getdate())),112)) --本月时间
begin
set @tablename = 'sms_log_'+@c+'_'+@d --备份表表名 如sms_log_20070901_20071201
create table #tablename (log_id int not null,EntName varchar(50),type int,plan_id int,plan_detail_id int,subject nvarchar(20),CustomerID varchar(50),mobile varchar(20),name nvarchar(20),title nvarchar(10),cell varchar(20),mtype varchar(4),message nvarchar(200),msgid varchar(50) not null,send_time datetime,admin varchar(20),state varchar(5),gw varchar(5),status varchar(5),amount smallint) --创建备份表
insert into #tablename --备份数据
select log_id,EntName,type,plan_id,plan_detail_id,subject,CustomerID,mobile,name,title,cell,mtype,message,msgid,send_time,admin,state,gw,status,amount from sms_log where send_time > @b and send_time < @a
delete from sms_log where send_time > @b and send_time < @a --删除已备份数据
end
declare @a datetime --现在时间
declare @b datetime --前段时间
declare @c nvarchar(8)--前段备份时间
declare @d nvarchar(8)--现在备份时间
declare @tablename nvarchar(50)--新建备份表
declare @insertSql nvarchar(1000)--执行备份表
set @a = (select dateadd(mm,0,dateadd(dd,-(datepart(dd,getdate())-1),getdate()))) --本月时间
set @b = (select dateadd(mm,-3,dateadd(dd,-(datepart(dd,getdate())-1),getdate()))) --前三个月的时间
set @c = (select convert(char,dateadd(mm,-3,dateadd(dd,-(datepart(dd,getdate())-1),getdate())),112)) --前三个月的时间
set @d = (select convert(char,dateadd(mm,0,dateadd(dd,-(datepart(dd,getdate())-1),getdate())),112)) --本月时间
begin
set @tablename = 'sms_log_'+@c+'_'+@d --备份表表名 如sms_log_20070901_20071201
create table #tablename (log_id int not null,EntName varchar(50),type int,plan_id int,plan_detail_id int,subject nvarchar(20),CustomerID varchar(50),mobile varchar(20),name nvarchar(20),title nvarchar(10),cell varchar(20),mtype varchar(4),message nvarchar(200),msgid varchar(50) not null,send_time datetime,admin varchar(20),state varchar(5),gw varchar(5),status varchar(5),amount smallint) --创建备份表
insert into #tablename --备份数据
select log_id,EntName,type,plan_id,plan_detail_id,subject,CustomerID,mobile,name,title,cell,mtype,message,msgid,send_time,admin,state,gw,status,amount from sms_log where send_time > @b and send_time < @a
delete from sms_log where send_time > @b and send_time < @a --删除已备份数据
end
解决方案 »
- 如何才能成为一个高手程序员?
- 艾 在线再求一正则表达式 在线等谢谢
- ASP.NET insert插入语句问题
- 安装ajax.net后,部分css不起作用了,有人遇到过吗
- 谁有 asp.net 网络数据库开发实例精解 的电子版 代码 ?
- 求一个简单的正则表达式!!!
- 好烦的问题,受不了了!
- 一个有关showModalDialog的问题
- VS2005(SP1)所见即所得的调试
- <a href ="home.aspx?cn="><asp:Label ID="Label12" runat="server"></asp:Label></a>
- 用编辑器后一个涉及安全的问题,怎么解决?请大家帮忙。。。~~~~~~~~~~~~~~~~~~~~~
- 想求一个“无限级栏目分类”的实力!!!100分
declare @b datetime --前段时间
declare @c nvarchar(8)--前段备份时间
declare @d nvarchar(8)--现在备份时间
declare @tablename nvarchar(50)--新建备份表
declare @insertSql nvarchar(1000)--执行备份表
set @a = (select dateadd(mm,0,dateadd(dd,-(datepart(dd,getdate())-1),getdate()))) --本月时间
set @b = (select dateadd(mm,-3,dateadd(dd,-(datepart(dd,getdate())-1),getdate()))) --前三个月的时间
set @c = (select convert(char,dateadd(mm,-3,dateadd(dd,-(datepart(dd,getdate())-1),getdate())),112)) --前三个月的时间
set @d = (select convert(char,dateadd(mm,0,dateadd(dd,-(datepart(dd,getdate())-1),getdate())),112)) --本月时间
begin
set @tablename = 'sms_log_'+@c+'_'+@d --备份表表名 如sms_log_20070901_20071201
select log_id,EntName,type,plan_id,plan_detail_id,subject,CustomerID,mobile,name,title,cell,mtype,message,msgid,send_time,admin,state,gw,status,amount into #tablename from sms_log into where send_time > @b and send_time < @a
delete from sms_log where send_time > @b and send_time < @a --删除已备份数据
end
select log_id,EntName,type,plan_id,plan_detail_id,subject,CustomerID,mobile,name,title,cell,mtype,message,msgid,send_time,admin,state,gw,status,amount into #tablename from sms_log where send_time > @b and send_time < @a
[id] [int] IDENTITY(1,1) NOT NULL,
[chatto] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NOT NULL,
[receiveOrSend] [bit] NOT NULL,
[sendTime] [datetime] NOT NULL,
[content] [varchar](max) COLLATE Chinese_PRC_CI_AS NOT NULL,
[isreaded] [bit] NOT NULL CONSTRAINT [DF_'+@tablename+'_isreaded] DEFAULT ((0)),
CONSTRAINT [PK_'+@tablename+'] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]')
关键是在创建表后的操作 获取表名
declare @a datetime --现在时间
declare @b datetime --前段时间
declare @c nvarchar(8)--前段备份时间
declare @d nvarchar(8)--现在备份时间
declare @tablename nvarchar(50)--新建备份表
begin
set @a = (select dateadd(mm,0,dateadd(dd,-(datepart(dd,getdate())-1),getdate())))
set @b = (select dateadd(mm,-3,dateadd(dd,-(datepart(dd,getdate())-1),getdate())))
set @c = (select convert(char,dateadd(mm,-3,dateadd(dd,-(datepart(dd,getdate())-1),getdate())),112))
set @d = (select convert(char,dateadd(mm,0,dateadd(dd,-(datepart(dd,getdate())-1),getdate())),112))
set @tablename = 'sms_log_'+@c+'_'+@d --如sms_log_20070901_20071201
exec('create table '+@tablename+'(log_id int not null,EntName varchar(50),type int,plan_id int,plan_detail_id int,subject nvarchar(20),CustomerID varchar(50),mobile varchar(20),name nvarchar(20),title nvarchar(10),cell varchar(20),mtype varchar(4),message nvarchar(200),msgid varchar(50) not null,send_time datetime,admin varchar(20),state varchar(5),gw varchar(5),status varchar(5),amount smallint);')
insert into @tablename
select log_id,EntName,type,plan_id,plan_detail_id,subject,CustomerID,mobile,name,title,cell,mtype,message,msgid,send_time,admin,state,gw,status,amount from sms_log where send_time > @b and send_time < @a
--delete from sms_log where send_time > @b and send_time < @a
end