Bulk Insert question

Hello,
I use the following statement to insert a string of 35 characters into a
table (I am planning to figure out a suitable .FMT file for parsing this int
o
the right column definitions, but that is after I figure out this current
problem described below)
BULK INSERT MyTestTable FROM '<UNCname-FileLocation>\MyTextFile.txt' WITH
(FIELDTERMINATOR = '\0',ROWTERMINATOR = '\n')
MyTestTable is currently defined as
CREATE TABLE MyTestTable (Col1 CHAR(35))
and some sample test data from MyTextFile.txt is as follows
ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789
123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
(length of the data is always 36 bytes and the length of the filename is
always 14 bytes)
(As you can tell, the DDL and data is what I am using for this test)
This BULK INSERT works fine, but what I need to do is to get the name of the
file (MyTextFile.txt in this case) appended to the end of the column.
So, the table definition would change to
CREATE TABLE MyTestTable (Col1 CHAR(49))
I am clueless about how to get the name of the file (which will vary at
runtime) into each row of the MyTestTable that gets affected by this BULK
INSERT.
(I will be inserting multiple files - different file names - to the same
table one after the other and would like to have the filename stored in a
separate column or appended to the column - either way. When I build the .FM
T
file, I will split this into the appropriate columns and will change the
table definition.
Any suggestions would be appreciated. Please let me know if any further
details are needed.
Thanks!you could probably write a TSQL block to read filename before inserting the
data with bulk insert. and then insert data, and update data with appending
the file name to recently inserted records.
hth,
avnrao
http://avnrao.blogspot.com
"Bob" wrote:

> Hello,
> I use the following statement to insert a string of 35 characters into
a
> table (I am planning to figure out a suitable .FMT file for parsing this i
nto
> the right column definitions, but that is after I figure out this current
> problem described below)
> BULK INSERT MyTestTable FROM '<UNCname-FileLocation>\MyTextFile.txt' WITH
> (FIELDTERMINATOR = '\0',ROWTERMINATOR = '\n')
> MyTestTable is currently defined as
> CREATE TABLE MyTestTable (Col1 CHAR(35))
> and some sample test data from MyTextFile.txt is as follows
> ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789
> 123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
> (length of the data is always 36 bytes and the length of the filename is
> always 14 bytes)
> (As you can tell, the DDL and data is what I am using for this test)
> This BULK INSERT works fine, but what I need to do is to get the name of t
he
> file (MyTextFile.txt in this case) appended to the end of the column.
> So, the table definition would change to
> CREATE TABLE MyTestTable (Col1 CHAR(49))
> I am clueless about how to get the name of the file (which will vary at
> runtime) into each row of the MyTestTable that gets affected by this BULK
> INSERT.
> (I will be inserting multiple files - different file names - to the same
> table one after the other and would like to have the filename stored in a
> separate column or appended to the column - either way. When I build the .
FMT
> file, I will split this into the appropriate columns and will change the
> table definition.
> Any suggestions would be appreciated. Please let me know if any further
> details are needed.
> Thanks!
>|||Bob,
I'm currently doing a migration which has between 40 and 50 text files as
the datasource from a mainframe. Their names can change so I use a batch
file to handle this. eg in my batch file,
Call dir with simple header, full filename options to list the files
required into :\temp\filelist.txt
Your file list should be a single column with the complete filepath and
filename
Upload the list to the server using bcp
Note: bcp into a view which has only one column, the filename
If you need more details post back. Basicallly, even if it's a bit
old-fashioned, DOS already has the commands for working with files. There's
always DTS but I'm not a fan ... ; )
Damien
"Bob" wrote:

> Hello,
> I use the following statement to insert a string of 35 characters into
a
> table (I am planning to figure out a suitable .FMT file for parsing this i
nto
> the right column definitions, but that is after I figure out this current
> problem described below)
> BULK INSERT MyTestTable FROM '<UNCname-FileLocation>\MyTextFile.txt' WITH
> (FIELDTERMINATOR = '\0',ROWTERMINATOR = '\n')
> MyTestTable is currently defined as
> CREATE TABLE MyTestTable (Col1 CHAR(35))
> and some sample test data from MyTextFile.txt is as follows
> ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789
> 123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
> (length of the data is always 36 bytes and the length of the filename is
> always 14 bytes)
> (As you can tell, the DDL and data is what I am using for this test)
> This BULK INSERT works fine, but what I need to do is to get the name of t
he
> file (MyTextFile.txt in this case) appended to the end of the column.
> So, the table definition would change to
> CREATE TABLE MyTestTable (Col1 CHAR(49))
> I am clueless about how to get the name of the file (which will vary at
> runtime) into each row of the MyTestTable that gets affected by this BULK
> INSERT.
> (I will be inserting multiple files - different file names - to the same
> table one after the other and would like to have the filename stored in a
> separate column or appended to the column - either way. When I build the .
FMT
> file, I will split this into the appropriate columns and will change the
> table definition.
> Any suggestions would be appreciated. Please let me know if any further
> details are needed.
> Thanks!
>|||avnrao, thank you for your quick response. I had considered this option too,
but the problem is that this load process is supposed to run once a month an
d
with all the 100 files combined, I will have totally about 600 million rows
to process - I guess I was hoping that BULK INSERT would allow us to process
the filename along with it - Any other means of achieving this (instead of
Bulk Insert) would be welcome too.
Thanks!
"avnrao" wrote:
> you could probably write a TSQL block to read filename before inserting th
e
> data with bulk insert. and then insert data, and update data with appendin
g
> the file name to recently inserted records.
> hth,
> avnrao
> http://avnrao.blogspot.com
> "Bob" wrote:
>|||Damien,
I'm ok with using DOS commands too - anything that can get this done
(without too much of a performance hit). :)
I do have the list of filenames available (and I can get it into a SQL table
too), but the problem is how to update the filenames onto the MyTestTable...
I guess I didn't quite understand the suggestion - bcp into the view (and
this view should be for the MyTestTable table?) - but how would I be able to
associate the multiple records from the file with the filename that I am
BCPing?
Sorry if I misunderstood you... but could you clarify a little on this?
Thanks again!
"Damien" wrote:
> Bob,
> I'm currently doing a migration which has between 40 and 50 text files as
> the datasource from a mainframe. Their names can change so I use a batch
> file to handle this. eg in my batch file,
> Call dir with simple header, full filename options to list the files
> required into :\temp\filelist.txt
> Your file list should be a single column with the complete filepath and
> filename
> Upload the list to the server using bcp
> Note: bcp into a view which has only one column, the filename
> If you need more details post back. Basicallly, even if it's a bit
> old-fashioned, DOS already has the commands for working with files. There
's
> always DTS but I'm not a fan ... ; )
>
> Damien
>
>
> "Bob" wrote:
>|||Ah,
well I cheated a little bit here. I used SQL to write the batch file for
me, and I use osql to fire off an ALTER TABLE to set the default for the
column.
So, from Query Analyser, write a query which selects your records, but
create a bcp string. This script will create a meaninful looking batch file
but obviously you can't bcp into temp tables:
DROP TABLE #import_files
CREATE TABLE #import_files ( file_id INT UNIQUE IDENTITY NOT NULL, file_name
VARCHAR(30) NOT NULL )
DROP TABLE #raw_data
CREATE TABLE #raw_data ( record_id INT UNIQUE IDENTITY NOT NULL, file_id INT
NOT NULL, record CHAR(36) )
ALTER TABLE #raw_data ADD CONSTRAINT def_raw_data__file_id DEFAULT -1 FOR
file_id
GO
SET NOCOUNT ON
INSERT INTO #import_files ( file_name ) VALUES ( 'test1.txt' )
INSERT INTO #import_files ( file_name ) VALUES ( 'test2.txt' )
SET NOCOUNT OFF
GO
DROP TABLE #batch_file
CREATE TABLE #batch_file ( file_id INT, sort_id INT, command VARCHAR( 500 )
)
GO
--
SET NOCOUNT ON
-- Section header
INSERT INTO #batch_file
SELECT file_id, 10, 'REM bcp file ' + CAST( file_id AS VARCHAR ) + ' - ' +
file_name
FROM #import_files
-- Drop the default
INSERT INTO #batch_file
SELECT file_id, 20, 'osql -Syourserver -dyourdatabase -Ulogin_id -Ppassword
-q"ALTER TABLE #raw_data DROP CONSTRAINT def_raw_data__file_id'
FROM #import_files
-- Set the default
INSERT INTO #batch_file
SELECT file_id, 30, 'osql -Syourserver -dyourdatabase -Ulogin_id -Ppassword
-q"ALTER TABLE #raw_data ADD CONSTRAINT def_raw_data__file_id DEFAULT ' +
CAST( file_id AS CHAR ) + ' FOR file_id"'
FROM #import_files
-- bcp the file
INSERT INTO #batch_file
SELECT file_id, 40, 'bcp -iyou get the idea.txt ; )'
FROM #import_files
-- Make a gap
INSERT INTO #batch_file
SELECT file_id, 50, ''
FROM #import_files
SELECT command
FROM #batch_file
ORDER BY file_id, sort_id
SET NOCOUNT OFF
Now, save the results as a batch file, remove the dashes from the top and
away you go. I actually use a similar structure in the migration, only it's
a bit more complex, plus it's wrapped in a stored procedure and paramterized
so it's nice and flexible.
If it seems like a lot of hard work, then perhaps this isn't the solution
for you, but it's worked for me!
Let me know how you get on.
Damien|||Bob,
If you are moving to SQL Server 2005, you might consider using
the BULK rowset provider. Existing (in SQL Server 2000) text
providers might also work, but may not be as fast.
DECLARE @.f nvarchar(200)
SET @.f = 'c:\test\values.txt'
INSERT INTO MyTestTable
SELECT colFromTextFile + @.f
SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
FORMATFILE = 'c:\test\values.fmt') AS a;
Steve Kass
Drew University
Bob wrote:

