关于一次性插入多条记录的优化问题

[accpt16]

我现在有个表 TAB
字段为：

ID BillDetail_ID GoodRecord_ID BuildDate
类型为：
INT INT INT DATETIME


现在我要一次性插入这样的数据

insert TAB values( 1,(1,2,3..... 有上W条记录),getdate())
我现在写的语句插入要等半天。。郁闷。。请问怎么优化啊~~~

[dobear_0922]

上W条？应该会很快呀，，，

[dawugui]

1,2,3之类的数据怎么来的,自动生成?

[accpt16]

我也不知道。GoodRecord_ID这个字段传进来的时候是用,隔开的。所以我用了charindex这个函数

[水族杰纶]

說詳細點

[accpt16]

[Quote=引用 2 楼 dawugui 的回复:]
1,2,3之类的数据怎么来的,自动生成?
[/Quote]

GoodRecord_ID是个varchar类型的

[dobear_0922]

insert  TAB  values( 1,(1,2,3..... 有上W条记录),getdate()) 
----可以改成下面的试试：
insert TAB select 1, 1, getdate()
union all select 2, 2, getdate()
--...
union all select 10000,1,getdate()

[水族杰纶]

---分割函數
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 

[dawugui]

--如果是自动生成一组连续的数值
--生成一个临时表
SELECT TOP 10000 id = identity(int,1,1) INTO # FROM syscolumns a, syscolumns b   
insert into TAB select id , id , id , getdate() from # 

[accpt16]

我现在的思路是是用
while charindex(',',@GoodRecord_ID)>0
这个条件来循环插入的啊

[dawugui]

[Quote=引用 3 楼 accpt16 的回复:]
我也不知道。GoodRecord_ID这个字段传进来的时候是用,隔开的。所以我用了charindex这个函数
[/Quote]

那就用子符串分解函数来实现.
/*
功能：实现split功能的函数
*/

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 

[dawugui]

/*
功能：实现split功能的函数
*/

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 ID = 1 , * , BuildDate = getdate() from dbo.fn_split(@s,',')

drop function dbo.fn_split 

/*
ID          a                                                                                                                                                                                                        BuildDate                                              
----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ 
1           1                                                                                                                                                                                                        2008-12-25 09:44:17.047
1           2                                                                                                                                                                                                        2008-12-25 09:44:17.047
1           3                                                                                                                                                                                                        2008-12-25 09:44:17.047
1           4                                                                                                                                                                                                        2008-12-25 09:44:17.047
1           5                                                                                                                                                                                                        2008-12-25 09:44:17.047
1           6                                                                                                                                                                                                        2008-12-25 09:44:17.047
1           7                                                                                                                                                                                                        2008-12-25 09:44:17.047
1           8                                                                                                                                                                                                        2008-12-25 09:44:17.047
1           55                                                                                                                                                                                                       2008-12-25 09:44:17.047

（所影响的行数为 9 行）

*/

[水族杰纶]

if object_id('dbo.fn_split')is not null drop function dbo.fn_split
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 
if object_id('tb')is not null drop table tb
go
create table tb(ID int,    BillDetail_ID int,  GoodRecord_ID int,    BuildDate  datetime)
--调用 
declare @s varchar(1000) 
set @s='1,2,3,4,5,6,7,8,9' 
insert tb select 1,1,a,getdate() from dbo.fn_split(@s,',') 
select * from tb
/*
(影響 9 個資料列)

ID          BillDetail_ID GoodRecord_ID BuildDate                                              
----------- ------------- ------------- ------------------------------------------------------ 
1           1             1             2008-12-25 09:47:11.930
1           1             2             2008-12-25 09:47:11.930
1           1             3             2008-12-25 09:47:11.930
1           1             4             2008-12-25 09:47:11.930
1           1             5             2008-12-25 09:47:11.930
1           1             6             2008-12-25 09:47:11.930
1           1             7             2008-12-25 09:47:11.930
1           1             8             2008-12-25 09:47:11.930
1           1             9             2008-12-25 09:47:11.930

(影響 9 個資料列)

*/

[accpt16]

select ID = 1 , * , BuildDate = getdate() from dbo.fn_split(@s,',')
这个*号是什么意思？

[JYR520]

[Quote=引用 12 楼 wufeng4552 的回复:]
SQL codeif object_id('dbo.fn_split')is not null drop function dbo.fn_split
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…
[/Quote]
学习，学习一下

[accpt16]

用函数。。代码看起来清晰多了。。。谢谢大家帮忙

[accpt16]

insert tb select 1,1,a,getdate() from dbo.fn_split(@s,',')
这个A 是不是fn_split(@s,',')返回的记录集啊？