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:
>
>