>Hello,
> I use the following statement to insert a string of 35 characters into
a
>table (I am planning to figure out a suitable .FMT file for parsing this in
to
>the right column definitions, but that is after I figure out this current
>problem described below)
>BULK INSERT MyTestTable FROM '<UNCname-FileLocation>\MyTextFile.txt' WITH
>(FIELDTERMINATOR = '\0',ROWTERMINATOR = '\n')
>MyTestTable is currently defined as
>CREATE TABLE MyTestTable (Col1 CHAR(35))
>and some sample test data from MyTextFile.txt is as follows
>ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789
>123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
>(length of the data is always 36 bytes and the length of the filename is
>always 14 bytes)
>(As you can tell, the DDL and data is what I am using for this test)
>This BULK INSERT works fine, but what I need to do is to get the name of th
e
>file (MyTextFile.txt in this case) appended to the end of the column.
>So, the table definition would change to
>CREATE TABLE MyTestTable (Col1 CHAR(49))
>I am clueless about how to get the name of the file (which will vary at
>runtime) into each row of the MyTestTable that gets affected by this BULK
>INSERT.
>(I will be inserting multiple files - different file names - to the same
>table one after the other and would like to have the filename stored in a
>separate column or appended to the column - either way. When I build the .F
MT
>file, I will split this into the appropriate columns and will change the
>table definition.
>Any suggestions would be appreciated. Please let me know if any further
>details are needed.
>Thanks!
>
>|||Thanks Damien - yes, this would work (now, I just have to get the DBA to
approve of building and dropping the constraint - hopefully, he will be ok
with it).
Thanks again.
"Damien" wrote:

> Ah,
> well I cheated a little bit here. I used SQL to write the batch file for
> me, and I use osql to fire off an ALTER TABLE to set the default for the
> column.
> So, from Query Analyser, write a query which selects your records, but
> create a bcp string. This script will create a meaninful looking batch fi
le
> but obviously you can't bcp into temp tables:
> DROP TABLE #import_files
> CREATE TABLE #import_files ( file_id INT UNIQUE IDENTITY NOT NULL, file_na
me
> VARCHAR(30) NOT NULL )
> DROP TABLE #raw_data
> CREATE TABLE #raw_data ( record_id INT UNIQUE IDENTITY NOT NULL, file_id I
NT
> NOT NULL, record CHAR(36) )
> ALTER TABLE #raw_data ADD CONSTRAINT def_raw_data__file_id DEFAULT -1 FOR
> file_id
> GO
>
> SET NOCOUNT ON
> INSERT INTO #import_files ( file_name ) VALUES ( 'test1.txt' )
> INSERT INTO #import_files ( file_name ) VALUES ( 'test2.txt' )
> SET NOCOUNT OFF
> GO
> DROP TABLE #batch_file
> CREATE TABLE #batch_file ( file_id INT, sort_id INT, command VARCHAR( 500
) )
> GO
> --
> SET NOCOUNT ON
> -- Section header
> INSERT INTO #batch_file
> SELECT file_id, 10, 'REM bcp file ' + CAST( file_id AS VARCHAR ) + ' - ' +
> file_name
> FROM #import_files
>
> -- Drop the default
> INSERT INTO #batch_file
> SELECT file_id, 20, 'osql -Syourserver -dyourdatabase -Ulogin_id -Ppasswor
d
> -q"ALTER TABLE #raw_data DROP CONSTRAINT def_raw_data__file_id'
> FROM #import_files
> -- Set the default
> INSERT INTO #batch_file
> SELECT file_id, 30, 'osql -Syourserver -dyourdatabase -Ulogin_id -Ppasswor
d
> -q"ALTER TABLE #raw_data ADD CONSTRAINT def_raw_data__file_id DEFAULT ' +
> CAST( file_id AS CHAR ) + ' FOR file_id"'
> FROM #import_files
> -- bcp the file
> INSERT INTO #batch_file
> SELECT file_id, 40, 'bcp -iyou get the idea.txt ; )'
> FROM #import_files
> -- Make a gap
> INSERT INTO #batch_file
> SELECT file_id, 50, ''
> FROM #import_files
>
> SELECT command
> FROM #batch_file
> ORDER BY file_id, sort_id
> SET NOCOUNT OFF
> Now, save the results as a batch file, remove the dashes from the top and
> away you go. I actually use a similar structure in the migration, only it
's
> a bit more complex, plus it's wrapped in a stored procedure and paramteriz
ed
> so it's nice and flexible.
> If it seems like a lot of hard work, then perhaps this isn't the solution
> for you, but it's worked for me!
> Let me know how you get on.
> Damien
>
>|||Steve,
Thanks for the update. At this time, the Co is not planning to move to
SQL Server 2005 (this project is expected to go live within a month), so I
guess I am stuck with 2000.
I haven't used text providers yet, so I don't fully understand the code. I
will go thru' BOL and assuming the performance drop isn't too much, I will
try to use this. Currently, I am able to push about 400 million rows into th
e
table (without the filename of course) in about an hour
Thanks again!
"Steve Kass" wrote:

