创建一个存储过程,把表A中的数据根据条件随机复制几条到另一张表B中,并要求表B中时间字段要随机生成。
整个表A的结构为CREATE TABLE [dbo].[ex091214] (
[date] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[time] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[ssitename] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[sip] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csmethod] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csuristem] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csuriquery] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[sport] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csusername] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[cip] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csversion] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csUserAgent] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csReferer] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[cshost] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[scstatus] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[scsubstatus] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[scwin32status] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[scbytes] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csbytes] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[timetaken] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO下面是我写的存储过程:可根据时间段或ip复制,该存储过程可以执行并成功,但现在需要把表名ex091214用参数变量表示,请帮忙想一下怎么改好,直接改好更好!
ALTER proc p_copyTableData
(
@type int,--查询类型
@copyCount int,--复制条数
@strTime varchar(50),--开始时间
@endTime varchar(50),--结束时间
@cip varchar(50)--Ip
)
as
begin-- declare @type int,
-- @copyCount int,
-- @strTime varchar(50),
-- @endTime varchar(50),
-- @cip varchar(50)
-- set @type=2
-- set @copyCount=12
-- set @strTime=''
-- set @endTime=''
-- set @cip='122.25.167.36' --根据条件把筛选的数据保存到临时表中
select identity(int,1,1) as tbId,* into #tb1 from ex091214 where 1=0
if @type = 1
begin
insert into #tb1 select * from WebLog.dbo.ex091214 where [time] between @strTime and @endTime
end
else
begin
insert into #tb1 select * from WebLog.dbo.ex091214 where cip = @cip
end
--统计临时表的行数
declare @lcount int
select @lcount = count(*)from #tb1 --记录插入行数
declare @insertCount int
set @insertCount = 0
--如果要添加的行数大于临时表的行数就循环插入
while 1=1
begin
--修改临时表中的时间(每条记录的时间都是随机的)
declare @i int
set @i = @lcount
while @i >0
begin
declare @d datetime
set @d = dateadd(second,convert(int,(rand()*datediff(second,'09:00:00','17:00:00'))),'09:00:00')
--select CONVERT(varchar(12) , @d, 108 )
update #tb1 set [time] = CONVERT(varchar(12),@d,108) where tbId = @i
set @i = @i - 1
end --获取表的字段名
declare @columns varchar(8000)
set @columns = ''
select identity(int,1,1) as tb3Id,COLUMN_NAME into #tb3 from information_schema.columns where table_name='ex091214'
select @i = count(*) from #tb3
while @i >0
begin
declare @c1 varchar(50)
select @c1 = COLUMN_NAME from #tb3 where tb3Id = @i
set @columns = convert(varchar(50),@c1) + ',' +@columns
set @i = @i - 1
end
set @columns = substring(@columns,0,len(ltrim(rtrim(@columns))))
drop table #tb3 declare @sql varchar(8000)
if @copyCount < @lcount
begin
set @sql = 'insert into ex091214('+@columns+') select top '+convert(varchar(30),@copyCount)+' '+@columns+' from #tb1 order by newid()'
exec(@sql)
set @insertCount = @@ROWCOUNT + @insertCount
-- print @sql
break
end
else
begin
set @sql = 'insert into ex091214('+@columns+') select '+@columns+' from #tb1'
exec(@sql)
set @insertCount = @@ROWCOUNT + @insertCount
set @copyCount = @copyCount - @lcount
end
end
select @insertCount
drop table #tb1
end
整个表A的结构为CREATE TABLE [dbo].[ex091214] (
[date] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[time] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[ssitename] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[sip] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csmethod] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csuristem] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csuriquery] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[sport] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csusername] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[cip] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csversion] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csUserAgent] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csReferer] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[cshost] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[scstatus] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[scsubstatus] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[scwin32status] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[scbytes] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[csbytes] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[timetaken] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO下面是我写的存储过程:可根据时间段或ip复制,该存储过程可以执行并成功,但现在需要把表名ex091214用参数变量表示,请帮忙想一下怎么改好,直接改好更好!
ALTER proc p_copyTableData
(
@type int,--查询类型
@copyCount int,--复制条数
@strTime varchar(50),--开始时间
@endTime varchar(50),--结束时间
@cip varchar(50)--Ip
)
as
begin-- declare @type int,
-- @copyCount int,
-- @strTime varchar(50),
-- @endTime varchar(50),
-- @cip varchar(50)
-- set @type=2
-- set @copyCount=12
-- set @strTime=''
-- set @endTime=''
-- set @cip='122.25.167.36' --根据条件把筛选的数据保存到临时表中
select identity(int,1,1) as tbId,* into #tb1 from ex091214 where 1=0
if @type = 1
begin
insert into #tb1 select * from WebLog.dbo.ex091214 where [time] between @strTime and @endTime
end
else
begin
insert into #tb1 select * from WebLog.dbo.ex091214 where cip = @cip
end
--统计临时表的行数
declare @lcount int
select @lcount = count(*)from #tb1 --记录插入行数
declare @insertCount int
set @insertCount = 0
--如果要添加的行数大于临时表的行数就循环插入
while 1=1
begin
--修改临时表中的时间(每条记录的时间都是随机的)
declare @i int
set @i = @lcount
while @i >0
begin
declare @d datetime
set @d = dateadd(second,convert(int,(rand()*datediff(second,'09:00:00','17:00:00'))),'09:00:00')
--select CONVERT(varchar(12) , @d, 108 )
update #tb1 set [time] = CONVERT(varchar(12),@d,108) where tbId = @i
set @i = @i - 1
end --获取表的字段名
declare @columns varchar(8000)
set @columns = ''
select identity(int,1,1) as tb3Id,COLUMN_NAME into #tb3 from information_schema.columns where table_name='ex091214'
select @i = count(*) from #tb3
while @i >0
begin
declare @c1 varchar(50)
select @c1 = COLUMN_NAME from #tb3 where tb3Id = @i
set @columns = convert(varchar(50),@c1) + ',' +@columns
set @i = @i - 1
end
set @columns = substring(@columns,0,len(ltrim(rtrim(@columns))))
drop table #tb3 declare @sql varchar(8000)
if @copyCount < @lcount
begin
set @sql = 'insert into ex091214('+@columns+') select top '+convert(varchar(30),@copyCount)+' '+@columns+' from #tb1 order by newid()'
exec(@sql)
set @insertCount = @@ROWCOUNT + @insertCount
-- print @sql
break
end
else
begin
set @sql = 'insert into ex091214('+@columns+') select '+@columns+' from #tb1'
exec(@sql)
set @insertCount = @@ROWCOUNT + @insertCount
set @copyCount = @copyCount - @lcount
end
end
select @insertCount
drop table #tb1
end
解决方案 »
- 如何比较给定一个值如何和查询的一条记录中任意一列相同就返回true?
- 请教一个关于连接的问题!
- exec sp_executesql @s, N'@dt_begin datetime, @dt_end datetime', @dt_begin , @dt_end 什么意思?
- 关系表ID值更改问题
- 一个存储过程,如何查询另外一个数据库里的表?
- 在连接数据库之前调用sp_password @new='123','sa'来强行修改登陆密码,这样应该怎样实现?用VB或哪个API可以吗?谢谢!
- 送分题--非常简单的问题! 请来指导
- 我在企业管理器里 怎么删除不掉记录?
- Access中怎么调试SQL语句?
- 小弟有一个关于提交给远程SQL Server的问题请教各位大哥.
- 数据库小问题
- 难题 SQL 过滤出 包含的 数据区间 ,困扰多年了.欢迎高手指导
(
@type int,--查询类型
@copyCount int,--复制条数
@strTime varchar(50),--开始时间
@endTime varchar(50),--结束时间
@cip varchar(50),--Ip
@tb varchar(100)
)
as
begin-- declare @type int,
-- @copyCount int,
-- @strTime varchar(50),
-- @endTime varchar(50),
-- @cip varchar(50)
-- set @type=2
-- set @copyCount=12
-- set @strTime=''
-- set @endTime=''
-- set @cip='122.25.167.36'--根据条件把筛选的数据保存到临时表中select identity(int,1,1) as tbId,* into ##tb1 from ex091214 where 1=0
if @type = 1
begin
exec('insert into ##tb1 select * from '+@tb+'where [time] between '''+convert(char(19),@strTime,120)+''' and '''+convert(char(19),@endTime,120)+''''
end
else
begin
exec('insert into ##tb1 select * from '+@tb+' where cip = '''+ltrim(@cip)+''''
end
--统计临时表的行数
declare @lcount int
select @lcount = count(*)from ##tb1--记录插入行数
declare @insertCount int
set @insertCount = 0
--如果要添加的行数大于临时表的行数就循环插入
while 1=1
begin
--修改临时表中的时间(每条记录的时间都是随机的)
declare @i int
set @i = @lcount
while @i >0
begin
declare @d datetime
set @d = dateadd(second,convert(int,(rand()*datediff(second,'09:00:00','17:00:00'))),'09:00:00')
--select CONVERT(varchar(12) , @d, 108 )
update ##tb1 set [time] = CONVERT(varchar(12),@d,108) where tbId = @i
set @i = @i - 1
end--获取表的字段名
declare @columns varchar(8000)
set @columns = ''
select identity(int,1,1) as tb3Id,COLUMN_NAME into #tb3 from information_schema.columns where table_name='ex091214'
select @i = count(*) from #tb3
while @i >0
begin
declare @c1 varchar(50)
select @c1 = COLUMN_NAME from #tb3 where tb3Id = @i
set @columns = convert(varchar(50),@c1) + ',' +@columns
set @i = @i - 1
end
set @columns = substring(@columns,0,len(ltrim(rtrim(@columns))))
drop table #tb3declare @sql varchar(8000)
if @copyCount < @lcount
begin
set @sql = 'insert into ex091214('+@columns+') select top '+convert(varchar(30),@copyCount)+' '+@columns+' from ##tb1 order by newid()'
exec(@sql)
set @insertCount = @@ROWCOUNT + @insertCount
-- print @sql
break
end
else
begin
set @sql = 'insert into ex091214('+@columns+') select '+@columns+' from ##tb1'
exec(@sql)
set @insertCount = @@ROWCOUNT + @insertCount
set @copyCount = @copyCount - @lcount
end
end
select @insertCount
drop table ##tb1
end