Insert Null where value is empty using insert select query

Question

I am using insert select query in .net c#. Data is not inserting when some values are empty.

According to my code, only if Title/ category/ occupation/ gender/ MaritalStatusDesc/ Qualification value is empty then data is not inserting.

INSERT INTO odisha_customer_data 
            ([share _id], 
             firstname, 
             middlename, 
             lastname, 
             fatherhusbandname, 
             mothername, 
             id, 
             dob, 
             age, 
             custnaturedesc, 
             [date of membership], 
             religion, 
             nationality, 
             uid, 
             pan, 
             segment, 
             introduceraccno, 
             empcode, 
             idproof, 
             idproofno, 
             residentialproofdesc, 
             residentialproofno, 
             annualincomedetails, 
             [risk category of customer], 
             [closing bal], 
             title, 
             gender, 
             category, 
             occupation, 
             community, 
             qualification, 
             maritalstatusdesc) 
SELECT @ShareId, 
       @FirstName, 
       @MiddleName, 
       @LastName, 
       @FatherHusbandName, 
       @MotherName, 
       @id, 
       @DOB, 
       @Age, 
       @CustNatureDesc, 
       @DateofMembership, 
       @Religion, 
       @Nationality, 
       @UID, 
       @PAN, 
       @Segment, 
       @IntroducerAccNo, 
       @EmpCode, 
       @IDproof, 
       @IDProofNo, 
       @ResidentialProofDesc, 
       @ResidentialProofNo, 
       @AnnualIncomeDetails, 
       @RiskCategoryofcustomer, 
       @ClosingBal, 
       t.titleid         AS title, 
       g.genderid        AS gender, 
       cat.catagoryid    AS category, 
       o.occupationid    AS Occupation, 
       c.communityid     AS Community, 
       q.qualificationid AS Qualification, 
       m.maritalstatusid AS MaritalStatus 
FROM   title t, 
       gender g, 
       catagory cat, 
       occupation o, 
       community c, 
       qualification q, 
       maritalstatus m 
WHERE  t.title = @Title 
       AND g.gendername = @gender 
       AND cat.catagoryname = @category 
       AND o.occupationname = @occupation 
       AND c.communityname = @community 
       AND q.qualificationname = @Qualification 
       AND m.maritalstatusdesc = @MaritalStatusDesc

Please check my code. And tell me to set Null while given values are empty.

