Cannot insert the value NULL into column 'City', t

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cannot insert the value NULL into column 'City', t

Author

Topic

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2014-02-02 : 01:17:48

Cannot insert the value NULL into column 'City', table 'DB_61318_itweb.dbo.Location'; column does not allow nulls. INSERT fails.

<code>
create table country(country_code nvarchar(2), country_name nvarchar(255))
create table states(country_code nvarchar(2),state_code nvarchar(20),state_name nvarchar(255))
create table weblocations (country_code nvarchar(2), state_code nvarchar(20),city_name nvarchar(255),timezoneid varchar(255))

INSERT INTO Location
([Country],[City], [State] )

SELECT
country.country_name AS [Country],
weblocations.city_name AS [City],
states.state_name AS [State]
FROM
country
LEFT JOIN
weblocations ON country.country_code = weblocations.country_code
LEFT JOIN
states ON weblocations.state_code = states.state_code;

CREATE TABLE [dbo].[Location](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Country] [nvarchar](max) NOT NULL,
[City] [nvarchar](max) NOT NULL,
[State] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
</code>

Any help would be awesome.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-02 : 03:27:36

the error message is very clear

quote:
Cannot insert the value NULL into column 'City', table 'DB_61318_itweb.dbo.Location'; column does not allow nulls. INSERT fails.

quote:

CREATE TABLE [dbo].[Location](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Country] [nvarchar](max) NOT NULL,
[City] [nvarchar](max) NOT NULL,
[State] [nvarchar](max) NOT NULL,

maybe you can change the INSERT query to

INSERT INTO Location
([Country],[City], [State] )

SELECT
country.country_name AS [Country],
weblocations.city_name AS [City],
states.state_name AS [State]
FROM
country
LEFT JOIN
weblocations ON country.country_code = weblocations.country_code
LEFT JOIN
states ON weblocations.state_code = states.state_code
WHERE weblocations.city_name is not null;

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-02 : 03:45:34

Or default missing city names to empty space?

COALESCE(weblocations.city_name, '') AS [City]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-02 : 11:49:41

quote:
Originally posted by khtan

the error message is very clear
quote:
Cannot insert the value NULL into column 'City', table 'DB_61318_itweb.dbo.Location'; column does not allow nulls. INSERT fails.

quote:
CREATE TABLE [dbo].[Location](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Country] [nvarchar](max) NOT NULL,
[City] [nvarchar](max) NOT NULL,
[State] [nvarchar](max) NOT NULL,

maybe you can change the INSERT query to
INSERT INTO Location
([Country],[City], [State] )

SELECT
country.country_name AS [Country],
weblocations.city_name AS [City],
states.state_name AS [State]
FROM
country
LEFT JOIN
weblocations ON country.country_code = weblocations.country_code
LEFT JOIN
states ON weblocations.state_code = states.state_code
WHERE weblocations.city_name is not null;

KH
[spoiler]Time is always against us[/spoiler]

This is as good as making join with weblocations an INNER JOIN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2014-02-02 : 15:12:41

Hi looks good. I appreciate it guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources