select 15,34 union all
select 15,35 union all
select 8,36 union all
select 8,37 union all
select 8,38 union all
select 8,39 union all
不够优雅
53 0
48 1
54 2
48 3
42 4
36 5
35 6
35 7
29 8
29 9
30 10
23 11
23 12
23 13
23 14
23 15
18 16
20 17
18 18
18 19
18 20
17 21
18 22
19 23
14 24
10 25
12 26
13 27
14 28
13 29
13 30
15 31
16 32
8 33
15 34
15 35
8 36
8 37
8 38
8 39
9 40
select 15,35 union all
select 8,36 union all
select 8,37 union all
select 8,38 union all
select 8,39 union all
不够优雅
53 0
48 1
54 2
48 3
42 4
36 5
35 6
35 7
29 8
29 9
30 10
23 11
23 12
23 13
23 14
23 15
18 16
20 17
18 18
18 19
18 20
17 21
18 22
19 23
14 24
10 25
12 26
13 27
14 28
13 29
13 30
15 31
16 32
8 33
15 34
15 35
8 36
8 37
8 38
8 39
9 40
存在的话 用
insert into
create table [tb]([count] int,[id] int)
insert [tb]
select 53,0 union all
select 48,1 union all
select 54,2 union all
select 48,3 union all
select 42,4 union all
select 36,5 union all
select 35,6 union all
select 35,7 union all
select 29,8 union all
select 29,9 union all
select 30,10 union all
select 23,11 union all
select 23,12 union all
select 23,13 union all
select 23,14 union all
select 23,15 union all
select 18,16 union all
select 20,17 union all
select 18,18 union all
select 18,19 union all
select 18,20 union all
select 17,21 union all
select 18,22 union all
select 19,23 union all
select 14,24 union all
select 10,25 union all
select 12,26 union all
select 13,27 union all
select 14,28 union all
select 13,29 union all
select 13,30 union all
select 15,31 union all
select 16,32 union all
select 8,33 union all
select 15,34 union all
select 15,35 union all
select 8,36 union all
select 8,37 union all
select 8,38 union all
select 8,39 union all
select 9,40select * from [tb]
create table tb(
col1 varchar(100),
col2 varchar(100)
)
goBULK
INSERT tb
FROM 'd:\test.txt'
WITH (
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
)
goselect * from tbdrop table tb
--文件c:\test1.txt内容:
--------------------------------------
53,0
48,1
54,2
....
--------------------------------------
--文件 c:\value.fmt内容:
--------------------------------------9.0
2
1 SQLCHAR 0 4 "," 1 Col1 SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 0 4 "\r\n" 2 Col2 SQL_Latin1_General_Cp437_BIN--------------------------------------
--导入语句
--------------------------------------SELECT a.* into T FROM OPENROWSET( BULK 'c:\test1.txt',
FORMATFILE = 'c:\value.fmt') AS a;
declare @p1 int, @p0 int;
declare @loop_flag int;
declare @tb table (col1 int not null, col2 int not null);
declare @col1 int, @col2 int ;set @s='53 0
48 1
54 2
48 3
42 4
36 5
35 6
35 7
29 8
29 9
30 10
23 11
23 12
23 13
23 14
23 15
18 16
20 17
18 18
18 19
18 20
17 21
18 22
19 23
14 24
10 25
12 26
13 27
14 28
13 29
13 30
15 31
16 32
8 33
15 34
15 35
8 36
8 37
8 38
8 39
9 40 ';set @splitter = char(13) + char(10); --回车换行作为分割符号
set @s = replace(@s, char(9), ''); --消除tab符号
set @s = ltrim(rtrim(@s));--下面的循环把源字串格式化为 {值}+{空格}+{值}
set @p0 = charindex(' ', @s, 1);
while @p0 <> 0
begin
set @s = replace(@s,' ',' ');
set @p0 = charindex(' ', @s, 1);
end
--print @s;--下面的把格式化后的字串分解, 插入到定义的内存表@tb中
set @p0 = 1;
set @loop_flag = 1 ;print @p1;
while @loop_flag=1
begin
set @p1 = charindex(@splitter, @s, @p0);
if @p1 = 0
begin
set @p1 = len(@s) + 1;
set @loop_flag = 0 ;
end
set @s1 = rtrim(ltrim(substring(@s, @p0,@p1-@p0)));
if charindex(' ',@s1,1) > 1
begin
set @col1 = cast(left(@s1,charindex(' ',@s1,1)-1) as int);
set @col2 = cast(right(@s1,len(@s1)- charindex(' ',@s1,1)) as int) ;
insert into @tb(col1, col2) values(@col1, @col2);
end
set @p0 = @p1 + len(@splitter);
set @p1 = charindex(@splitter, @s, @p0);
endselect * from @tb ;