SQL Server: How to automatically stop unnecessary data at insert
Introduction
This article is about how to automatically stop unnecessary data when inserting data in a SQL Server Table.
Problem
One of the problematic aspects of SQL Server is to stop the unnecessary data at the time inserting automatically in SQL Server
First, we will create #test table and will insert data in this table
CREATE TABLE #test
(Name varchar(30) ,
Weight float)
INSERT #test VALUES ('DR.Joe Glass', 112)
INSERT #test VALUES ('Glass Joe', 112)
INSERT #test VALUES ('Piston Hurricane', 176)
INSERT #test VALUES ('DR.Bald Bull', 298)
INSERT #test VALUES ('Sugar Ray Ali', 151)
INSERT #test VALUES ('Leon Holmes', 119)
INSERT #test VALUES ('George Liston', 139)
INSERT #test VALUES ('DR.Larry Leonard', 115)
INSERT #test VALUES ('Mike Mooncalf', 134)
INSERT #test VALUES ('Joe Glass', 112)
select * from #test
Name Weight
------------------------------ ----------------------
DR.Joe Glass 112
Glass Joe 112
Piston Hurricane 176
DR.Bald Bull 298
Sugar Ray Ali 151
Leon Holmes 119
George Liston 139
DR.Larry Leonard 115
Mike Mooncalf 134
Joe Glass 112
(10 row(s) affected)
After inserting we have 10 rows but three rows begin with characters "DR".
select * from #test where SUBSTRING(Name,1,2) like 'DR%'
Name Weight
------------------------------ ----------------------
DR.Joe Glass 112
DR.Bald Bull 298
DR.Larry Leonard 115
(3 row(s) affected)
If we do not want to load the data that begins with "DR" and count is not 10 rows but million rows, we will insert with bulk insert then we have a great problem within this data.
Solution
For these problems use the power of Microsoft SQL Server language: "Check" Constraint.
But how can we use this constant in "bulk loading" or "inserting" in a table without unnecessary data?
First, we will create #test table but in column Name, we will add a constraint for stopping insert of unnecessary data.
CREATE TABLE #test
(Name varchar(30) CHECK (LEFT(Name,2)<>'DR'), --- Establish a check constraint for stopping uncessary data
Weight float)
------ Now insert data starts with the 'DR'
INSERT #test VALUES ('DR.Joe Glass', 112)
INSERT #test VALUES ('Glass Joe', 112)
INSERT #test VALUES ('Piston Hurricane', 176)
INSERT #test VALUES ('DR.Bald Bull', 298)
INSERT #test VALUES ('Sugar Ray Ali', 151)
INSERT #test VALUES ('Leon Holmes', 119)
INSERT #test VALUES ('George Liston', 139)
INSERT #test VALUES ('DR.Larry Leonard', 115)
INSERT #test VALUES ('Mike Mooncalf', 134)
INSERT #test VALUES ('Joe Glass', 112)
Msg 547, Level 16, State 0, Line 29
The INSERT statement conflicted with the CHECK constraint "CK__#test_______Name__4222D4EF". The conflict occurred in database "tempdb", table "dbo.#test_______________________________________________________________________________________________________________00000000003D", column 'Name'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 32
The INSERT statement conflicted with the CHECK constraint "CK__#test_______Name__4222D4EF". The conflict occurred in database "tempdb", table "dbo.#test_______________________________________________________________________________________________________________00000000003D", column 'Name'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 36
The INSERT statement conflicted with the CHECK constraint "CK__#test_______Name__4222D4EF". The conflict occurred in database "tempdb", table "dbo.#test_______________________________________________________________________________________________________________00000000003D", column 'Name'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
SQL Server gives us a message:
"INSERT statement conflicted ``with the ``CHECK constraint "CK__#test_______Name__4222D4EF"
Select testing results from our query:
select * from #test
select * from #test where SUBSTRING(Name,1,2) like 'DR%'
Name Weight
------------------------------ ----------------------
Glass Joe 112
Piston Hurricane 176
Sugar Ray Ali 151
Leon Holmes 119
George Liston 139
Mike Mooncalf 134
Joe Glass 112
(7 row(s) affected)
Name Weight
------------------------------ ----------------------
(0 row(s) affected)
The result was a success, inserting data without unnecessary data "DR".
Conclusion
This article shows how to use CHECK constraints to avoid inserting unwanted data in a table.