INSERT NULL values not allowed

Question

INSERT NULL values not allowed

OJDev

SSC Veteran

Points: 248
More actions
April 14, 2009 at 7:52 am

#155556

I am trying to insert values from into one table from another table
INSERT INTO TABLE1 (
Version
, Type
, Name
, Description
, Category
)
SELECT
Version = 1
, Type = 2
, Name = t.name
, Description = t.description
, Category = t.category
)
FROM TABLE2 t
My category column in table1 doesn't allow NULL values but my SELECT statement on table2 returns a NULL value for category so my insert statement fails. How can I adress this issue?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286602 More actions · Answer 1

INSERT INTO TABLE1 (
Version
, Type
, Name
, Description
, Category
)
SELECT
Version = 1
, Type = 2
, Name = t.name
, Description = t.description
, Category = t.category
)
FROM TABLE2 t

One of two way. Either allow nulls in Table1 or assign a "default" in your script.

SELECT

Version = 1

, Type = 2

, Name = t.name

, Description = t.description

, Category = isnull(t.category, SomeDefaultHere)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Vijaya Kadiyala SSCrazy Eights Points: 9149 More actions · Answer 2

You can even try with CASE statement.

ex:

CASE WHEN arg1 IS NULL THEN 'x' ELSE arg1 END

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 3

This also should work if you can't modify the table.

INSERT INTO TABLE1 (

Version

, Type

, Name

, Description

, Category

)

SELECT

Version = 1

, Type = 2

, Name = t.name

, Description = t.description

, Category = IsNull(t.category,'')

)

FROM TABLE2 t

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 4

Sorry Slange already posted this.

Gabriel P (4/14/2009)
This also should work if you can't modify the table.
INSERT INTO TABLE1 (
Version
, Type
, Name
, Description
, Category
)
SELECT
Version = 1
, Type = 2
, Name = t.name
, Description = t.description
, Category = IsNull(t.category,'')
)
FROM TABLE2 t

INSERT NULL values not allowed

Cookies on SQLServerCentral