> Bob,
> If you are moving to SQL Server 2005, you might consider using
> the BULK rowset provider. Existing (in SQL Server 2000) text
> providers might also work, but may not be as fast.
> DECLARE @.f nvarchar(200)
> SET @.f = 'c:\test\values.txt'
> INSERT INTO MyTestTable
> SELECT colFromTextFile + @.f
> SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
> FORMATFILE = 'c:\test\values.fmt') AS a;
> Steve Kass
> Drew University
> Bob wrote:
>
>

BULK INSERT into a table variable

Bulk Insert to an existing table works fine, but substituting a table variable causes parsing error:

Msg 137, Must declare the scalar variable "@.tblInput". or

Msg 207, Invalid column name '@.tblInput'.

To repro:

CREATE PROC X AS

BEGIN

DECLARE @.strSQL char(99)

DECLARE @.BulkFile varchar(60)

SET @.BulkFile = 'c:\LookingGlass\BulkCopy1.tmp'

DECLARE @.tblInput TABLE (

[Word] [varchar] (50),

[UseCount] [int] )

--works ok with temp table:

SET @.strSQL = 'BULK INSERT temp_tbl From ''' + @.BulkFile + ''''

--but not with table variable

SET @.strSQL = 'BULK INSERT ''' + @.tblInput + ''' From ''' + @.BulkFile + ''''

EXEC(@.strSQL)

END

These don't work either:

SET @.strSQL = 'BULK INSERT @.tblInput From ''' + @.BulkFile + ''''

SET @.strSQL = 'BULK INSERT ' + @.tblInput + ' From ''' + @.BulkFile + ''''

version: Microsoft SQL Server Management Studio Express 9.00.2047.00

You can't do that what you could do is use OPENROWSET with a txt or csv file

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Ignore!!!

|||

The op has tried that already, look at his last line of code

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks, Denis, for the very quick reply. I had searched extensively, and the documentation neglects to mention that Table Variables can't be used with Bulk Insert.

Ironically, the name of the Table Variable is fixed, but because it's a "variable", it can't be used to import.

Importing with a variable filename is a requirement.

But according to BOL, OPENROWSET, down in the Remarks,

"OPENROWSET does not accept variables for its arguments. "

Doesn't that mean hardcoded input filenames? ( OPENROWSET can use a Linked Server, but that's overkill for a text file.)

I wanted the performance benefits that a Table Variable provides, but it appears I'll have to make do with a temp table.

|||

Denis, stupidity on my part ... Read the post and thought he was trying to pass a table name in the variable. He is trying to pass a table variable... You're post is correct, you can't use table variables as part of a dynamic SQL string (would be nice if you could cuz it would same me from using #temp tables...

|||

you will have to use dynamic SQL

example

declare @.v varchar(500)
declare @.s varchar(500)
select @.v ='select top 6 * from ' + 'TestTextFileImport.txt'

select @.s ='
select * from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;'', ''' +@.v +' '')'

