BULK INSERT datafile parameter

Hi everyone,
how can I pass a variable to the command "BULK INSERT", to the datafile
parameter? The following script:
create procedure some_procedure (@.filename varchar(256)) as
begin
bulk insert some_table from @.filename with(codepage='raw');
end;
fails with error "Incorrect syntax near '@.filename'". Any idea?
Thanks,
Tamas Beri
Create a string with the bulk insert command and exec it ie
exec('bulk insert sometable from ' + @.filename + yad yada)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gfoyle" <gfoyle@.discussions.microsoft.com> wrote in message
news:546E659E-ABC3-4B34-B6D5-2EB647E3A6A9@.microsoft.com...
> Hi everyone,
> how can I pass a variable to the command "BULK INSERT", to the datafile
> parameter? The following script:
> create procedure some_procedure (@.filename varchar(256)) as
> begin
> bulk insert some_table from @.filename with(codepage='raw');
> end;
> fails with error "Incorrect syntax near '@.filename'". Any idea?
> Thanks,
> Tamas Beri
>
|||Thanks,
it's finally working with the exec...
"Wayne Snyder" wrote:

> Create a string with the bulk insert command and exec it ie
> exec('bulk insert sometable from ' + @.filename + yad yada)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com

BULK INSERT datafile parameter

Hi everyone,
how can I pass a variable to the command "BULK INSERT", to the datafile
parameter? The following script:
create procedure some_procedure (@.filename varchar(256)) as
begin
bulk insert some_table from @.filename with(codepage='raw');
end;
fails with error "Incorrect syntax near '@.filename'". Any idea?
Thanks,
Tamas BeriCreate a string with the bulk insert command and exec it ie
exec('bulk insert sometable from ' + @.filename + yad yada)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gfoyle" <gfoyle@.discussions.microsoft.com> wrote in message
news:546E659E-ABC3-4B34-B6D5-2EB647E3A6A9@.microsoft.com...
> Hi everyone,
> how can I pass a variable to the command "BULK INSERT", to the datafile
> parameter? The following script:
> create procedure some_procedure (@.filename varchar(256)) as
> begin
> bulk insert some_table from @.filename with(codepage='raw');
> end;
> fails with error "Incorrect syntax near '@.filename'". Any idea?
> Thanks,
> Tamas Beri
>|||Thanks,
it's finally working with the exec...
"Wayne Snyder" wrote:
> Create a string with the bulk insert command and exec it ie
> exec('bulk insert sometable from ' + @.filename + yad yada)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com

BULK INSERT datafile parameter

Hi everyone,
how can I pass a variable to the command "BULK INSERT", to the datafile
parameter? The following script:
create procedure some_procedure (@.filename varchar(256)) as
begin
bulk insert some_table from @.filename with(codepage='raw');
end;
fails with error "Incorrect syntax near '@.filename'". Any idea?
Thanks,
Tamas BeriCreate a string with the bulk insert command and exec it ie
exec('bulk insert sometable from ' + @.filename + yad yada)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gfoyle" <gfoyle@.discussions.microsoft.com> wrote in message
news:546E659E-ABC3-4B34-B6D5-2EB647E3A6A9@.microsoft.com...
> Hi everyone,
> how can I pass a variable to the command "BULK INSERT", to the datafile
> parameter? The following script:
> create procedure some_procedure (@.filename varchar(256)) as
> begin
> bulk insert some_table from @.filename with(codepage='raw');
> end;
> fails with error "Incorrect syntax near '@.filename'". Any idea?
> Thanks,
> Tamas Beri
>|||Thanks,
it's finally working with the exec...
"Wayne Snyder" wrote:

> Create a string with the bulk insert command and exec it ie
> exec('bulk insert sometable from ' + @.filename + yad yada)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com

BULK INSERT - escape " - .fmt

