Import a TXT without problems,

 
If object_id (' tempdb for. Dbo. # test1 ') is not null drop table # test1 
Go 
The create table # test1 ([notext] nvarchar (20)) 

BULK INSERT # test1 FROM 'E: \ LOTTData \ hellome \ 24 TXT' 
WITH 
(
FIELDTERMINATOR=", 
ROWTERMINATOR='\ n' 
) 

GO 
SELECT * FROM # test1 

But
In the folder hellome have 1, 2, 3, 4,,,,,,, 333. TXT text documents, a total of several hundred TXT text documents, TXT text document file names for the digital
1. TXT
2. TXT
3. TXT
.
.
1223. TXT

 
Declare @ SQL nvarchar (20), @ I int 
Select SQL="@, @ I=1223 
While @ i> 0 begin 
SQL="insert INTO the set @ # test1 
Select the FROM OPENDATASOURCE (. ' 'MICROSOFT JET. The OLEDB. 4.0 "', 'Text; The DATABASE=E: \ LOTTData \ hellome \ ')... [' + convert (varchar, @ I) + '# TXT]' 
The exec (@ SQL) 
@ I=@ I - 1 
End 

SQL all TXT in the NOT NULL record (NOT empty data, each row should be no comma separator) one-time import multiple TXT to list, but run times wrong, how to change?
TXT text documents in the empty data format is 1, 2, 3, 4, 5 in a row at the same time requirement: field notext format of 01 02, 03 04 05

CodePudding user response:

 
If object_id (' tempdb for. Dbo. # test1 ') is not null drop table # test1 
Go 
The create table # test1 ([notext] nvarchar (20)) 
DECLARE @ SQL NVARCHAR (MAX), @ INT I; 
SELECT @ SQL=N '@ I=1223; 
WHILE @ I & gt; 0 
The BEGIN 
SET @ SQL=N 'BULK INSERT # test1 FROM' E: \ LOTTData \ hellome \ '+ CONVERT (VARCHAR, @ I) 
+ N ". TXT "' WITH 
(
FIELDTERMINATOR=' ' ' ', 
ROWTERMINATOR='\ n' 
) '; 
PRINT @ SQL; 
The EXEC (@ SQL); 
The SET @ I=@ I - 1; 
END; 

CodePudding user response:

The

Hello World, 1/f, reference response:

 
If object_id (' tempdb for. Dbo. # test1 ') is not null drop table # test1 
Go 
The create table # test1 ([notext] nvarchar (20)) 
DECLARE @ SQL NVARCHAR (MAX), @ INT I; 
SELECT @ SQL=N '@ I=1223; 
WHILE @ I & gt; 0 
The BEGIN 
SET @ SQL=N 'BULK INSERT # test1 FROM' E: \ LOTTData \ hellome \ '+ CONVERT (VARCHAR, @ I) 
+ N ". TXT "' WITH 
(
FIELDTERMINATOR=' ' ' ', 
ROWTERMINATOR='\ n' 
) '; 
PRINT @ SQL; 
The EXEC (@ SQL); 
The SET @ I=@ I - 1; 
END; 

reference

message 102, level 15, state 1, line 9
"" near a syntax error,

later the original error is the same as
Trouble you

CodePudding user response:

 
If object_id (' tempdb for. Dbo. # test1 ') is not null drop table # test1 
Go 
The create table # test1 ([notext] nvarchar (20)) 
DECLARE @ SQL NVARCHAR (MAX), @ INT I; 
SELECT @ SQL=N '@ I=1223; 
WHILE @ I & gt; 0 
The BEGIN 
SET @ SQL=N 'BULK INSERT # test1 FROM' E: \ LOTTData \ hellome \ '+ CONVERT (VARCHAR, @ I) + N ". TXT "' 
; WITH 
(
FIELDTERMINATOR=' ' ' ', 
ROWTERMINATOR='\ n' 
); 
PRINT @ SQL; 
The EXEC (@ SQL); 
The SET @ I=@ I - 1; 
END; 

CodePudding user response:

Modify the path when the quotes deleted one more, the path is in front of the two single quotes:

 

If object_id (' tempdb for. Dbo. # test1 ') is not null drop table # test1 
Go 
The create table # test1 ([notext] nvarchar (20)) 
DECLARE @ SQL NVARCHAR (MAX), @ INT I; 
SELECT @ SQL=N '@ I=1223; 
WHILE @ I & gt; 0 
The BEGIN 
SET @ SQL=N 'BULK INSERT # test1 FROM' 'E: \ LOTTData \ hellome \' + CONVERT (VARCHAR, @ I) 
+ N ". TXT "' WITH 
(
FIELDTERMINATOR=' ' ' ', 
ROWTERMINATOR='\ n' 
) '; 
PRINT @ SQL; 
The EXEC (@ SQL); 
The SET @ I=@ I - 1; 
END; 

CodePudding user response:

Thank god

Hello World reference 4 floor, response:

modify the path when the quotes deleted one more, the path is in front of the two single quotes:
Increased the final code, TXT text document non-empty data format is 1, 2, 3, 4, 5 in a row in the replace become 1, 2, 3, 4, 5, but the format of 01 02, 03 04 05 couldn't write the code

 
If object_id (' tempdb for. Dbo. # test1 ') is not null drop table # test1 
Go 
The create table # test1 ([notext] nvarchar (20)) 

DECLARE @ SQL NVARCHAR (MAX), @ INT I; 
SELECT @ SQL=N '@ I=70; 
WHILE @ I & gt; 0 
The BEGIN 
SET @ SQL=N 'BULK INSERT # test1 FROM' 'E: \ LOTTData \ hellome \' + CONVERT (VARCHAR, @ I) 
+ N ". TXT "' WITH 
(
FIELDTERMINATOR=' ' ' ', 
ROWTERMINATOR='\ n' 
) '; 
PRINT @ SQL; 
The EXEC (@ SQL); 
The SET @ I=@ I - 1; 
END; 

Select *, replace (notext, ', ', ') AS rep 
The from # test1 where notext is not null 

Please put the output format for the 01, 02, 03 04 05 hope to learn this technique