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 Query

Hey i am planning to use the BULK INSERT to copy the data into my Server. Th
e
data is being written to the file by external application.
But i want the data that is being written should be deleted after the
insert. Can BULK INSERT statement support such option to be specified. other
wise my file will grow infintly over time.
Any suggestion or pointer will be helpful.
MCAD
Vineet BattaBULK INSERT itself will not manage the files for you. You'll have to do
that yourself via another script. You might want to look into using a DTS
package to manage the BULK INSERT and if successful, execute a script task
to move/delete the file.
--Brian
(Please reply to the newsgroups only.)
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
> Hey i am planning to use the BULK INSERT to copy the data into my Server.
> The
> data is being written to the file by external application.
>
> But i want the data that is being written should be deleted after the
> insert. Can BULK INSERT statement support such option to be specified.
> other
> wise my file will grow infintly over time.
> Any suggestion or pointer will be helpful.
> --
> MCAD
> Vineet Batta|||But does BULK Insert can keep track of what data has been pushed to server
from last time , so that when it runs the second time it just picks up delta
only.
--
MCAD
Vineet Batta
"Brian Lawton" wrote:

> BULK INSERT itself will not manage the files for you. You'll have to do
> that yourself via another script. You might want to look into using a DTS
> package to manage the BULK INSERT and if successful, execute a script task
> to move/delete the file.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
> news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
>
>|||No, but you can insert into a staging table and then run queries to
unset/update data into the main table as desired.
Hope this helps.
Dan Guzman
SQL Server MVP
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:08DC46E9-ED63-413D-83E5-B7FA3EC47762@.microsoft.com...
> But does BULK Insert can keep track of what data has been pushed to server
> from last time , so that when it runs the second time it just picks up
> delta
> only.
> --
> MCAD
> Vineet Batta
>
> "Brian Lawton" wrote:
>

Building Move

My current company is planning to undertake an enormous move late this week,
We have a very large SAN environment .
It has been decided that we change the Ip address of the new network at the
new building in order to make the transition as smooth as possible.
With the ip address changes on the new network what type of issues should we
look out for during this move. If any one has participated in a move that
included sql servers any help would greatly be appreciated.Hi
If you are not directly using the IP address then in general I think you
should be ok.
Write down the tests that you are going to do to verify the change before
hand and check them off as you go. You can then make sure that everything is
covered in your testing. Knowing the current systems data flows will help
make sure these are documented.
Make sure that the DNS's are correctly updated and replication of the new
records may require time to propogate.
Make sure that firewalls are correctly configured for any new ranges you are
using and rules relating to the old ranges changed or removed.
John
"JDS" wrote:
> My current company is planning to undertake an enormous move late this week,
> We have a very large SAN environment .
> It has been decided that we change the Ip address of the new network at the
> new building in order to make the transition as smooth as possible.
> With the ip address changes on the new network what type of issues should we
> look out for during this move. If any one has participated in a move that
> included sql servers any help would greatly be appreciated.

Building Development Box for 2005

I'm planning on building a new desktop box for developing apps with VS 2005
and SQL Server 2005.
Would it make sense to have two SATA drives, say C: and D: and install SQL
Server on D?
What would you recommend would be a reasonably good configuration - RAM,
CPU, video, HDs, etc considering a budget of about $2000, NOT $5000.
Thanks.You should be able to buy something cheaper than $2000, i would think.
Not much video is necessary so pretty much anything on board would do
the job.
A gig of ram - or more.
Any old fairly fast HD would do...
Plus an external drive to back up to.
You might aklso want to think about raid so you minimise your risk of
losing anything.
Only having the one cevelopment PC is a risk if you're working at home
or in an office where you can't use someone else's PC.
I'll probably be buying a 2nd (cheap) pc for my home office.
You can then back up from one to the other and if you mess one up the
other is still ok.
Oh.
And a router firewall. These are cheap now.