inserting into a not null field

Question

Hello,

I'm doing a bulk data insert into a table that includes not nulls and for those fields the source table includes some data in that column that is null.  Is there away to have the script insert something like "n/a" or whatever if a null value occurs?

My insert looks something like:

[Id],1,[ParentCategoryID],1,[Name],[ShortDescription],[FullDescription],[AdminComment],1,1,0

and if say AdminComment was null I'd want to insert "n/a"

Thanks for any ideas,

Dave

Answer 1

Can you show your complete INSERT statement?

Are you using BCP or SSIS or straight T-SQL?

Answer 2

As sqlity.net has implied, I have assumed you are using T-SQL.
USE TSQLFORUMS
GO

--CREATE TABLE SOME_TABLE
--(
--	Id INT NOT NULL, 
--	ParentCategoryId INT NOT NULL, 
--	Name VARCHAR(25) NOT NULL, 
--	ShortDescription VARCHAR(50) NOT NULL, 
--	FullDescription VARCHAR(75) NOT NULL, 
--	AdminContent VARCHAR(100) NOT NULL
--)

INSERT INTO SOME_TABLE
	VALUES(1, 1, 'CHARLIE', 'LOOKS AWESOME', 'GREAT WORK', ISNULL(NULL, 'n/a'));

INSERT INTO SOME_TABLE
	VALUES(1, 1, 'CHARLIE', 'LOOKS AWESOME', 'GREAT WORK', NULL);

SELECT * FROM SOME_TABLE
	       

---

UML, then code

Answer 3

BULK INSERT 
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 
    ( 
   [ [ , ] BATCHSIZE = batch_size ] 
   [ [ , ] CHECK_CONSTRAINTS ] 
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ [ , ] DATAFILETYPE = 
      { 'char' | 'native'| 'widechar' | 'widenative' } ] 
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] 
   [ [ , ] FIRSTROW = first_row ] 
   [ [ , ] FIRE_TRIGGERS ] 
   [ [ , ] FORMATFILE = 'format_file_path' ] 
   [ [ , ] KEEPIDENTITY ] 
   [ [ , ] KEEPNULLS ] 
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] 
   [ [ , ] LASTROW = last_row ] 
   [ [ , ] MAXERRORS = max_errors ] 
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] 
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ] 
   [ [ , ] ROWTERMINATOR = 'row_terminator' ] 
   [ [ , ] TABLOCK ] 
   [ [ , ] ERRORFILE = 'file_name' ] 
    )] 

these are the attributes you can use while bulk insert.  You can use KEEPDEFAULTS attribute in with clause.

USE AdventureWorks;
GO
INSERT INTO MyTestDefaultCol2
    WITH (KEEPDEFAULTS)
    SELECT *
      FROM OPENROWSET(BULK  'C:\MyTestEmptyField2-c.Dat',
      FORMATFILE='C:\MyTestDefaultCol2-f-c.Fmt'     
      ) as t1 ;
GO

Answer 4

If you are using sql.. directly use ISNULL(AdminComment ,'N/A')

Answer 5

You can add a default value for that non-nullable column.

alter table Table_test
ADD CONSTRAINT colName_defaultvalue DEFAULT 'N/A' FOR colName

Answer 6

Hi Dave,

BULK INSERT statement observe any defaults that are defined for the columns in the table. For example, if there is a null field in a data file, the default value for the column is loaded instead. Please refer to Jingyang Li's solution to set the default value.

For more information, please refer to the article below:
Keep Nulls or UseDefault Values During Bulk Import (SQL Server): http://technet.microsoft.com/en-us/library/ms187887.aspx

If you have any feedback on our support, please click here.

Regards,

---

Elvis Long
TechNet Community Support

Answer 7

For existing data update use below statement,

update t2 set AdminComment = 'N/A' where AdminComment is null

And to avoid further null updates to table

alter table t2
ADD CONSTRAINT AdminComment_defaultvalue DEFAULT 'N/A' FOR AdminComment