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

Question

我现在有个表 TAB 
字段为：ID    BillDetail_ID  GoodRecord_ID    BuildDate 
类型为：
INT      INT               INT         DATETIME
现在我要一次性插入这样的数据 insert   TAB  values( 1,(1,2,3..... 有上W条记录),getdate())
我现在写的语句插入要等半天。。郁闷。。请问怎么优化啊~~~

Answer 1

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

Answer 2

GoodRecord_ID是个varchar类型的

Answer 3

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()

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

那就用子符串分解函数来实现.
/*
功能：实现split功能的函数
*/create function dbo.fn_split 
(
@inputstr varchar(8000), 
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @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)
endif @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 

Answer 8

/*
功能：实现split功能的函数
*/create function dbo.fn_split 
(
@inputstr varchar(8000), 
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @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)
endif @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 行）*/

Answer 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 個資料列)*/

Answer 10

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

Answer 11

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