数据格式如下: Silly 1,2,3,4 2009-11-10 A,B,C,D 这是需要提交进去的数据格式 存储到数据库里的格式应该是: Silly 1 2009-11-19 A
Silly 2 2009-11-19 B
Silly 3 2009-11-19 C
Silly 4 2009-11-19 D 我想用一个存储过程,实现这四条数据的一次性插入,请高手帮我写一下,谢谢。
Silly 2 2009-11-19 B
Silly 3 2009-11-19 C
Silly 4 2009-11-19 D 我想用一个存储过程,实现这四条数据的一次性插入,请高手帮我写一下,谢谢。
drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
GoSQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select
a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID)
from
Tab a,#Num b
where
charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
SQL2005用Xml:select
a.COl1,b.Col2
from
(select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
(select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-10 14:25:22
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[T]
if object_id('[T]') is not null drop table [T]
go
create table [T]([col1] varchar(5),[col2] varchar(7),[col3] datetime,[col4] varchar(7))
insert [T]
select 'Silly','1,2,3,4','2009-11-10','A,B,C,D'
--------------开始查询--------------------------
select
a.COl1,b.Col2,a.col3,b.col4
from
(select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>'),col3,
COl4=convert(xml,'<root><v>'+replace(COl4,',','</v><v>')+'</v></root>') from T)a
outer apply
(select Col2=C.v.value('.','nvarchar(100)'),Col4=C.v.value('.','nvarchar(100)')
from a.COl2.nodes('/root/v')C(v))b
----------------结果----------------------------
/*
(1 行受影响)
COl1 Col2 col3 col4
----- ---------------------------------------------------------------------------------------------------- ----------------------- ----------------------------------------------------------------------------------------------------
Silly 1 2009-11-10 00:00:00.000 1
Silly 2 2009-11-10 00:00:00.000 2
Silly 3 2009-11-10 00:00:00.000 3
Silly 4 2009-11-10 00:00:00.000 4(4 行受影响)
*/