1

Topic: bulk insert in a cycle

Greetings,
There are 1000 files.csv which need to be loaded in the table.
How  xp_cmdshell or something another that not to register 1000 times a way to files?

2

Re: bulk insert in a cycle

For example preempt in the temporary table of a title of your files and then substitute their titles in dynamic request in a cycle
Create table #t (file_name varchar (max))
INSERT #t
EXEC xp_cmdshell ' dir/b "C:\*.CSV ';

3

Re: bulk insert in a cycle

assmsk;
Thanks for council, but any more problem how to preempt this list in a label and as the cursor c bulk th to write.
How instead of to substitute this list?
BULK INSERT #t_vasya
FROM ' C:\cur_bulk_280218 \' - to change necessarily
WITH
(
firstrow = 2;
MAXERRORS = 0;
FIELDTERMINATOR = '\t ';
ROWTERMINATOR = '\n ';
DATAFILETYPE ='widechar ';
CODEPAGE = ' ACP'
);
go

4

Re: bulk insert in a cycle

To you already answered.

assmsk wrote:

For example preempt in the temporary table of a title of your files and then substitute their titles in dynamic request in a cycle
Create table #t (file_name varchar (max))
INSERT #t
EXEC xp_cmdshell ' dir/b "C:\*.CSV ';

5

Re: bulk insert in a cycle

I hope understand idea
Declare @t table (nn int identity, file_name varchar (max))
Declare @i int = 1
Declare @strSQL varchar (max)
INSERT @t
EXEC xp_cmdshell ' dir/b "C:\cur_bulk_280218\*.CSV ';
While @i <= (Select max (nn) from @t)
begin
Set @strSQL = ' BULK INSERT # ' + (Select file_name from @t where nn = @i) +
' FROM ' C:\cur_bulk_280218 \' + (Select file_name from @t where nn = @i) +
' WITH
(
firstrow = 2;
MAXERRORS = 0;
FIELDTERMINATOR = "t";
ROWTERMINATOR = "n";
DATAFILETYPE = "widechar";
CODEPAGE = "ACP"
);'
exec (@strSQL)
Set @i = @i + 1
end

6

Re: bulk insert in a cycle

Thanks big, I will study now.
And so it does not turn out?
While an error
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "+ @a +" does not exist.
In a file ways to 1000 files lie.
DECLARE @a VARCHAR (50);
SET @a ='C:\2.txt'
DECLARE @b VARCHAR (500);
SET @b ='BULK INSERT t_konkud_test_bulk
FROM "+ a +"
WITH
(MAXERRORS = 0;
FIELDTERMINATOR = "t";
ROWTERMINATOR = "n";
DATAFILETYPE = "widechar";
CODEPAGE = "ACP")'
exec (@b);

7

Re: bulk insert in a cycle

assmsk;
Declare @tf table (nn int identity, file_name varchar (max))
Declare @i int = 1
Declare @strSQL varchar (max)
--drop table #t1
insert into @tf
exec master.dbo.xp_cmdshell ' dir/b C:\work_ne_trogat/O:E'
--select * from @tf
--Select file_name from @tf
While @i <= (Select max (nn) from @tf)
begin
Set @strSQL = ' BULK INSERT t_vasya ' + (Select file_name from @tf where nn = @i) +
' FROM ' ' C:\work_ne_trogat \"+ (Select file_name from @tf where nn = @i) +
"WITH
(
firstrow = 1
,FIELDTERMINATOR = ";"
,ROWTERMINATOR = "n"
,tablock
);'
exec (@strSQL)
Set @i = @i + 1
end
Like it is already close, but while  the sea of the same errors
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' January '.
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@tf".
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string'
,tablock
);'.
At me suspicions that do not suffice something length of a line, but I do not understand to that... A MB who sees the reason?

8

Re: bulk insert in a cycle

svanrus;
And what your table @tf should be visible in EXEC ()???

9

Re: bulk insert in a cycle

With  at you delirium any
As that so probably

wrote:

Set @strSQL =
' BULK INSERT t_vasya ' + (Select file_name from @tf where nn = @i) +
' FROM ' ' C:\work_ne_trogat \' "+ (Select file_name from @tf where nn = @i) +
' WITH
(
firstrow = 1
,FIELDTERMINATOR = ";"
,ROWTERMINATOR = "n"
,tablock
);'

10

Re: bulk insert in a cycle

While @i <= (Select max (nn) from @tf)

But not the cursor! .

11

Re: bulk insert in a cycle

It seems to me so more correctly:

Set @strSQL =
' BULK INSERT t_vasya ' + (Select replace (file_name, ' ', ") from @tf where nn = @i) +
'>>> <<<FROM ' ' C:\work_ne_trogat \>>> ' <<<+ (Select file_name from @tf where nn = @i) +
'>>> "<<<WITH
(
firstrow = 1
,FIELDTERMINATOR = ";"
,ROWTERMINATOR = "n"
,tablock
);'

12

Re: bulk insert in a cycle

Kopelly;
and so

Declare @t table (nn int identity, file_name varchar (max))
Declare @i int = 1
Declare @strSQL varchar (max)
INSERT @t
EXEC xp_cmdshell ' dir/b "e:\*.CSV ';
SELECT @strSQL = ISNULL (@strSQL, ") +
' BULK INSERT # ' + [file_name] + ' FROM "C:\cur_bulk_280218 \'" + [file_name] +
' WITH
(
firstrow = 2;
MAXERRORS = 0;
FIELDTERMINATOR = "t";
ROWTERMINATOR = "n";
DATAFILETYPE = "widechar";
CODEPAGE = "ACP"
);' + CHAR (13)
FROM @t
WHERE [file_name] IS NOT NULL
EXEC (@strSQL)

13

Re: bulk insert in a cycle

TaPaK wrote:

Kopelly;
and so

Almost it turned out! It is necessary to place correctly inverted commas...

14

Re: bulk insert in a cycle

Damirovich wrote:

is passed...
Almost it turned out! It is necessary to place correctly inverted commas...

Have a good time

15

Re: bulk insert in a cycle

Svanrus, if problems  dynamic request at first deliver print instead of exec visually to understand in what place at you the error

16

Re: bulk insert in a cycle

TaPaK wrote:

have a good time

() after the assembly to finish a file...

DECLARE @t TABLE ([nn] INT IDENTITY (1,1), [filename] VARCHAR (MAX))
DECLARE @tSQL NVARCHAR (MAX) = N"
INSERT @t ([filename])
EXEC xp_cmdshell ' dir/b "C:\cur_bulk_280218\*.csv" ';
SELECT @tSQL + =
N'BULK INSERT
' + QUOTENAME (' t_vasya _ ' + @filename) + N'
FROM
' + QUOTENAME (' C:\cur_bulk_280218 \' + @filename, ' "') + N'
WITH (
FIRSTROW = 2;
MAXERRORS = 0;
FIELDTERMINATOR = "t";
ROWTERMINATOR = "n";
DATAFILETYPE = "WIDECHAR";
CODEPAGE = "ACP"
);' + CHAR (13)
FROM @t
WHERE [file_name] IS NOT NULL
EXECUTE sp_executesql @tSQL

17

Re: bulk insert in a cycle

Ruslan Damirovich;
fail, saw further

18

Re: bulk insert in a cycle

Thanks all!

19

Re: bulk insert in a cycle

svanrus;
If learn will not work with Integration Services similar tasks to cause any problems.

20

Re: bulk insert in a cycle

Kolosov wrote:

svanrus;
If learn will not work with Integration Services similar tasks to cause any problems.

   smile)) powershell our all