I have an INSERT INTO statement that I need to check if one of the field values I'm inserting is an empty string or not. If it is, I need to insert an actual SPACE instead of an empty string.
INSERT INTO Vendors (LegacyID, ExternalID1, ExternalID2, EIN, LegalName, DBAName, ...)
Update Vendors SET LegacyID = ds.LegacyID, ExternalID1 = ds.ExtID1, ExternalID2 = ds.ExtID2, ...)
You didn't include the
VALUES portion of the
INSERT, nor the
EIN in your
UPDATE, so you've omitted the sections where you'd actually be making changes.
Because of this, I could only take a best-guess shot at what your actual fields may be, but the general jist of the answer is to use a
CASE statement around the value going to be inserted.
-- If field is '', then use ' '. -- Otherwise, use it as-is. CASE field WHEN '' THEN ' ' ELSE field END
Potential use-case (field names assumed):
INSERT INTO Vendors (EIN, ds.LegacyID, ...) VALUES (CASE ds.EIN WHEN '' THEN ' ' ELSE ds.EIN END, ds.LegacyID, ...)
UPDATE Vendors SET EIN = CASE ds.EIN WHEN '' THEN ' ' ELSE ds.EIN END