SQL Insert Query Example

Learn how to insert data into SQL table, We can insert a single row or multiple rows with single query, we also can insert data into sql table from another table.

Before writing any insert query, keep following points in mind to avoid any error.

  • Inserted values must match with the data type of that column, means if you try to insert string value into integer column type, then that will produce error.
  • Inserted value length is more than the data length specified in that column, suppose if you try to insert string of 100 character, and the column accept only 50 character, then also query will throw error.
  • If any column has some relationship defined with other table column, and that relationship is not satisfied, then also the insert query will fail.

Inserting new record to sql table

Here is the example of insert query syntax in sql table in ms sql database.

INSERT INTO TableName
    (field1,field2, field3, field4 )
VALUES
    (value1, value2, value3, value4)

While specifying values in insert query, if the value type is string, then you need to pass the value within single quote

Here is an practical example how your SQL query will look like

INSERT INTO [tbContactList]
(   [fullName]
    ,[email]
    ,[Organisation]
    ,[Address]
    ,[City]
    ,[lastNotified]        
)
VALUES
(  'Mr Alexa'
    ,'alexa@google.com'
    ,'Google INC'
    ,'USA'
    ,'Kolkata'
    ,getdate()
)
GO

Now if you notice in above query, for all value types we have used single quote, except date type

Insert Query using Select Statement

You also can write insert query using select statement, this type of insert statement are very useful when you want to pull data from one table to another and table structure match, you also can insert data from view to table, take a look at the example below

Insert into [dbo].[tbOldStudent]
SELECT [Firstname]
    ,[Lastname]
    ,[Email]
    ,[ContactNumber]
    ,[DOB]
FROM [dbo].[tbStudent]

Insert Query Failing Reason

Sometimes you may experience that insert query failing, Here are some of the reason to look at to find the root cause

  • If column data type and value data type is not same
  • If the value data length is more than specified in column

If you are inserting more than one table you may need to use Transaction

Sql insert multiple rows query syntax

You can insert multiple records into SQL table with single statement, this technique will be very useful when you want to insert master data or testing data.

INSERT INTO [dbo].[tbSoldProduct]
(ProductId,[ProductName],[UnitPrice],[SoldQuantity],[SDate])
VALUES
(1, 'Banana',3.50,400,getdate()),
(2, 'Orange',4.50,300,getdate()),
(3, 'apple',5.20,200,getdate()),
(4, 'cherries',1.20,900,getdate()),
(5, 'blueberries',0.50,1500,getdate()),
(1, 'Banana',2.50,900,DATEADD(month, -1, getdate())),
(2, 'Orange',4.10,400,DATEADD(month, -1, getdate())),
(3, 'apple',5.10,700,DATEADD(month, -1, getdate())),
(4, 'cherries',1.00,500,DATEADD(month, -1, getdate())),
(5, 'blueberries',0.90,1400,DATEADD(month, -1, getdate())),
(1, 'Banana',2.10,550,DATEADD(month, -2, getdate())),
(2, 'Orange',3.50,700,DATEADD(month, -2, getdate())),
(3, 'apple',4.75,500,DATEADD(month, -2, getdate())),
(4, 'cherries',1.16,950,DATEADD(month, -2, getdate())),
(5, 'blueberries',1.10,1200,DATEADD(month, -2, getdate())),

You should also read following post

 
Hire SQL Developer
Insert Query in SQL Server Database
SQL job Interview Questions Answers
Course in Demand
MS SQL Examples | Join MS SQL Course