Click here to Skip to main content
15,075,944 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.


QUERY :

SQL
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' )


ERROR CODE :

CSS
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!
Posted

this means secCode column has set property allow null = false
modify table and tick mark allow Null option then try...

Happy Coding!
:)
   
Comments
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.
1.
direct modify table column's datatype = varchar(10)

2.
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!
:)
Try the following (added parenthesis around the countrycode clause):
SQL
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 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