String query = "insert into Odisha_Customer_data([Share _Id], FirstName, MiddleName, LastName, FatherHusbandName, MotherName, "
                        + "id,  DOB, Age, CustNatureDesc, [Date of Membership], "
                    + "Religion, Nationality, UID, PAN, Segment, IntroducerAccNo, EmpCode, IDproof, IDProofNo, ResidentialProofDesc, ResidentialProofNo, "
                    + "AnnualIncomeDetails, [Risk Category of customer], [Closing Bal], Title, gender, category, occupation, community, Qualification, MaritalStatusDesc)"+
                    " SELeCT @ShareId, @FirstName, @MiddleName, @LastName, @FatherHusbandName, @MotherName, @id,"
                    + "@DOB, @Age, @CustNatureDesc, @DateofMembership, @Religion, @Nationality, @UID, @PAN,"
                    + " @Segment, @IntroducerAccNo, @EmpCode, @IDproof, @IDProofNo, @ResidentialProofDesc, @ResidentialProofNo, @AnnualIncomeDetails, @RiskCategoryofcustomer, @ClosingBal," +
                    "t.TitleID AS title, g.GenderId AS gender, cat.CatagoryID AS category, o.OccupationID AS Occupation, c.CommunityID AS Community, q.QualificationID AS Qualification," +
                    " m.MaritalStatusID AS MaritalStatus FROM Title AS t INNER JOIN Gender AS g ON t.Title = @title INNER JOIN Catagory AS cat ON cat.catagoryname = @Category " +
                    "INNER JOIN Occupation AS o ON o.OccupationName = @Occupation INNER JOIN Community AS c ON c.CommunityName = @Community " +
                    " INNER JOIN Qualification AS q ON q.QualificationName = @qualification INNER JOIN MaritalStatus AS m ON m.MaritalStatusDesc = @maritalStatusDesc " +
                    "WHERE @title Is Not Null AND g.gendername = @Gender AND @Category Is Not Null AND @Occupation Is Not Null " +
                    "AND @Community Is Not Null AND @qualification Is Not Null AND @maritalStatusDesc Is Not Null";

                //query = "UPDATE a SET a.MaritalStatusDesc = b.MaritalStatusID From Odisha_Customer_data AS a INNER JOIN MaritalStatus AS b ON b.MaritalStatusDesc = '" + MaritalStatusDesc + "' where a.rowid = (Select max(rowid) as NewCustomer from Odisha_Customer_data)";

                SqlCommand cmd = new SqlCommand(query, con);

                cmd.Parameters.Add(new SqlParameter("@title", string.IsNullOrEmpty(Title) ? (object)DBNull.Value : Title));
                cmd.Parameters.Add(new SqlParameter("@Gender", string.IsNullOrEmpty(gender) ? (object)DBNull.Value : gender));
                cmd.Parameters.Add(new SqlParameter("@Occupation", string.IsNullOrEmpty(occupation) ? (object)DBNull.Value : occupation));
                cmd.Parameters.Add(new SqlParameter("@Category", string.IsNullOrEmpty(category) ? (object)DBNull.Value : category));
                cmd.Parameters.Add(new SqlParameter("@Community", string.IsNullOrEmpty(community) ? (object)DBNull.Value : community));
                cmd.Parameters.Add(new SqlParameter("@qualification", string.IsNullOrEmpty(Qualification) ? (object)DBNull.Value : Qualification));
                cmd.Parameters.Add(new SqlParameter("@maritalStatusDesc", string.IsNullOrEmpty(MaritalStatusDesc) ? (object)DBNull.Value : MaritalStatusDesc));

                cmd.Parameters.Add(new SqlParameter("@ShareId", string.IsNullOrEmpty(ShareId) ? (object)DBNull.Value : ShareId));
                cmd.Parameters.Add(new SqlParameter("@FirstName", string.IsNullOrEmpty(FirstName) ? (object)DBNull.Value : FirstName));
                cmd.Parameters.Add(new SqlParameter("@MiddleName", string.IsNullOrEmpty(MiddleName) ? (object)DBNull.Value : MiddleName));
                cmd.Parameters.Add(new SqlParameter("@LastName", string.IsNullOrEmpty(LastName) ? (object)DBNull.Value : LastName));
                cmd.Parameters.Add(new SqlParameter("@FatherHusbandName", string.IsNullOrEmpty(FatherHusbandName) ? (object)DBNull.Value : FatherHusbandName));
                cmd.Parameters.Add(new SqlParameter("@MotherName", string.IsNullOrEmpty(MotherName) ? (object)DBNull.Value : MotherName));
                cmd.Parameters.Add(new SqlParameter("@id", string.IsNullOrEmpty(id) ? (object)DBNull.Value : id));
                cmd.Parameters.Add(new SqlParameter("@DOB", string.IsNullOrEmpty(DOB) ? (object)DBNull.Value : DOB));
                cmd.Parameters.Add(new SqlParameter("@Age", string.IsNullOrEmpty(Age) ? (object)DBNull.Value : Age));
                cmd.Parameters.Add(new SqlParameter("@CustNatureDesc", string.IsNullOrEmpty(CustNatureDesc) ? (object)DBNull.Value : CustNatureDesc));
                cmd.Parameters.Add(new SqlParameter("@DateofMembership", string.IsNullOrEmpty(DateofMembership) ? (object)DBNull.Value : DateofMembership));
                cmd.Parameters.Add(new SqlParameter("@Religion", string.IsNullOrEmpty(Religion) ? (object)DBNull.Value : Religion));
                cmd.Parameters.Add(new SqlParameter("@Nationality", string.IsNullOrEmpty(Nationality) ? (object)DBNull.Value : Nationality));
                cmd.Parameters.Add(new SqlParameter("@UID", string.IsNullOrEmpty(UID) ? (object)DBNull.Value : UID));
                cmd.Parameters.Add(new SqlParameter("@PAN", string.IsNullOrEmpty(PAN) ? (object)DBNull.Value : PAN));
                cmd.Parameters.Add(new SqlParameter("@Segment", string.IsNullOrEmpty(Segment) ? (object)DBNull.Value : Segment));
                cmd.Parameters.Add(new SqlParameter("@IntroducerAccNo", string.IsNullOrEmpty(IntroducerAccNo) ? (object)DBNull.Value : IntroducerAccNo));
                cmd.Parameters.Add(new SqlParameter("@EmpCode", string.IsNullOrEmpty(EmpCode) ? (object)DBNull.Value : EmpCode));
                cmd.Parameters.Add(new SqlParameter("@IDproof", string.IsNullOrEmpty(IDproof) ? (object)DBNull.Value : IDproof));
                cmd.Parameters.Add(new SqlParameter("@IDProofNo", string.IsNullOrEmpty(IDProofNo) ? (object)DBNull.Value : IDProofNo));
                cmd.Parameters.Add(new SqlParameter("@ResidentialProofDesc", string.IsNullOrEmpty(ResidentialProofDesc) ? (object)DBNull.Value : ResidentialProofDesc));
                cmd.Parameters.Add(new SqlParameter("@ResidentialProofNo", string.IsNullOrEmpty(ResidentialProofNo) ? (object)DBNull.Value : ResidentialProofNo));
                cmd.Parameters.Add(new SqlParameter("@AnnualIncomeDetails", string.IsNullOrEmpty(AnnualIncomeDetails) ? (object)DBNull.Value : AnnualIncomeDetails));
                cmd.Parameters.Add(new SqlParameter("@RiskCategoryofcustomer", string.IsNullOrEmpty(RiskCategoryofcustomer) ? (object)DBNull.Value : RiskCategoryofcustomer));
                cmd.Parameters.Add(new SqlParameter("@ClosingBal", string.IsNullOrEmpty(ClosingBal) ? (object)DBNull.Value : ClosingBal));

                cmd.ExecuteNonQuery();
                con.Close();

My full code is this and i just want where String value like title,gender etc are empty then will save Null in DB table. But using this code, whenever String value like title, gender etc is empty then data is not inserting. And if all string have value then data save perfectly. Please see the code and correct me.

Why? What do you mean `empty` in the first place? An empty string isn't a NULL, it's a string with a well-defined value. It *can* be inserted in a non-null column. It's *NULL's* that cause problems, not "empty" strings — Panagiotis Kanavos, Jul 29 '19 at 10:06
Check IsNullOrEmpty() for each nullable columns and pass DBNull.Value for them — Ajoe, Jul 29 '19 at 10:09
As for the query itself, it's generating the cartesian product of all title, gender, category etc rows that match the criteria. Unless each condition returns only *one* result, you could get multiple entries for the same person. Is that what you really want? Did you expect to get only one row from each table this way, instead of writing a proper lookup? — Panagiotis Kanavos, Jul 29 '19 at 10:09
@PanagiotisKanavos actually, im using title, gender etc for compare with string and save its id and the result of this query in a table of one row — Anonymous, Jul 29 '19 at 10:33
Evolve - NO ONE should be using [old style joins](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins). — SMor, Jul 29 '19 at 12:11
There is no point to storing both age and date of birth. And surely much of this very sensitive information should be encrypted. — SMor, Jul 29 '19 at 12:14

score 1 · Answer 1 · answered Jul 29 '19 at 10:27

It is a bit unclear what you are asking, because "empty" has no technical meaning in SQL.

I can interpret this as the parameter values are NULL. If so:

WHERE (t.title = @Title OR @Title IS NULL) AND
      (g.gendername = @gender OR @gender IS NULL) AND
      (cat.catagoryname = @category OR @category IS NULL) AND
      . . .

score 0 · Answer 2 · answered Jul 29 '19 at 10:04

Use IsNull to default to an acceptable value

INSERT INTO odisha_customer_data 
            ([share _id], 
             firstname, 
             middlename, 
             lastname, 
             fatherhusbandname, 
             mothername, 
             id, 
             dob, 
             age, 
             custnaturedesc, 
             [date of membership], 
             religion, 
             nationality, 
             uid, 
             pan, 
             segment, 
             introduceraccno, 
             empcode, 
             idproof, 
             idproofno, 
             residentialproofdesc, 
             residentialproofno, 
             annualincomedetails, 
             [risk category of customer], 
             [closing bal], 
             title, 
             gender, 
             category, 
             occupation, 
             community, 
             qualification, 
             maritalstatusdesc) 
SELECT @ShareId, 
       @FirstName, 
       @MiddleName, 
       @LastName, 
       @FatherHusbandName, 
       @MotherName, 
       @id, 
       @DOB, 
       @Age, 
       @CustNatureDesc, 
       @DateofMembership, 
       @Religion, 
       @Nationality, 
       @UID, 
       @PAN, 
       @Segment, 
       @IntroducerAccNo, 
       @EmpCode, 
       @IDproof, 
       @IDProofNo, 
       @ResidentialProofDesc, 
       @ResidentialProofNo, 
       @AnnualIncomeDetails, 
       @RiskCategoryofcustomer, 
       @ClosingBal, 
       t.titleid         AS title, 
       g.genderid        AS gender, 
       cat.catagoryid    AS category, 
       o.occupationid    AS Occupation, 
       c.communityid     AS Community, 
       q.qualificationid AS Qualification, 
       m.maritalstatusid AS MaritalStatus 
FROM   title t, 
       gender g, 
       catagory cat, 
       occupation o, 
       community c, 
       isnull(qualification,'NA') q, 
       maritalstatus m 
WHERE  t.title = @Title 
       AND g.gendername = @gender 
       AND cat.catagoryname = @category 
       AND o.occupationname = @occupation 
       AND c.communityname = @community 
       AND q.qualificationname = @Qualification 
       AND m.maritalstatusdesc = @MaritalStatusDesc

The OP is asking the opposite - how to return *NULL* when a value is "empty" whatever that means — Panagiotis Kanavos, Jul 29 '19 at 10:05

score 0 · Answer 3 · answered Sep 13 '21 at 08:15

If you want to convert empty spaces into null values on insert, try the following:

INSERT INTO new_table ( empty_col, other )
SELECT
   NULLIF(empty_col, ''),
   other
FROM old_table

Check the following discussion for more details, if this fits your problem:

How to convert empty spaces into null values, using SQL Server?

Insert Null where value is empty using insert select query

3 Answers3