Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

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.
0
w1res
Asked:
w1res
1 Solution
 
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
 
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now