SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Cannot insert the value NULL into column 'City', t
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mcupryk
Yak Posting Veteran

Canada
90 Posts

Posted - 02/02/2014 :  01:17:48  Show Profile  Reply with Quote
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)

Singapore
17584 Posts

Posted - 02/02/2014 :  03:27:36  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30109 Posts

Posted - 02/02/2014 :  03:45:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/02/2014 :  11:49:41  Show Profile  Reply with Quote
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
Time is always against us




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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mcupryk
Yak Posting Veteran

Canada
90 Posts

Posted - 02/02/2014 :  15:12:41  Show Profile  Reply with Quote
Hi looks good. I appreciate it guys.

Edited by - mcupryk on 02/02/2014 15:13:18
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000