We help IT Professionals succeed at work.

Defaulting Temp Table Required Column to a Null Value

I have a temp table that has required columns.  I am outputing the result to a flat file that will be used by an EDI process.  I need to default the blank fields to a NULL value.  Everything I try either results in a "column does not allow null" message or an "error near [field] message.  Below is my ccode:


   SELECT  TITLE_UNIT AS BOOK_TYPE
         ,ITEM AS SKU
         ,'' AS Title_Date
         ,'' AS 'BOOK SKU'
         ,TITLE_DESC AS 'Description'
         ,'' AS 'Title Cost'
         ,'' AS 'Title Costa'
         ,'' AS 'Title Costb'
         ,'' AS 'Title Costc'
         ,'' AS 'Book Dept'
  INTO Temptbl
FROM BookINVENTORY
WHERE Genre = 'Fiction'

How can I default my Title Costa and other fields to NULL values using the above?
Comment
Watch Question

Why don't you try like this.

SELECT  TITLE_UNIT AS BOOK_TYPE
         ,ITEM AS SKU
         ,'' AS Title_Date
         ,'' AS 'BOOK SKU'
         ,TITLE_DESC AS 'Description'
         ,'' AS 'Title Cost'
         ,NULL AS 'Title Costa'
         ,NULL AS 'Title Costb'
         ,'' AS 'Title Costc'
         ,'' AS 'Book Dept'
  INTO Temptbl
FROM BookINVENTORY
WHERE Genre = 'Fiction

with the above sql both Title Costa and Title Costb will allow NULL

Commented:
If the columns in the temp table do not allow NULLS then the only way to be able to insert them into the temp table is to change the columns to allow them.  Another option is on your select from the temp table to the flat use NULLIF([Title Costa],'').  That way you can insert the values as empty strings.  

Author

Commented:
Worked like a charm!!  
Thanks so much!
You are always welcome.. :) Have a nice day.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.