print @.s
exec (@.s)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Denis, that works great, just open the text file as a rowset! No need for a table at all.

Unfortunately, this raises a security issue. Running the sproc produces this error:

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'

because this component is turned off as part of the security configuration for this server.

A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.

For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Here are excerpts from searching BOL (underlines are mine):

If a nonzero value is set, SQL Server does not allow for ad hoc access

through the OPENROWSET and OPENDATASOURCE functions against the OLE DB provider.

When this option is not set, SQL Server also does not allow for ad hoc access.

This option controls the ability of non-administrators to run ad hoc queries. Administrators are not affected by this option.

By default, SQL Server does not allow ad hoc distributed queries

using OPENROWSET and OPENDATASOURCE against providers other than the SQL Native Client OLE DB Provider.

When this option is set to 0, SQL Server allows ad hoc access against other providers.

When this option is not set or is set to 1, SQL Server does not allow ad hoc access.

Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions

to connect to remote data sources that use OLE DB.

OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently.

For any data sources that will be accessed more than several times, define a linked server.

'Ad Hoc Distributed Queries is an Advanced option, which should be changed only by an experienced database administrator or a certified SQL Server technician,

and which require setting show advanced options to 1.

And here is how to do it:

sp_configure 'show advanced options',1;

GO

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries',1;

GO

RECONFIGURE;

GO

or with version 2.0 of the Microsoft .NET Framework, set the OleDbProviderSettings.DisallowAdHocAccess Property

All that just to read local text files? The server can't differentiate between local and remote files?

Why is a linked server recommended for files that will be read frequently? (thousands per hour, hopefully)

And I thought this was a pretty basic task. Thanks for answering my question.