SQL SUB QUERY FOR INSERT DATA FROM ONE TABLE TO ANOTHER

Question

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 15-Oct-12 20:32pm

Hesha

Add a Solution

2 solutions

Solution 2

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

Posted 15-Oct-12 20:38pm

Mehdi Gholam

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aarti Meswania · Accepted Answer · 2012-10-15T20:37:00

Solution 1

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

Happy Coding!
:)

Posted 15-Oct-12 20:37pm

Aarti Meswania

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!
:)