SQL Server Bulk Insert

[dougancil]

I have the following SQL Bulk Insert query:

BULK INSERT [dialerresults] FROM [C:\IVR Test\csvtest.txt] WITH (FIELDTERMINATOR = ',' ROWTERMINATOR = '/n')

and here is the sample data:

1,512 206 1293,rob stevens,doctor alexander,1 5 10,12:30, null
2,512 555 1212,jason reed,doctor smith,5 5 10,1:00, null
3,512 444 1111,sean jones,doctor paul,6 1 10,4:00, null
4,512 111 1212,zach cochran,doctor stevens,5 6 10,3:15, null

The table has the following fields

ID (int)

phonenumber (varchar 20)

patientname (varchar 20)

drname (varchar 30)

apptdate (datetime 8)

appttime (datetime 4)

currentdate (timestamp 8)


When I run this query with the Query Analyzer for SQL server, I am not getting any errors but I'm also not getting any data inserted into the table. I'm on SQL server 2000. When I run the query I have added a Select * from dialerresults to see if any data is inserted and the analyzer tells me that there are 0 rows affected. Does anyone have any idea as to why it's not inserting the data?

Thank you Doug
result (char 10)

 

[bborissov]

Your 5th values are not correct, that is not a datetime.
That works fo me:

values in TXT file (saved in D:\TEST\TEST.TXT):
[tt]
1,512 206 1293,rob stevens,doctor alexander,20010510,12:30, null
2,512 555 1212,jason reed,doctor smith,20050510,1:00, null
3,512 444 1111,sean jones,doctor paul,20060110,4:00, null
4,512 111 1212,zach cochran,doctor stevens,20050610,3:15, null
[/tt]

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #Test (Id [COLOR=blue]int[/color],
                    phonenumber [COLOR=blue]varchar[/color] (20),
                    patientname [COLOR=blue]varchar[/color] (20),
                    drname [COLOR=blue]varchar[/color] (30),
                    apptdate [COLOR=#FF00FF]datetime[/color],
                    appttime [COLOR=#FF00FF]datetime[/color],
                    currentdate [COLOR=blue]timestamp[/color])

[COLOR=blue]BULK[/color] [COLOR=blue]INSERT[/color] #Test [COLOR=blue]FROM[/color] [COLOR=red]'d:.txt'[/color]
   [COLOR=blue]WITH[/color]
     (
        FIELDTERMINATOR =[COLOR=red]','[/color],
        ROWTERMINATOR = [COLOR=red]''[/color])
[COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] #Test

[COLOR=blue]DROP[/color] [COLOR=blue]TABLE[/color] #Test

With your data I get this error:
[tt]
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 5 (apptdate).
[/tt]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

OOPS, that:

BULK INSERT #Test FROM 'd:.txt'

should be read as:

BULK INSERT #Test FROM 'd:\test\test.txt'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[dougancil]

Boris,

So if I want the ID field to be automatically incremented (since it's the primary key) I can just have a leading comma in the data file and I'll have to allow the last field to accept nulls, correct?

 

[bborissov]

SQL Server just ignores them (both IDENTITY and timestamp fields).
I just tested it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[dougancil]

Lastly,

Since this is data that someone is providing to us, and I don't want them to have to manipulate the data in any way, is it possible to have the last field as just blank (rather than null) and still have the SQL server read that field? So in other words the sample data would look like this:

,512 206 1293,rob stevens,doctor alexander,20010510,12:30,

Would that also work?

 

[bborissov]

Yes, you can (if you keep last comma).

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[dougancil]

Boris,

I just tested this and here is the result I got:

512 206 1293 rob stevens doctor alexander 2001-05-10 00:00:00.000 1900-01-01 12:30:00.000 0x000000000000100C NULL
512 555 1212 jason reed doctor smith 2005-05-10 00:00:00.000 1900-01-01 01:00:00.000 0x000000000000100D NULL
512 444 1111 sean jones doctor paul 2006-01-10 00:00:00.000 1900-01-01 04:00:00.000 0x000000000000100E NULL

So a couple of things I need to address.
1. The time for the appttime field is throwing 1900 as the year, if I convert that field to smalldatetime will just the time be ok?
2. For the currentdatetime, that timestamp is written in hex. I need it as a "real value" and not as hex. How could I get that to happen?
3. Lastly, you can see that it's only putting in 3 lines of data rather than 4. Any idea as to why that's happening?

Thank you

Doug

 

[bborissov]

1. No, DateTime is a Datetime, you always will have a Date part in that field. And since you didn't provide a Datepart SQL Server adds your time to 0 (which is 1900/01/01). That is why you always get 1900-.....

2. You declared your field as TimeStamp. That type has nothing to do with the date and time.
If you want current datetime just use GETDATE() function.

3. No, I didn't. It always inserted all 4 records here :-( Sorry.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[dougancil]

Boris,

Ok I understand, so rather than having 2 fields for appttime and apptdate, I think I'll just have one field for apptdatetime with both date and time on it, so I don't have to worry about having the 1900 issue. On the getdate function, should that be part of my sql query?

 

[bborissov]

1. Yes, that is better than have two in separate fields.

2. If you want to use any functions check OPENROWSET() set in BOL.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[dougancil]

Boris,

Thank you for all of your help. I am looking at BOL and noticing a lot of information about GETDATE and I want to have it only fill in the currentdate field,I tried this:

bulk insert dialerresults
from 'C:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
CurrentDate NOT NULL GETDATE()
)

Select *
from dialerresults
go


and it returns an error to me. Where would you suggest that I put the GETDATE function?

 

[bborissov]

That works for me:

create table #Test (Id int,
                    phonenumber varchar (20),
                    patientname varchar (20),
                    drname varchar (30),
                    apptdate datetime,
                    currentdate datetime NOT NULL DEFAULT(GETDATE()) )

