Video Tutorial:

How to insert data into Table in SQL Server?

     I am going to show 3 ways of inserting data into our Weights table which has following structure:

CREATE TABLE dbo.WeightsData (
   ID  int  primary key,
   Name  varchar(max),
   Weight  int
)

1). insert into table by specifying Column names :

syntax: 

insert into {table name}   ({column 1} ,{column 2} ,{column 3}...)
VALUES                              ({value 1}    ,{value 2}     ,{value 3}   ...)

insert into dbo.WeightsData (ID ,Name ,Weight)
VALUES                                     (1  , 'bob'   ,    55)

once data insert statement is executed, lets check the data in our table using select statement.

select   *   from  dbo.WeightsData

output:

IDNameWeight
1bob55   

inserting more than 1 row at a time:

insert into dbo.WeightsData (ID ,Name , Weight)
VALUES                                     (2  ,'sam'    ,65        ),
                                                    (3  ,'lisa'     ,45       )

lets check our data again:

select   *   from  dbo.WeightsData

output:

IDNameWeight
1bob55
2sam65
3lisa45

2.) Inserting rows to tables without specifying column names :

When you dont want to specify column names, we should make sure to type values in the same order as in the table. Also we should not skip any column values unlike in first method.

example:

insert into dbo.WeightsData
VALUES  (4 , 'john' , 65),
                 (5  , 'jack' ,  45)

after checking data again:

IDNameWeight
1bob55
2sam65
3lisa45
4john65
5jack45

3). Using select statement to insert data from another table :

Lets assume we have one more table dbo.WeightsData2 with following values :

IDNameWeight
7mary45
8carl65

We can insert these two rows to our table using following query:

insert into dbo.WeightsData
select ID,Name,Weight from dbo.WeightsData2

Now if we check our dbo.WeightsData table again:

IDNameWeight
1bob55
2sam65
3lisa45
4john65
5jack45
7mary45
8carl65

 

Inserting values into a table which has identity column :

   lets assume we have a table with identity column

CREATE TABLE dbo.WeightsData3 (
ID int identity(1,1),
Name varchar(max),
Weight int
)

What is an identity column?

     An identity column will auto insert data into itself.

For example, in our table, the ID column in identity(1,1) which means, if we insert one row, its ID will be 1, the next time we insert a row, the ID will be 2 and so on.. 

So it starts from 1 and increments by 1 for every value.

syntax to insert data into identity column table:

At first our table is empty.

After executing the below insert query,

insert into dbo.WeightsData3
VALUES ('first'     ,  45),
               ('second', 65)

The table will we values like this:

IDNameWeight
1first45
2second65

The ID column has auto inserted values 1 and 2.

 

Further reading:

1.) INSERT (Transact-SQL)