表名:a_temp
ip type id
1 1 2
2 1 3
3 2 1
4 3 3
5 3 3
6 2 1
. . .
. . .
. . .运用sql server存储过程:将表a_temp中的数据按类型分别插入到表a_1,a_2,a_3......中(如表a_temp中字段type是1时就将数据插入到a_1),表a_1,a_2,a_3.....中的字段和表a_temp中的字段一样
ip type id
1 1 2
2 1 3
3 2 1
4 3 3
5 3 3
6 2 1
. . .
. . .
. . .运用sql server存储过程:将表a_temp中的数据按类型分别插入到表a_1,a_2,a_3......中(如表a_temp中字段type是1时就将数据插入到a_1),表a_1,a_2,a_3.....中的字段和表a_temp中的字段一样
解决方案 »
- 关于排序的问题
- [散分]VB连接SQL登陆问题,来者有分,顺便感谢萤火虫。
- sqlserver中如何判断字符串是有数字组成,我写了一个,觉得特别苯,大虾们有没有好的方法啊
- 紧急求救!数据库被注入并替换了数据,如何恢复?
- 哪有 microsoft sql server2000 下载?
- 晕! SQL2005中的网络盘备份日志出错了! 帮忙看看,谢谢!
- 取得server上时间的问题(急!!)
- 请问这个select怎么写?谢谢
- 一个报表问题
- 急求sql 统计每一天的历史至当日的去重计数值
- 问一个关于触发器的问题,INSTEAD OF delete里面执行delete会再次触发这个触发器吗
- 存储过程,跪求答案啊
if OBJECT_ID('tb1_A','u') is not null
drop table tb1_A
create table tb1_A
(
ip int,
type int,
id int
)
goinsert into tb1_A
select 1,1,2 union all
select 2,1,3 union all
select 3,2,1 union all
select 4,3,3 union all
select 5,3,3 union all
select 6,2,1create table a_1
(
ip int,
type int,
id int
)create table a_2
(
ip int,
type int,
id int
)create table a_3
(
ip int,
type int,
id int
)create proc text_tb1
as
insert into a_1 select *From tb1_A where [type]=1
insert into a_2 select *From tb1_A where [type]=2
insert into a_3 select *From tb1_A where [type]=3
goexec text_tb1
begin
insert into a_1 select * from a_temp where type=1
insert into a_2 select * from a_temp where type=2
insert into a_3 select * from a_temp where type=3 end--执行存储过程
exec 存储过程名
但是如果type动态不确定,那么楼主看看下面的代码。--创建测试数据
if OBJECT_ID('a_temp') is not null drop table a_temp
create table a_temp(ip int identity(1,1),[type] int,id int)
go
insert into a_temp([type],id)
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 3,3 union all
select 3,3 union all
select 2,1 union all
select 7,3 union all
select 8,2 --创建存储过程
if OBJECT_ID('[dbo].[createTableByType_20121202]') is not null drop procedure [dbo].[createTableByType_20121202]
go
create procedure [dbo].[createTableByType_20121202]
as
declare @number int,--最大的类型值
@i int,--临时变量
@sql nvarchar(max)--存放动态拼接的sql语句(用于后面动态插入)
set @i=1
select @number=MAX([type]) from a_temp
while @i<=@number
begin
if isnull(@number,0)>=@i
begin
set @sql=isnull(@sql,'')+'if exists(select * from a_temp where [type]='+convert(nvarchar(4),@i)+') begin'
set @sql=@sql+' if OBJECT_ID(''a_'+convert(nvarchar(4),@i)+''') is not null drop table a_'
+convert(nvarchar(4),@i)+' select * into a_'+convert(nvarchar(4),@i)+' from a_temp where [type]='+convert(nvarchar(4),@i)+' end '
set @i=@i+1
end
end
if(isnull(@sql,'')<>'')
--print @sql
exec(@sql)go
--调用存储过程
exec [dbo].[createTableByType_20121202]/* 查看新增表的查询结果
select * from a_1
select * from a_2
select * from a_3
select * from a_7
select * from a_8
--结果如下
ip type id
----------- ----------- -----------
1 1 2
2 1 3(2 行受影响)ip type id
----------- ----------- -----------
3 2 1
6 2 1(2 行受影响)ip type id
----------- ----------- -----------
4 3 3
5 3 3(2 行受影响)ip type id
----------- ----------- -----------
7 7 3(1 行受影响)ip type id
----------- ----------- -----------
8 8 2(1 行受影响)*/--以上面的例子为例存储过程代码备注:(其实上面的存储过程就是动态生成下面的sql语句)
/*if exists(select * from a_temp where [type]=1)--判断a_temp表中是否存在[type]=1的数据
begin --如果有
if OBJECT_ID('a_1') is not null drop table a_1 --看表a_1(这里的1就是上面的[type])是否存在,存在则删除
select * into a_1 from a_temp where [type]=1 --查询表a_temp中[type]=1的数据新增表a_1并将数据插入到a_1
end
--下面同上
if exists(select * from a_temp where [type]=2)
begin
if OBJECT_ID('a_2') is not null drop table a_2
select * into a_2 from a_temp where [type]=2
end --......下面还有很多组
*/
if OBJECT_ID('a_temp') is not null drop table a_temp
create table a_temp(ip int identity(1,1),[type] int,id int)
go
insert into a_temp([type],id)
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 3,3 union all
select 3,3 union all
select 2,1 union all
select 7,3 union all
select 8,2
--创建存储过程
if OBJECT_ID('[dbo].[createTableByType_20121202]') is not null drop procedure [dbo].[createTableByType_20121202]
go
create procedure [dbo].[createTableByType_20121202]
as
declare @sql varchar(max) =''
--表存在则删除
select @sql=@sql+'if exists(select * from a_temp where [type]='+ltrim([type])+') begin'
+' if OBJECT_ID(''a_'+ltrim([type])+''') is not null drop table a_'
+ltrim([type])+' end ' from (select distinct [type] from a_temp) t
--插入
select @sql = @sql +'
select * into a_'+ltrim([type])+' from a_temp where type='+ltrim([type]) from (select distinct [type] from a_temp) t
exec(@sql)
go
--调用
exec [dbo].[createTableByType_20121202]
if OBJECT_ID('[dbo].[createTableByType_20121202]') is not null drop procedure [dbo].[createTableByType_20121202]
go
create procedure [dbo].[createTableByType_20121202]
as
declare @sql varchar(max) =''
--表存在则删除
select @sql=@sql+' if OBJECT_ID(''a_'+ltrim([type])+''') is not null drop table a_'
+ltrim([type]) from (select distinct [type] from a_temp) t
--插入
select @sql = @sql +'
select * into a_'+ltrim([type])+' from a_temp where type='+ltrim([type]) from (select distinct [type] from a_temp) t
exec(@sql)
GO
-->生成表a_temp
if object_id('a_temp') is not null
drop table a_temp
Go
Create table a_temp([ip] smallint,[type] smallint,[id] smallint)
Insert into a_temp
Select 1,1,2
Union all Select 2,1,3
Union all Select 3,2,1
Union all Select 4,3,3
Union all Select 5,3,3
Union all Select 6,2,1
Union all Select 7,4,1 -- for test
Union all Select 8,3,1 -- for test
Union all Select 9,4,1 -- for testDECLARE @sql NVARCHAR(MAX)SELECT @sql=ISNULL(@sql+';','')+'Select * into a_'+LTRIM(type)+' FROM a_temp Where type='+LTRIM(type) FROM a_temp GROUP BY typeEXEC (@sql)
--SELECT * FROM a_1
--SELECT * FROM a_2
--SELECT * FROM a_3
--SELECT * FROM a_4
--
--if object_id('a_1') is not null
-- drop table a_1
--Go
--if object_id('a_2') is not null
-- drop table a_2
--Go
--if object_id('a_3') is not null
-- drop table a_3
--Go
--if object_id('a_4') is not null
-- drop table a_4
--Go
create proc up_test
as
select * into a_1 from tb1_A where [type]=1
select * into a_2 from tb1_A where [type]=2
select * into a_3 from tb1_A where [type]=3
go
exec up_test
drop table a_temp
go
create table a_temp(ip int,type int,id int)
insert into a_temp
select 1,1,2 union all
select 2,1,3 union all
select 3,2,1 union all
select 4,3,3 union all
select 5,3,3 union all
select 6,2,1
create table a_1(ip int,type int,id int)
create table a_2(ip int,type int,id int)
create table a_3(ip int,type int,id int)
GO
if OBJECT_ID('pr_a_temp') is not null
DROP PROCEDURE pr_a_temp
GO
CREATE PROCEDURE pr_a_temp
as
INSERT INTO a_1 SELECT * FROM a_temp WHERE TYPE=1
INSERT INTO a_2 SELECT * FROM a_temp WHERE TYPE=2
INSERT INTO a_3 SELECT * FROM a_temp WHERE TYPE=3EXECUTE pr_a_tempDROP TABLE a_1,a_2,a_3
if object_id('a_temp') is not null
drop table a_temp
Go
Create table a_temp([ip] smallint,[type] smallint,[id] smallint)
Insert into a_temp
Select 1,1,2 Union all
Select 2,1,3 Union all
Select 3,2,1 Union all
Select 4,3,3 Union all
Select 5,3,3 Union all
Select 6,2,1
DECLARE @sql NVARCHAR(MAX)
SELECT @sql=ISNULL(@sql+';'+CHAR(10),'')+'Select * into a_'+LTRIM(type)+' FROM a_temp Where type='+LTRIM(type) FROM a_temp GROUP BY type
PRINT @sql
/*
Select * into a_1 FROM a_temp Where type=1;
Select * into a_2 FROM a_temp Where type=2;
Select * into a_3 FROM a_temp Where type=3;
Select * into a_4 FROM a_temp Where type=4
*/