22,210
社区成员
发帖
与我相关
我的任务
分享
bulk insert people from 'F:\aa.txt'
with(fieldterminator=';',
rowterminator=';\r',
kilobytes_per_batch=5000)
update people
set l_id=left(l_id,len(l_id)-1)
where right(l_id,1)=N';'
select * from people
s_type c_id c_name p_id p_name b_date e_date l_id
A 1008000138 299船 20027834 钱 长城 2013-06-14 9999-12-31 1008000003
A 1008000138 299船 20027834 钱 长城 2013-06-14 9999-12-31 1008000005;
[/quote]
我的也是2008R2的,就是多个分号呢。truncate table people
bulk insert people from 'c:\aa.txt' with(fieldterminator='/',rowterminator='\;\n')
select * from people
/*
s_type c_id c_name p_id p_name b_date e_date l_id
A 1008000138 299船 20027834 钱 长城 2013-06-14 9999-12-31 1008000003
A 1008000138 299船 20027834 钱 长城 2013-06-14 9999-12-31 1008000005
*/
s_type c_id c_name p_id p_name b_date e_date l_id
A 1008000138 299船 20027834 钱 长城 2013-06-14 9999-12-31 1008000003
A 1008000138 299船 20027834 钱 长城 2013-06-14 9999-12-31 1008000005;
bulk insert people from 'F:\aa.txt' with(fieldterminator=';',rowterminator=';\n')
CREATE TABLE [dbo].[people](
[s_type] [varchar](50) NOT NULL,
[c_id] [int] NOT NULL,
[c_name] [varchar](50) NOT NULL,
[p_id] [int] NOT NULL,
[p_name] [varchar](50) NOT NULL,
[b_date] [date] NOT NULL,
[e_date] [date] NOT NULL,
[l_id] [nvarchar](max) NOT NULL
)
--truncate table people
bulk insert people from 'c:\aa.txt' with(fieldterminator=';',rowterminator='\n')
select * from people
--把分号去掉
update people
set l_id = left(l_id,len(l_id)-1)
/*
s_type c_id c_name p_id p_name b_date e_date l_id
A 1008000138 299船 20027834 钱 长城 2013-06-14 9999-12-31 1008000003
A 1008000138 299船 20027834 钱 长城 2013-06-14 9999-12-31 1008000005
*/
CREATE TABLE [dbo].[people](
[s_type] [varchar](50) NOT NULL,
[c_id] [int] NOT NULL,
[c_name] [varchar](50) NOT NULL,
[p_id] [int] NOT NULL,
[p_name] [varchar](50) NOT NULL,
[b_date] [date] NOT NULL,
[e_date] [date] NOT NULL,
[l_id] [nvarchar](max) NOT NULL
)
--truncate table people
bulk insert people from 'c:\aa.txt' with(fieldterminator=';',rowterminator='\n')
select * from people
update people
set l_id = left(l_id,len(l_id)-1)
/*
s_type c_id c_name p_id p_name b_date e_date l_id
A 1008000138 299船 20027834 钱 长城 2013-06-14 9999-12-31 1008000003
A 1008000138 299船 20027834 钱 长城 2013-06-14 9999-12-31 1008000005
*/