BULK INSERT & NULLS

[TamedTech]

Hello Guys,

Only a quick one here, I'm looking to insert a CSV file using BULK INSERT and there is quite a reasonable chance that from time to time odd columns in the text file will not have a value, somthing like this.

10, , 67, Paul, Smith
10, 11, 67, Fred, Smith
10, 20, 67, , Smith
10, 18, 67, john, doe

If I parse a file like this, will SQL just set those empty CSV values to a NULL in the database, or will it cause the import to fail?

I only ask as I know when using ColdFusion (my native language) it has real beef with empty list values.

Thanks,

Rob

 

[GrahamHills]

I saved your example data into a text file and created a table using the following command

CREATE TABLE [dbo].[tblImport](
	[ID1] [varchar](50) NOT NULL,
	[ID2] [varchar](50) NOT NULL,
	[ID3] [varchar](50) NOT NULL,
	[Firstname] [varchar](50) NOT NULL,
	[Lastname] [varchar](50) NOT NULL
) ON [PRIMARY]

GO

I then executed the following statement from query analyser

BULK INSERT tblImport
   FROM 'C:\Documents and Settings\graham.hills\Desktop\temp\gh.txt'
   WITH 
      (
         FIELDTERMINATOR =',',
         ROWTERMINATOR ='\n'
      )

This imported four rows as follows:-

ID1 ID2 ID3 Firstname Lastname
10 67 Paul Smith
10 11 67 Fred Smith
10 20 67 Smith
10 18 67 john doe


The blank values come in as '' (ie NOT NULL, but blank)

 

[TamedTech]

Ok thanks for that Graham,

Is there any database design principle that says NULL is better than '', or does it not really make any differance?

Thanks,

Rob

 

[Crowley16]

there is a design principal which says you shouldn't allow ANY nulls in your database, however I am sometimes flexible with that principal...

--------------------
Procrastinate Now!

 

[gmmastros]

>> Is there any database design principle that says NULL is better than '', or does it not really make any differance?

In my opinion, the answer depends on you and your requirements. I know developers that like to put default values on every column so that there won't be any NULLs. I think this is wrong.

Think of NULL as 'I don't know'. This unknown value is NOT the same as empty string or zero or any other arbitrary value.

Let's give some examples. Suppose you are tracking a list of people. You have, FirstName, MiddleName, LastName & Suffix columns in your table. If the middle name column is empty string, does that mean this person has no middle name, or does it mean there is a middle name, but you don't know what it is? Arguably... sometimes it doesn't matter, but sometimes it does.

Let's look at another example. Suppose you are tracking exam grades. If you see a value of 0 (for the grade), does this mean the student took the test but got ALL of the answers wrong, or does this mean the student hasn't taken the test yet? When performing aggregate calculations (like Average), a 0 in the field will cause it to be used in the calculation, whereas a NULL will be ignored in the calculation. Take a look at this example:

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](StudentId [COLOR=blue]Int[/color], Grade [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'90'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'0'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2, [COLOR=red]'90'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2, NULL)

[COLOR=blue]Select[/color] StudentId, [COLOR=#FF00FF]Avg[/color](Grade)
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] StudentID

Student 1 has grades of 90 and 0. Student 2 has grades of 90 and NULL. What do you think the averages will be for the students?

Does this make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom