The target column list of an INSERT, UPDATE, or MERGE statement cannot contain both a sparse column and the column set that contains the sparse column. Rewrite the statement to include either the sparse column or the column set, but not both.

Suppose we have created a table tblData which has a sparse column and a column_set column

CREATE TABLE tbldata(

    Data VARCHAR(100) SPARSE,

    DataSet XML column_set FOR ALL_SPARSE_COLUMNS,

    DeletedDate DATE DEFAULT(GETDATE())

)

If we will try to insert a records into the tblData by using following sql query:

INSERT INTO tbldata(Data,DataSet) VALUES(NULL,NULL)

We will get error message :

The target column list of an INSERT, UPDATE, or MERGE statement cannot contain both a sparse column and the column set that contains the sparse column. Rewrite the statement to include either the sparse column or the column set, but not both.

Cause: we cannot insert ,update or merge both spare columns and column-set  column at the same time. Since if will update or insert any one it will automatically reflected to other.

Solution:

Correct way to insert or update records :

INSERT INTO tbldata(Data) VALUES(NULL)

Or

UPDATE tbldata SET DataSet = NULL

Or

INSERT INTO tbldata(DataSet) VALUES(NULL)