Hi All,
Simple table:
TABLE:
create table employee (
name char(20),
title char(120)
)
DATAFILE(.csv):
name;title
Gustavo;"Marketing Assistant"
Catherine;"Engineering Manager"
I'd like use BULK INSERT command to insert this data. Tell me please,
how should look formatfile (.fmt) for this example (SQL 2005). I need
data after BULK INSERT looks like this:
name title
-- --
Gustavo Marketing Assistant
Catherine Engineering Manager
NOT like this:
name title
-- --
Gustavo "Marketing Assistant"
Catherine "Engineering Manager"
So, " should be escape.
Thank you
--
RegardsHello,
I've done this, it's almost finished. You can tray this:
/
***************************************************************************************************/
--table
CREATE TABLE [dbo].[ImportTest](
[fname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[lname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[organization] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[address] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[zip] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[city] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[state] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[email] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
--data file (ImportTest.csv), REMEMBER at the end you HAVE TO add
ENTER
fname;lname;organization;address;zip;city;state;email;
Shirley;Birosik;;;90013;"Los Angeles";"CA";"sbirosik@.gmail.com";
Dennis;Eschen;;;94920;"Alhambra";"CA";"de@.hotmail.com";
Valerie;Chambers;;;91803;"Long Beach";"CA";"vweber@.verizon.net";
--fmt file(ImportTest.Fmt), add ENTER at the end
9.0
8
1 SQLCHAR 0 510 ";" 1
fname Latin1_General_CI_AS
2 SQLCHAR 0 510 ";" 2
lname Latin1_General_CI_AS
3 SQLCHAR 0 510 ";" 3
organization Latin1_General_CI_AS
4 SQLCHAR 0 510 ";" 4
address Latin1_General_CI_AS
5 SQLCHAR 0 510 ";\"" 5
zip Latin1_General_CI_AS
6 SQLCHAR 0 510 "\";\"" 6
city Latin1_General_CI_AS
7 SQLCHAR 0 510 "\";\"" 7
state Latin1_General_CI_AS
8 SQLCHAR 0 510 "\r\n" 8
email Latin1_General_CI_AS
bulk insert ImportTest
from 'd:\bulk\ImportTest.csv'
with (
formatfile = 'd:\bulk\ImportTest.Fmt',
firstrow = 2
)
--(2 row(s) affected)
select*
from ImportTest
Dennis Eschen NULL NULL 94920 Alhambra CA
de@.hotmail.com";
Valerie Chambers NULL NULL 91803 Long Beach CA
vweber@.verizon.net";
/
***************************************************************************************************/
For me now most important is why didn't insert first row (data row,
not column definition)?
When I added quotation mark to column definition works fine, but I
can't modify .csv file, I received it from third part company :( is it
possible add all rows without modification .csv file?
Second, how remove quotation mark and semicolon from last column?
Regards,
anxcomp

Bulk Import Large Datafile

Just wonding if there is any optimization that can be done to import a large
file faster into SQL Server. Am using BULK INSERT with a rowsize of 100000.
The text file for the import is 159gig so this a big one. Any tips are
appreciated.
TIA> Just wonding if there is any optimization that can be done to import a
large
> file faster into SQL Server. Am using BULK INSERT with a rowsize of
100000.
> The text file for the import is 159gig so this a big one. Any tips are
> appreciated.
You can use bulk insert with a tablock. This way you could load the file
parallelly using multiple bulk loads, you could set the batch size or split
up the text file into multiple files.
Remeber to drop indexes on target table for performance.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi.
three four things to keep in mind
first put the database in bulk-logged mode ( if logs are not atall
important - make 'trunc. log on chkpt' to true)
second turn off auto update statistics/ auto create statistics
drop indexes
restrict access to dbo
also if u can split this text file in to n number of txt files. u can start
multiple bul inserts parallely.
Enjoy SQL
Regards,
Mayur
"DWinter" <dwinter@.attbi.com> wrote in message
news:%23eo8Ehn7DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Just wonding if there is any optimization that can be done to import a
large
> file faster into SQL Server. Am using BULK INSERT with a rowsize of
100000.
> The text file for the import is 159gig so this a big one. Any tips are
> appreciated.
> TIA
>

Bulk Import Large Datafile

Just wonding if there is any optimization that can be done to import a large
file faster into SQL Server. Am using BULK INSERT with a rowsize of 100000.
The text file for the import is 159gig so this a big one. Any tips are
appreciated.
TIA> Just wonding if there is any optimization that can be done to import a
large
> file faster into SQL Server. Am using BULK INSERT with a rowsize of
100000.
> The text file for the import is 159gig so this a big one. Any tips are
> appreciated.
You can use bulk insert with a tablock. This way you could load the file
parallelly using multiple bulk loads, you could set the batch size or split
up the text file into multiple files.
Remeber to drop indexes on target table for performance.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||I would consider turning off 'auto create stats' and 'auto
update stats' on your database before performing any
import. Then when the import has completed - run update
statistics manually...
I've seen this have a significant impact on some bulk
loads in the past - but you really need to test this
option to see how much it is applicable to your situation.
>--Original Message--
>Just wonding if there is any optimization that can be
done to import a large
>file faster into SQL Server. Am using BULK INSERT with a
rowsize of 100000.
>The text file for the import is 159gig so this a big one.
Any tips are
>appreciated.
>TIA
>
>.
>|||Hi.
three four things to keep in mind
first put the database in bulk-logged mode ( if logs are not atall
important - make 'trunc. log on chkpt' to true)
second turn off auto update statistics/ auto create statistics
drop indexes
restrict access to dbo
also if u can split this text file in to n number of txt files. u can start
multiple bul inserts parallely.
Enjoy SQL
Regards,
Mayur
"DWinter" <dwinter@.attbi.com> wrote in message
news:%23eo8Ehn7DHA.1040@.TK2MSFTNGP10.phx.gbl...
> Just wonding if there is any optimization that can be done to import a
large
> file faster into SQL Server. Am using BULK INSERT with a rowsize of
100000.
> The text file for the import is 159gig so this a big one. Any tips are
> appreciated.
> TIA
>