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)
No comments:
Post a Comment