BULK INSERT #Test FROM 'c:\test\test.txt'
   WITH
     (
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '\n')
SELECT * FROM #Test

DROP TABLE #Test

and the file is:
[tt]
1,512 206 1293,rob stevens,doctor alexander,20010510 12:30,
2,512 555 1212,jason reed,doctor smith,20050510 01:00,
3,512 444 1111,sean jones,doctor paul,20060110 04:00,
4,512 111 1212,zach cochran,doctor stevens,20050610 03:15,
[/tt]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[dougancil]

Boris,

What about if I have already created the table and just want to have the currentdate as part of the bulk insert?

Thank you

Doug

 

[bborissov]

Then use a temp table as me, and after after this import from it in the real table.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[dougancil]

boris,

What would be the reasoning behind that? I'm trying to understand because that just seems like additional points of potential failure. If I am having "static" data uploaded and inserted into a table, I understand that the table may require more maintenance being done that way, but that's the only thing that table would be used for. Is it possible to not have to import that from a temp table but actually have it go to it's "final destination" table?

 

[bborissov]

The reason is that your column in "final destination" table did not have default value and accepts NULLs. If you change these than you can use it.
But if you use that table ONLY for importing data and then you sent that data to other tables and truncate that table after that, you don't need it at all. A simple temp table will do this in one Stored procedure.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[dougancil]

Boris,

I created the table the this way:

create table dialerresults (Id int PRIMARY KEY,
phonenumber varchar (20),
patientname varchar (20),
drname varchar (30),
apptdate datetime,
currentdate datetime NOT NULL DEFAULT(GETDATE()),
results varchar (30) )


bulk insert dialerresults
from '\\MSBWEB3\data\test.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Select *
from dialerresults
go


and I'm now getting this error:

Server: Msg 4869, Level 16, State 1, Line 1
Bulk Insert failed. Unexpected NULL value in data file row 2, column 1. Destination column (Id) is defined NOT NULL

I need the ID column to be the primary key for this table. What am I missing?

 

[bborissov]

create table dialerresults (Id int [COLOR=red][b][u]IDENTITY(1,1)[/u][/b][/color]PRIMARY KEY,
                    phonenumber varchar (20),
                    patientname varchar (20),
                    drname varchar (30),
                    apptdate datetime,
                    currentdate datetime NOT NULL DEFAULT(GETDATE()),
                    results varchar (30) )

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[dougancil]

Boris,

Thank you. That worked.