SQL - Using INSERT INTO with a subquery that contains NULL values

Hi
I am trying to write an SQL query (in classic ASP) to create a new record in a table by copying the values from another record in the same table. The problem is that in some cases, some fields in the record that I am copying may NULL.

For example:

INSERT INTO TableA (name,company,phone,fax,email) SELECT name,company,phone,fax,email FROM TableA WHERE uniqueID=1

This is fine providing that the name, company, phone, fax and email fields in record 1 are not NULL, but it fails if any of them are NULL.

Is there a way to allow NULL values to be inserted, or do I have to write a procedure to check each value before inserting it, and swapping out the NULL value for a blank space or a zero?

FYI, this is SQL Server 2000 Standard Edition, and classic ASP running on IIS 7.

Thanks, Paul.
w1resAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

funwithdotnetCommented:
Do the columns allow NULLS? If not, you can do something like...
INSERT INTO TableA (name,company,phone,fax,email) 
SELECT 
ISNULL(name, ''), 
ISNULL(company, ''), 
ISNULL(phone, ''), 
ISNULL(fax, ''), 
ISNULL(email, '') 
FROM TableA WHERE uniqueID=1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
edtechdbaCommented:
Adding to funwithdotnet's comment (ID: 39288266), if you really need the NULLS in the table where the data is going to reside (copied to), you can always do an update on the table after blank values are inserted to switch them back to a NULL value if the columns allow NULL values.

example:
UPDATE TableA
SET name = NULL
WHERE name = ''

It's a work-around, but sounds like it would get the job done.
0
PortletPaulfreelancerCommented:
why not just look at the table definition to see if nulls are permitted in any of those fields?
try this perhaps:
SELECT
       o.name AS table_name
     , c.name AS column_name
FROM syscolumns AS c
INNER JOIN sysobjects AS o ON c.id = o.id

WHERE o.name = 'TableA' -- your table here

AND c.isnullable = 0 -- no nulls allowed
--and c.isnullable = 1 -- nulls are allowed
;

-- sql 2000 ??
EXEC sp_columns TableA -- your table here
;

Open in new window

{+ an edit, sorry, correction needed}
0
w1resAuthor Commented:
That did the job, thank you. I didn't realise you could use isnull to replace the null with an alternative value. The fields were all null enabled but I was still getting the same error.

Thank you for such a quick solution!
0
PortletPaulfreelancerCommented:
please keep in mind that you will now have a table containing "empty strings" and when making queries you may need to things like this:

where name = '' -- instead of -- where name is null

where name <> '' -- instead of -- where name is not null

empty strings can be a bit of a pain, but most importantly be aware of the need to deal with them

oh also:
   ISNULL(name,'something')
will not work on an existing row where name = ''
(because it isn't null)

just reminders/tips nothing else. Cheers. Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.