In how many ways we can insert records in SQL server

Syntax 1: Single Row at a time:

This is basic syntax to insert data inside table. This way specifies both the column names and the values to be inserted:

 
INSERT INTO tableName
              (column1, column2, …)
  VALUES      (value1, value2, …)

Syntax 2: Multiple row at a time:

The first way specifies both the column names and the values to be inserted. In this case we can insert multiple row at a time like below.

 
INSERT INTO tableName
              (column1, column2, …)
  VALUES      (value1, value2, …)
      (value1, value2, …)
      (value1, value2, …)
      (value1, value2, …)

Syntax 3: No Column name

If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows:

 
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Syntax 4: Insert into Select:

 
INSERT into Example_2 (column_2_1, column_2_2) select (column_1_1, column_1_2) from Example_1;

and

 
INSERT into Example_2 select * from Example_1;

The only criteria is that number of columns and their data types returned in select query should match with insert table command.

Syntax 5: Select into statement:

 

SELECT * into ExampleCopy from Example;

Here ExampleCopy table is not created prior to execution of the query. It creates new table from existing table.

Syntax 6: Arithmetic Operation

 
INSERT INTO tableName (ColumnX, ColumnY)
VALUES      (10/3, 5),
            (10/5, 2),
            (10/2, 5)

Syntax 7: Using Stored Procedure:

We can insert data into the table using store procedure.

 
CREATE procedure spGetExample as
BEGIN
SELECT * FROM Example
END

INSERT into Example
EXC spGetExample

Syntax 8: INSERT SELECT and Unique Identifiers

When adding data to column declared with the uniqueidentifier type use the NEWID() function to generate a globally unique value.

As an example

 
INSERT INTO dbo.esqlSalesPerson
            (City, FullName, rowguid)
VALUES      ('Traverse City', 'Donald Sax', NEWID())

Inserts a new row into the esqlSalesPerson. If you run the command again, another row is added, but the rowguid value is different.

NEWID() generates a new value each time it is called.

Way 9: Bulk Insert: Through another files like CSV or Excel

Through Data Import option also we can insert Data.”RightClick on Database -> Select Task-> Export Data -> Select CSV/ Excel File (Data Source)->Selet the specific file-> Select DB name -> Select table Name-> Preview – Finish

BULK INSERT. Bulk inserts data from files. It's a little quirky and not very flexible when it comes to parsing files, but if you can get the data to line up it works well enough. Selecting data for bulk insert purposes can also be done with OPENROWSET(BULK, ...).

INSERT BULK. This is an internal command that's used under the covers by drivers that use the bulk insert protocol in TDS (the protocol used by SQL Server). You do not issue these commands yourself. Unlike BULK INSERT, this is for client-side initiated bulk inserting, for example through the SqlBulkCopy class in .NET, or SQL Server's own bcp tool.