Solved

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

Posted on 2013-06-30
5
510 Views
Last Modified: 2013-07-01
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.
0
Comment
Question by:w1res
5 Comments
 
LVL 12

Accepted Solution

by:
funwithdotnet earned 500 total points
ID: 39288266
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
 
LVL 9

Expert Comment

by:edtechdba
ID: 39288783
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39288818
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
 

Author Closing Comment

by:w1res
ID: 39289893
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289936
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now