sql语句一条记录拆分为多条记录

最新推荐文章于 2024-09-12 15:49:40 发布

不停歇的步伐

最新推荐文章于 2024-09-12 15:49:40 发布

阅读量1w

点赞数 1

分类专栏： SQLSERVER 文章标签： sql insert table object null function

SQLSERVER 专栏收录该内容

20 篇文章

订阅专栏

if exists (select 1 from dbo.sysobjects where id = object_id(N't_test'))
drop procedure [dbo].[t_test]
GO
Create table t_test ( id int primary key,LongField varchar(50))
GO
Insert Into t_test (id,LongField)  values('1','aa bb cc dd')
Insert Into t_test (id,LongField)  values('3','ab cc da fm')
GO
if exists (select 1 from dbo.sysobjects where id = object_id(N'f_tstr'))
drop procedure [dbo].[f_tstr]
GO
create  function f_tstr(
@s varchar(8000),
@split varchar(10),
@id int )returns @re table(id int ,col varchar(100))
as
begin
if @s is null return
while charindex(@split,@s)>0
begin
insert into @re(id,col)select @id,left(@s,charindex(@split,@s)-1)
set @s=right(@s,len(@s)-charindex(@split,@s))
end
insert into @re(id,col) select @id, @s return
end
GO
if exists (select 1 from dbo.sysobjects where id = object_id(N't_test1'))
drop procedure [dbo].[t_test1]
GO
Create table t_test1 ( id int,ShortField varchar(50))
GO
declare @M int
select @M=max(id) from t_test
while @M>0
begin
declare @a varchar (200)
,@b varchar(20)
select @a=id,@b=LongField from t_test where id =@M
insert into t_test1(id,ShortField) select * from dbo.f_tstr(@b,' ',@a)
set @M=@M-1
set @a=null
set @b=null
end
GO
select * from t_test1
GO
drop table t_test
drop table t_test1
drop function DBO.f_tstr
GO