(1)问:有一段数据为,'00274','01374','02216','02352','04752','04948','05105'
请问怎么使用sql语句循环插入数据库?
(2)问:数据库有如下列,怎么循环给如下列多加一列bb呢?
00274 aa
01374 aa
02216 aa
02352 aa
04752 aa
04948 aa
05105 aa
05106 aa
增加后的效果如下
00274 aa
01374 aa
02216 aa
02352 aa
04752 aa
04948 aa
05105 aa
05106 aa
00274 bb
01374 bb
02216 bb
02352 bb
04752 bb
04948 bb
05105 bb
05106 bb回答哪个都可以,最好两个都回答
请问怎么使用sql语句循环插入数据库?
(2)问:数据库有如下列,怎么循环给如下列多加一列bb呢?
00274 aa
01374 aa
02216 aa
02352 aa
04752 aa
04948 aa
05105 aa
05106 aa
增加后的效果如下
00274 aa
01374 aa
02216 aa
02352 aa
04752 aa
04948 aa
05105 aa
05106 aa
00274 bb
01374 bb
02216 bb
02352 bb
04752 bb
04948 bb
05105 bb
05106 bb回答哪个都可以,最好两个都回答
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
--调用
declare @s varchar(1000)
set @s='1,2,3,4,5,6,7,8,55'
select * from dbo.fn_split(@s,',')
drop function dbo.fn_split
00274 aa
01374 aa
02216 aa
02352 aa
04752 aa
04948 aa
05105 aa
05106 aa
增加后的效果如下
00274 aa
01374 aa
02216 aa
02352 aa
04752 aa
04948 aa
05105 aa
05106 aa
00274 bb
01374 bb
02216 bb
02352 bb
04752 bb
04948 bb
05105 bb
05106 bb
insert into tb select col1 , col2 = 'bb' from tb
select id,aa from tb union all
select id,'bb' from tb
GO
create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go GO
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID VARCHAR(10),[NAME] VARCHAR(10))
--调用
declare @s varchar(1000)
set @s='''00274'',''01374'',''02216'',''02352'',''04752'',''04948'',''05105'''
INSERT TB SELECT *,'AA' from dbo.fn_split(@s,',')
INSERT TB SELECT *,'BB' FROM dbo.fn_split(@s,',')
SELECT * FROM TB
/*ID NAME
---------- ----------
'00274' AA
'01374' AA
'02216' AA
'02352' AA
'04752' AA
'04948' AA
'05105' AA
'00274' BB
'01374' BB
'02216' BB
'02352' BB
'04752' BB
'04948' BB
'05105' BB*/
请问怎么使用sql语句循环插入数据库?
(2)问:数据库有如下列,怎么循环给如下列多加一列bb呢?
00274 aa
01374 aa
02216 aa
02352 aa
04752 aa
04948 aa
05105 aa
05106 aa declare @s as varchar(100)
declare @sql as varchar(1000)
set @s = '00274,01374,02216,02352,04752,04948,05105'
set @sql =
'select ''' + replace(@s , ',' , ''' col1 , ''aa'' col2 union all select ''') + ''' col1 , ''aa'' col2 '
exec('insert into tb ' + @sql)
go--1
declare @s as varchar(100)
declare @sql as varchar(1000)
set @s = '00274,01374,02216,02352,04752,04948,05105'
set @sql =
'select ''' + replace(@s , ',' , ''' col1 , ''aa'' col2 union all select ''') + ''' col1 , ''aa'' col2 '
exec('insert into tb ' + @sql)
goselect * from tb
/*
col1 col2
---------- ----------
00274 aa
01374 aa
02216 aa
02352 aa
04752 aa
04948 aa
05105 aa(所影响的行数为 7 行)
*/--2
insert into tb select col1 , col2 = 'bb' from tbselect * from tb
/*
col1 col2
---------- ----------
00274 aa
01374 aa
02216 aa
02352 aa
04752 aa
04948 aa
05105 aa
00274 bb
01374 bb
02216 bb
02352 bb
04752 bb
04948 bb
05105 bb(所影响的行数为 14 行)
*/drop table tb
if object_id('tb') is not null
drop table tb
go
create table tb(id varchar(10),name varchar(10))
go
declare @str varchar(100)
set @str='00274,01374,02216,02352,04752,04948,05105,'
while @str<>''
begin
insert into tb (id,name) values (substring(@str,1,charindex(',',@str)-1),'aa')
insert into tb (id,name) values (substring(@str,1,charindex(',',@str)-1),'bb')
set @str=substring(@str,charindex(',',@str)+1,len(@str)-charindex(',',@str))
end
go
select * from tb order by name