Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-06-30
5
Medium Priority
?
541 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 12

Accepted Solution

by:
funwithdotnet earned 2000 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 49

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 49

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

660 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