Click here to Skip to main content
15,070,089 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
HI i just tried to insert data from one table to onother.but it gives me you cannot insert NULL values since it is a NOT NULL feild.THEN i add IS NOT NULL junction.but still gives the same error.


INSERT INTO [AntronERP_DB].[dbo].[tblCountry](countryCode,countryName,secCode,stdCountryCode)
(SELECT [CountryCode],[CountryName],[Sector],[CountryCode]  FROM [ANTRONMASTERDB].[dbo].[CountryMaster]
WHERE  Sector  IS NOT NULL AND CountryCode <> 'UK' OR CountryCode <> 'LK' OR CountryCode <> 'US' )


Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'secCode', table 'AntronERP_DB.dbo.tblCountry'; column does not allow nulls. INSERT fails.
The statement has been terminated.

ANybody help me on this issue.Thanks!

Try the following (added parenthesis around the countrycode clause):
INSERT INTO [AntronERP_DB].[dbo].[tblCountry](countryCode,countryName,secCode,stdCountryCode)
(SELECT [CountryCode],[CountryName],[Sector],[CountryCode]  FROM [ANTRONMASTERDB].[dbo].[CountryMaster]
WHERE  Sector  IS NOT NULL AND (CountryCode <> 'UK' OR CountryCode <> 'LK' OR CountryCode <> 'US') )
this means secCode column has set property allow null = false
modify table and tick mark allow Null option then try...

Happy Coding!
Hesha 16-Oct-12 4:19am
SecCode is a Foreign Key referring tbl Sector.So cannot allow Null Values to it. i just want to copy data from other table it can be Contains NULL values in the 'Sector' field and if NuLL values are found in the that table simply i want to Skip that record and add rest of records to it.
Aarti Meswania 16-Oct-12 4:23am
ok then in select query
filter records in where condition properly see solution 2
you missing brackets so condition is not giving desired result
where Sector IS NOT NULL AND
CountryCode <> 'UK' OR CountryCode <> 'LK' OR CountryCode <> 'US'
Hesha 16-Oct-12 5:20am
hey i figured out why it was not working.because in the table that i want to copy data has char 10 for Sector While new table having varchar (10).thats why error occured. Thanks for your guidance. is there any way to convert char field to varchar with out modifying previous table when run the sub query? Thanks a Lot.
Aarti Meswania 16-Oct-12 5:24am
two ways to do it
first is better.
direct modify table column's datatype = varchar(10)

convert datatype in Query
select convert(varchar(10),ColNm)
Hesha 16-Oct-12 6:12am
Thanks a Lot Aarti. it worked!!! :)
Aarti Meswania 16-Oct-12 6:13am
most welcome!
glad to help you!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900