Solved

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

Posted on 2013-06-30
5
521 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

820 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