SELECT INTO NULLable column to NOT NULL

I’ve added a beginner category to my blog, so here’s the first post. Hope it helps someone out along the way…

SELECT INTO moves a certain number of things to the newly created table from the source table; some things it doesn’t move, and these are well known. NULL value specifications for a given column get moved, but what if you don’t want the NULL? The following example illustrates:

USE _;

GO

IF OBJECT_ID('testTable') IS NOT NULL

       DROP TABLE testTable

GO

SELECT DISTINCT StateProvinceCode

INTO testTable

FROM AdventureWorksDW2008R2.dbo.DimGeography

GO

ALTER TABLE testTable

       ADD CONSTRAINT pk_testTable PRIMARY KEY (StateProvinceCode)

GO

Msg 8111, Level 16, State 1, Line 1

Cannot define PRIMARY KEY constraint on nullable column in table 'testTable'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

How about ISNULL or COALESCE?

SELECT DISTINCT ISNULL(StateProvinceCode,'î') AS StateProvinceCode

INTO testTable

FROM AdventureWorksDW2008R2.dbo.DimGeography

WHERE StateProvinceCode!='î'

GO

Add some weird character or combination of characters that fall outside of the set of values from your SELECT list so that you know you won’t miss a record for your new column.

Thanks,

Lee

-----------------------------