关于INSERT的问题(实在没分了各位帮帮忙)

[accpt16]

我现在有一张表A，字段为ID NAME AGE

我存储过程传进来的值是ID='1,2,3,4' NAME='A,B,C,D' AGE=18
这样的数据怎么插入啊？
也就是表的记录应该是

ID NAME AGE
1 A 18
2 B 18
3 C 18
4 D 18

[快乐_石头]

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 
--调用 
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 

[surlew]

up

[dawugui]

[Quote=引用楼主 accpt16 的帖子:]


我现在有一张表A，字段为ID NAME AGE

我存储过程传进来的值是ID='1,2,3,4' NAME='A,B,C,D' AGE=18
这样的数据怎么插入啊？
也就是表的记录应该是

ID NAME AGE
1 A 18
2 B 18
3 C 18
4 D 18
[/Quote]
如果只有四个,可以使用parsename()

[dawugui]

create table tb(ID int, NAME varchar(10), AGE int)

declare @ID as varchar(20)
declare @NAME as varchar(20)
declare @age as int
set @id = '1,2,3,4' 
set @name='A,B,C,D'
set @AGE=18 

insert into tb
select parsename(replace(@id , ',' , '.'),4) , parsename(replace(@name , ',' , '.'),4) , @age
union all
select parsename(replace(@id , ',' , '.'),3) , parsename(replace(@name , ',' , '.'),3) , @age
union all
select parsename(replace(@id , ',' , '.'),2) , parsename(replace(@name , ',' , '.'),2) , @age
union all
select parsename(replace(@id , ',' , '.'),1) , parsename(replace(@name , ',' , '.'),1) , @age

select * from tb

drop table tb

/*

ID          NAME       AGE         
----------- ---------- ----------- 
1           A          18
2           B          18
3           C          18
4           D          18

（所影响的行数为 4 行）
*/

[dawugui]

如果是多个,得用1楼的字符串分解函数来做.

[accpt16]

楼上的大哥，我用你这个函数怎么只能用在一个字段里面啊。2个字段就报错了
insert 表A select bb.a ,aa.a , @age from dbo.fn_split(@ID,',') as bb, dbo.fn_split(@NAME,',')as aa
是不是我写错了

[wantodba]

if object_id('tb')is not null
drop table tb
go
create table tb(ID int,Name varchar(20),AGE INT)
insert into tb values(1,'A',18)
insert into tb values(2,'B',18)
insert into tb values(3,'C',18)
insert into tb values(4,'D',18)
GO
select ID,Name,AGE
from tb
group by ID,Name,AGE

[accpt16]

不止4个。。N多啊。我这里只是举个例子

[accpt16]

[Quote=引用 5 楼 dawugui 的回复:]
如果是多个,得用1楼的字符串分解函数来做.
[/Quote]

那请问这个分解函数怎么用才能达到我要的效果呢

[dawugui]

[Quote=引用 9 楼 accpt16 的回复:]
引用 5 楼 dawugui 的回复:
如果是多个,得用1楼的字符串分解函数来做.


那请问这个分解函数怎么用才能达到我要的效果呢
[/Quote]
用存储过程

create table tb(ID int, NAME varchar(10), AGE int)
go
create procedure my_proc
@id as varchar(20),
@NAME as varchar(20),
@age as int
as
begin
  while charindex(',' , @id) > 0
  begin
    insert into tb values(left(@id , charindex(',' , @id) - 1) , left(@name , charindex(',' , @name) - 1) , @age)
    set @id = substring(@id , charindex(',' , @id) + 1 , len(@id))
    set @NAME = substring(@NAME , charindex(',' , @NAME) + 1 , len(@NAME))
  end
    insert into tb values(@id , @name , @age)
end
go

exec my_proc '1,2,3,4' , 'A,B,C,D' , 18

select * from tb

drop table tb
drop procedure my_proc

/*
ID          NAME       AGE         
----------- ---------- ----------- 
1           A          18
2           B          18
3           C          18
4           D          18

（所影响的行数为 4 行）

*/

[accpt16]

啊铺。。。。

[dawugui]

注意:ID和NAME的中的值必须按逗号匹配.

create table tb(ID int, NAME varchar(10), AGE int)
go
create procedure my_proc
@id as varchar(20),
@NAME as varchar(20),
@age as int
as
begin
  while charindex(',' , @id) > 0
  begin
    insert into tb values(left(@id , charindex(',' , @id) - 1) , left(@name , charindex(',' , @name) - 1) , @age)
    set @id = substring(@id , charindex(',' , @id) + 1 , len(@id))
    set @NAME = substring(@NAME , charindex(',' , @NAME) + 1 , len(@NAME))
  end
    insert into tb values(@id , @name , @age)
end
go

exec my_proc '1,2,3,4,5,6,7,8' , 'A,B,C,D,E,F,G,H' , 19

select * from tb

drop table tb
drop procedure my_proc

/*
ID          NAME       AGE         
----------- ---------- ----------- 
1           A          19
2           B          19
3           C          19
4           D          19
5           E          19
6           F          19
7           G          19
8           H          19

（所影响的行数为 8 行）


*/

[Roc_Lee]

[Quote=引用 4 楼 dawugui 的回复:]
SQL codecreate table tb(ID int, NAME varchar(10), AGE int)

declare @ID as varchar(20)
declare @NAME as varchar(20)
declare @age as int
set @id = '1,2,3,4'
set @name='A,B,C,D'
set @AGE=18

insert into tb
select parsename(replace(@id , ',' , '.'),4) , parsename(replace(@name , ',' , '.'),4) , @age
union all
select parsename(replace(@id , ',' , '.'),3) , parsename(replace(@name , ','…
[/Quote]
这个似乎，思路好啊。

[chuifengde]

CREATE PROC ly
	@ID VARCHAR(100)='1,2,3,4',
	@NAME VARCHAR(100)='A,B,C,D,E',
	@AGE int=18 
AS
SET NOCOUNT ON 

DECLARE @b TABLE(Id INT IDENTITY(1,1),x VARCHAR(100))
DECLARE @a TABLE(Id int identity(1,1),Ix INT,NAME VARCHAR(20),age INT)

IF LEN(@name)-LEN(REPLACE(@name,',',''))>=LEN(@ID)-LEN(REPLACE(@id,',',''))
BEGIN 	
	INSERT @a (NAME,age) SELECT x,@age FROM dbo.getIL(@name)	
	INSERT @b (x) SELECT x FROM dbo.getIL(@id)
	UPDATE a SET Ix=x FROM @a a Inner join @b b ON a.id=b.id
END 
ELSE
BEGIN 
	INSERT @a (ix,age) SELECT x,@age FROM dbo.getIL(@id)	
	INSERT @b (x) SELECT x FROM dbo.getIL(@name)
	UPDATE a SET Name=x FROM @a a Inner join @b b ON a.id=b.id		
END 
SELECT * FROM @a

GO

CREATE FUNCTION getIL(@x VARCHAR(100))
RETURNS  @a TABLE(id INT IDENTITY(1,1),x VARCHAR(100))
AS 
BEGIN
	INSERT @a(x)
	SELECT SUBSTRING(@x+',',langid,CHARINDEX(',',@x+',',langid)-langid)
	FROM MASTER.dbo.syslanguages A
	WHERE SUBSTRING(','+@x,langid,1)=',' ORDER BY langid
	RETURN 
END

GO

EXEC ly 

--result
/*Id          Ix          NAME                 age         
----------- ----------- -------------------- ----------- 
1           1           A                    18
2           2           B                    18
3           3           C                    18
4           4           D                    18
5           NULL        E                    18*/