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:
<span class="pln"> INSERT INTO tableName </span><span class="pun">(</span><span class="pln">column1</span><span class="pun">,</span><span class="pln"> column2</span><span class="pun">,</span><span class="pln"> </span><span class="pun">…)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="pln">value1</span><span class="pun">,</span><span class="pln"> value2</span><span class="pun">,</span><span class="pln"> </span><span class="pun">…)</span>
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.
<span class="pln"> INSERT INTO tableName </span><span class="pun">(</span><span class="pln">column1</span><span class="pun">,</span><span class="pln"> column2</span><span class="pun">,</span><span class="pln"> </span><span class="pun">…)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="pln">value1</span><span class="pun">,</span><span class="pln"> value2</span><span class="pun">,</span><span class="pln"> </span><span class="pun">…)</span><span class="pln"> </span><span class="pun">(</span><span class="pln">value1</span><span class="pun">,</span><span class="pln"> value2</span><span class="pun">,</span><span class="pln"> </span><span class="pun">…)</span><span class="pln"> </span><span class="pun">(</span><span class="pln">value1</span><span class="pun">,</span><span class="pln"> value2</span><span class="pun">,</span><span class="pln"> </span><span class="pun">…)</span><span class="pln"> </span><span class="pun">(</span><span class="pln">value1</span><span class="pun">,</span><span class="pln"> value2</span><span class="pun">,</span><span class="pln"> </span><span class="pun">…)</span>
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:
<span class="pln"> INSERT INTO table_name VALUES </span><span class="pun">(</span><span class="pln">value1</span><span class="pun">,</span><span class="pln"> value2</span><span class="pun">,</span><span class="pln"> value3</span><span class="pun">,</span><span class="pln"> </span><span class="pun">...);</span>
Syntax 4: Insert into Select:
<span class="pln"> INSERT </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Example_2</span><span class="pln"> </span><span class="pun">(</span><span class="pln">column_2_1</span><span class="pun">,</span><span class="pln"> column_2_2</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">(</span><span class="pln">column_1_1</span><span class="pun">,</span><span class="pln"> column_1_2</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Example_1</span><span class="pun">;</span>
and
<span class="pln"> INSERT </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Example_2</span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Example_1</span><span class="pun">;</span>
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:
<span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">ExampleCopy</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Example</span><span class="pun">;</span><span class="pln"> </span>
Here ExampleCopy table is not created prior to execution of the query. It creates new table from existing table.
Syntax 6: Arithmetic Operation
<span class="pln"> INSERT INTO tableName </span><span class="pun">(</span><span class="typ">ColumnX</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ColumnY</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">10</span><span class="pun">/</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="lit">5</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">10</span><span class="pun">/</span><span class="lit">5</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">10</span><span class="pun">/</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="lit">5</span><span class="pun">)</span>
Syntax 7: Using Stored Procedure:
We can insert data into the table using store procedure.
<span class="pln"> CREATE procedure spGetExample </span><span class="kwd">as</span><span class="pln"> </span><span class="kwd">BEGIN</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Example</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> INSERT </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Example</span><span class="pln"> EXC spGetExample</span>
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
<span class="pln"> INSERT INTO dbo</span><span class="pun">.</span><span class="pln">esqlSalesPerson </span><span class="pun">(</span><span class="typ">City</span><span class="pun">,</span><span class="pln"> </span><span class="typ">FullName</span><span class="pun">,</span><span class="pln"> rowguid</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="str">'Traverse City'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Donald Sax'</span><span class="pun">,</span><span class="pln"> NEWID</span><span class="pun">())</span>
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, ...).
Way 10: Insert 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.
