Bulk Insert (type mismatch) on datetime field containing NULL

Can anyone help please, I am using bulk insert for the first time.
The statement I am running is:
BULK INSERT Titles
FROM 'c:\Titles.txt'
WITH (FIRSTROW = 3,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
KEEPNULLS,
FORMATFILE = 'c:\Titles.fmt')
Titles.txt contains tab delimited data like:
ID Description StartDate ExpiryDate ParentItemID
-- --
-- -- --
440 Doctor 1 Jan 1997 0:00 NULL NULL
441 Mr 1 Jan 1990 0:00 NULL 1
If I run the bulk insert statement I get the message:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 4
(ExpiryDate)
In the file Titles.txt, if I find and replace NULL with nothing and
then execute the statement the data inserts into the Titles table.
I need to be able to insert without having to do find and replace as I
have hundreds of files to bulk insert.
The format file Titles.fmt looks like this:
8.0
5
1 SQLCHAR 0 12 "\t" 1
ID ""
2 SQLCHAR 0 100 "\t" 2
Description Latin1_General_CI_AS
3 SQLCHAR 0 24 "\t" 3
StartDate ""
4 SQLCHAR 0 24 "\t" 4
ExpiryDate ""
5 SQLCHAR 0 12 "\t" 5
ParentItemID ""If this is not a one time deal, you'd be better off making sure that when
these files are generated, the value for column ExpireDate that is null does
not contain a string NULL.
With the existing data files, personally, I'd write a little utility to
find/replace all the 'NULL' string in the ExpireDate column with an empty
string. This can be esily done with any tool that supports regular
expressions.
Linchi
"rai_sk@.hotmail.com" wrote:
> Can anyone help please, I am using bulk insert for the first time.
> The statement I am running is:
> BULK INSERT Titles
> FROM 'c:\Titles.txt'
> WITH (FIRSTROW = 3,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> FORMATFILE = 'c:\Titles.fmt')
> Titles.txt contains tab delimited data like:
> ID Description StartDate ExpiryDate ParentItemID
> -- --
> -- -- --
> 440 Doctor 1 Jan 1997 0:00 NULL NULL
> 441 Mr 1 Jan 1990 0:00 NULL 1
> If I run the bulk insert statement I get the message:
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 4
> (ExpiryDate)
> In the file Titles.txt, if I find and replace NULL with nothing and
> then execute the statement the data inserts into the Titles table.
> I need to be able to insert without having to do find and replace as I
> have hundreds of files to bulk insert.
> The format file Titles.fmt looks like this:
> 8.0
> 5
> 1 SQLCHAR 0 12 "\t" 1
> ID ""
> 2 SQLCHAR 0 100 "\t" 2
> Description Latin1_General_CI_AS
> 3 SQLCHAR 0 24 "\t" 3
> StartDate ""
> 4 SQLCHAR 0 24 "\t" 4
> ExpiryDate ""
> 5 SQLCHAR 0 12 "\t" 5
> ParentItemID ""
>

BULK DELETE ON SQL SERVER

We have a requirement to delete 20 million rows from a table A
containing 21 million rows
and insert 800,000 rows into table A from another table B.
We are getting the message:
Server: Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'ledgerdb' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column
in sys.databases
Server
Is there any way to do this deletion of 20 million rows out of 21
million rows in SQL server 2005 without writing log data?
Regards
Rajagopal NSHi
SELECT INTO ... FROM originaltable WHERE ...1mln rows to remain
DROP original table that nopw contains 20 mln rows. Rename a new created
table to original.
<rajagopal.ns@.gmail.com> wrote in message
news:1188982556.629589.151750@.19g2000hsx.googlegroups.com...
> We have a requirement to delete 20 million rows from a table A
> containing 21 million rows
> and insert 800,000 rows into table A from another table B.
> We are getting the message:
> Server: Msg 9002, Level 17, State 2, Line 1
> The transaction log for database 'ledgerdb' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column
> in sys.databases
> Server
> Is there any way to do this deletion of 20 million rows out of 21
> million rows in SQL server 2005 without writing log data?
> Regards
> Rajagopal NS
>|||You also may want to change the Recovery Model to "Bulk Log" while
performing this operation.
Or, you may change it to Simple Recovery Model and after the operation to
FULL model again. Otherwise, your ldf file may blow up :)
--
Ekrem Önsoy
<rajagopal.ns@.gmail.com> wrote in message
news:1188982556.629589.151750@.19g2000hsx.googlegroups.com...
> We have a requirement to delete 20 million rows from a table A
> containing 21 million rows
> and insert 800,000 rows into table A from another table B.
> We are getting the message:
> Server: Msg 9002, Level 17, State 2, Line 1
> The transaction log for database 'ledgerdb' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column
> in sys.databases
> Server
> Is there any way to do this deletion of 20 million rows out of 21
> million rows in SQL server 2005 without writing log data?
> Regards
> Rajagopal NS
>

Building data warehouse

I have 35 small (<2 GB) OLTP databases containing about 80 tables. We have
a need to mine this data and do not want to do this on the OLTP servers and
would prefer creating a seperate large merged database on a different
system. There are no columns in the table that indicate which database they
came from.
I think it would be simple to clone any one of the OLTP databases on our
warehouse server, add a database name column to all the table and then write
a few simple append table queues which would allow to to consolidate the 35
database into the warehouse server.
This doesn't need to be done on a transactional basis and a nighly of weekly
merge would be sufficient.
Can anyone tell me if any replication methods would be better suited to do
this and if so specifically which kind/type/etc?
In the data on the warehouse I need to know which database the data came
from and I don't want to add a column to all the tables on all the databases
just to do this which is what got me into thinking the brute force approach
was a better idea.
Any comments of suggestions would be greatly appreciated.
Transactional would be best depending on the volume of transactions. If you
have a lot of transactions you will have to set a shorter distribution
frequency.
Merge would entail more processing than transactional, and snapshot would
send your entire set of tables each time which depending on the size of your
tables can be problematic.
"Myles Duffy" <MylesDuffy@.softwhere.com> wrote in message
news:%23V7%23QMOnEHA.392@.tk2msftngp13.phx.gbl...
>I have 35 small (<2 GB) OLTP databases containing about 80 tables. We have
> a need to mine this data and do not want to do this on the OLTP servers
> and
> would prefer creating a seperate large merged database on a different
> system. There are no columns in the table that indicate which database
> they
> came from.
> I think it would be simple to clone any one of the OLTP databases on our
> warehouse server, add a database name column to all the table and then
> write
> a few simple append table queues which would allow to to consolidate the
> 35
> database into the warehouse server.
> This doesn't need to be done on a transactional basis and a nighly of
> weekly
> merge would be sufficient.
> Can anyone tell me if any replication methods would be better suited to do
> this and if so specifically which kind/type/etc?
> In the data on the warehouse I need to know which database the data came
> from and I don't want to add a column to all the tables on all the
> databases
> just to do this which is what got me into thinking the brute force
> approach
> was a better idea.
> Any comments of suggestions would be greatly appreciated.
>