SQL: Toddlers Query (Insert Statement)

We are now in the phase of Data Manipulation Language. I’ll cover 3 parts of the DML statements which includes INSERT, UPDATE and DELETE.

In a General point of view, INSERT statement by my own definition is basically inserting data to a created table in a database.

Basic Syntax:

INSERT INTO TableName (column-1, column-2, column-n..) VALUES (value-1, value-2, value-n..);

Below are the sample INSERT statements that you can use in your day to day SQL transactions.

Based on my previous post, we have a sample database named “RutherRoque” with table named “Friends”. If you will notice there has been an additional column on my “Friends” table. I’ve added column “FriendsID” (Primary Key) and “Friends_Number” (Foreign Key). I’ll discuss the usage of PK and FK on my next post on “How to use table constraints”. I don’t want to complicate things now, l just want us to focus on how to insert a data.

FriendsID	Friends_Number	FirstName	LastName	Country	City
1	7001	Mel	Jacob	Philippines	Quezon
2	7002	CJ	Samuel	Philippines	Valenzuela
3	7003	Jasmin	Tyler	Philippines	Quezon
4	7004	Fred	Dylan	Philippines	Novaliches
5	7005	Joseph	Jonathan	Philippines	Pasig
6	7006	Jennifer	Caleb	USA	Corona

 Example:

                INSERT INTO Friends (FriendsID, Friends_Number, FirstName, LastName, Country, City) VALUES (7, 7007, Champy, Rivera, ‘New Zealand’, Oakland);

After inserting the values, run a SELECT statement to view the inserted value:

SELECT * FROM Friends WHERE FirstName =’Champy’ and LastName =’Rivera’;

FriendsID	Friends_Number	FirstName	LastName	Country	City
7	7007	Champy	Rivera	New Zealand	Oakland

 

If you want to INSERT multiple rows in a column. Try the next sample below:

                INSERT INTO Friends (FriendsID, Friends_Number, FirstName, LastName, Country, City) VALUES

                (8, 7008, Ivy, Hotdog, ‘New Zealand’, Oakland),
                (9, 7009, Sydney, Strips, Philippines, Malabon),
                (10, 7010, Miguel, Porkchop, Philippines, Mandaluyong);

Run a SELECT statement that will view all the data in Friends table:

SELECT * FROM Friends;

FriendsID	Friends_Number	FirstName	LastName	Country	City
1	7001	Mel	Jacob	Philippines	Quezon
2	7002	CJ	Samuel	Philippines	Valenzuela
3	7003	Jasmin	Tyler	Philippines	Quezon
4	7004	Fred	Dylan	Philippines	Novaliches
5	7005	Joseph	Jonathan	Philippines	Pasig
6	7006	Jennifer	Caleb	USA	Corona
7	7007	Champy	Rivera	New Zealand	Oakland
8	7008	Ivy	Hotdog	New Zealand	Oakland
9	7009	Sydney	Strips	Philippines	Malabon
10	7010	Miguel	Porkchop	Philippines	Mandaluyong

 NOTE:

All the listed tables are phantom tables. If you want to test the same queries I made, you need to create RutherRoque Database with the same tables and columns listed on my post.

Try this:

How to Create a Database:

Start SQL Server Management Studio (SSMS) and connect to your SQL server instance

-          In the Object Explorer, Expand the SQL Server instance

-          Right click the “Databases”, and then click the “New Database…”

-          Type the Database Name “RutherRoque” and set the Owner to <default> then check the “Use full-text indexing”

-          Click Ok

-          Otherwise continue with the following steps.

 How to Create Table:

-          In the Object Explorer, Expand the SQL Server instance

-          Expand the “Databases”, expand the created “RutherRoque” database

-          Right click “Tables”, and then click “New Table”, set the table name to “Friends” in the table properties.

-          Type and add the Column Names, Data Type, and if tables Allow Nulls based on the phantom tables.

Column Name	FriendsID	Friends_Number	FirstName	LastName	Country	City
Data Type	Int ()	Int ()	Nvarchar (128)	Nvarchar (128)	Nvarchar (128)	Nvarchar (128)
If Table Allow Null	Not Null	Not Null	Not Null	Not Null	Not Null	Not Null

-          Click Save

For this reason, let’s make it simple. Just follow the steps provided on how to create a database and a table along with the columns. Once your done, you can already use the queries stated on my examples. I'll discuss on my next post on how to create a Database and a Table using a script.

If you have question/s or comment/s, please feel free to ask.

Reference:

Data Manipulation language- http://en.widipedia.org/wiki/Data_manipulation_language

Int, bigint, smallint, and tinyint (Transact SQL) - http://msdn.microsoft.com/en-us/library/ms187745.aspx