SQL Server INSERT Multiple Rows Into a Table Using One Statement

Summary : in this tutorial, you will learn how to insert multiple rows into a table using a individual SQL Server INSERT instruction. In the previous tutorial, you have learned how to add one row at a time to a board by using the INSERT instruction. To add multiple rows to a board at once, you use the watch form of the INSERT statement :

 

INSERT

INTO

table_name (column_list)

VALUES

(value_list_1), (value_list_2), ... (value_list_n);
Code lyric : SQL ( Structured Query Language ) ( sql )

In this syntax, rather of using a single tilt of values, you use multiple comma-separated lists of values for interpolation. The number of rows that you can insert at a time is 1,000 rows using this form of the INSERT argument. If you want to insert more rows than that, you should consider using multiple INSERT statements, BULK INSERT or a derived board. notice that this INSERT multiple rows syntax is merely supported in SQL Server 2008 or late. To insert multiple rows returned from a SELECT instruction, you use the INSERT INTO SELECT statement .

SQL Server INSERT multiple rows – examples

We will use the sales.promotions board created in the previous tutorial for the presentation. If you have not however created the sales.promotions postpone, you can use the comply CREATE TABLE statement :

 

CREATE

TABLE

sales.promotions ( promotion_id

INT

PRIMARY

KEY

IDENTITY

(

1

,

1

), promotion_name

VARCHAR

(

255

)

NOT

NULL

, discount

NUMERIC

(

3

,

2

)

DEFAULT

0

, start_date

DATE

NOT

NULL

, expired_date

DATE

NOT

NULL

);
Code linguistic process : SQL ( Structured Query Language ) ( sql )

1) Inserting multiple rows example

The adopt instruction inserts multiple rows to the sales.promotions board :

 

INSERT

INTO

sales.promotions ( promotion_name, discount, start_date, expired_date )

VALUES

(

'2019 Summer Promotion'

,

0.15

,

'20190601'

,

'20190901'

), (

'2019 Fall Promotion'

,

0.20

,

'20191001'

,

'20191101'

), (

'2019 Winter Promotion'

,

0.25

,

'20191201'

,

'20200101'

);
Code speech : SQL ( Structured Query Language ) ( sql )

SQL server issued the adopt message indicating that three rows have been inserted successfully .

 (3 rows affected)
 Code language :  SQL ( Structured Query Language )  ( sql )

Let ’ s verify the insert by executing the follow question :

 

SELECT

*

FROM

sales.promotions;
Code terminology : SQL ( Structured Query Language ) ( sql )

here is the output :SQL Server INSERT multiple rows example

2) Inserting multiple rows and returning the inserted id list example

This exercise inserts three rows into the sales.promotions table and returns the promotion identity list :

 

INSERT

INTO

sales.promotions ( promotion_name, discount, start_date, expired_date )

OUTPUT

inserted.promotion_id

VALUES

(

'2020 Summer Promotion'

,

0.25

,

'20200601'

,

'20200901'

), (

'2020 Fall Promotion'

,

0.10

,

'20201001'

,

'20201101'

), (

'2020 Winter Promotion'

,

0.25

,

'20201201'

,

'20210101'

);
Code language : SQL ( Structured Query Language ) ( sql )

SQL Server Insert Multiple Rows In this exercise, we added the OUTPUT article with the column that we want to return using the inserted.column_name syntax. If you want to return values from multiple columns, you can use the follow syntax :

 OUTPUT inserted.column1, inserted.column2...
 Code language :  SQL ( Structured Query Language )  ( sql  )

In this tutorial, you have learned how to use another shape of the SQL Server INSERT statement to insert multiple rows into a table using one INSERT statement .

reservoir : https://thefartiste.com
Category : Tech

About admin

I am the owner of the website thefartiste.com, my purpose is to bring all the most useful information to users.

Check Also

articlewriting1

How to Show Profile Picture Instead of Video in Zoom Meeting

Zoom is a democratic and utilitarian outside meet and television conferencing tool. however, not everyone …

Leave a Reply

Your email address will not be published.