Insert Table Into Same Table Again
Summary: in this tutorial, yous volition larn how to use the SQL Server INSERT INTO SELECT statement to add data from other tables to a table.
Introduction to SQL Server INSERT INTO SELECT statement
To insert data from other tables into a table, you lot utilise the post-obit SQL Server INSERT INTO SELECT argument:
INSERT [ Top ( expression ) [ Pct ] ] INTO target_table (column_list) query
Code language: SQL (Structured Query Language) ( sql ) In this syntax, the statement inserts rows returned by the query into the target_table.
The query is whatsoever valid SELECT statement that retrieves information from other tables. It must return the values that are respective to the columns specified in the column_list.
The TOP clause function is optional. It allows you to specify the number of rows returned by the query to be inserted into the target table. If you apply the Pct option, the statement will insert the percent of rows instead. Note that it is a all-time practice to always utilise the TOP clause with the Club BY clause.
SQL Server INSERT INTO SELECT examples
Let's create a table named addresses for the sit-in:
CREATE Table sales.addresses ( address_id INT IDENTITY PRIMARY Central, street VARCHAR (255) Non NULL, city VARCHAR (50), land VARCHAR (25), zip_code VARCHAR (five) );
Code language: SQL (Structured Query Language) ( sql ) 1) Insert all rows from another table example
The following statement inserts all addresses from the customers tabular array into the addresses tabular array:
INSERT INTO sales.addresses (street, city, state, zip_code) SELECT street, city, state, zip_code FROM sales.customers Gild BY first_name, last_name;
Lawmaking language: SQL (Structured Query Language) ( sql ) To verify the insert, yous use the following query:
SELECT * FROM sales.addresses;
Code linguistic communication: SQL (Structured Query Language) ( sql ) Here is the result:
2) Insert some rows from another tabular array example
Sometimes, you merely demand to insert some rows from some other table into a tabular array. In this instance, you lot limit the number of rows returned from the query by using conditions in the WHERE clause.
The following argument adds the addresses of the stores located in Santa Cruz and Baldwin to the addresses table:
INSERT INTO sales.addresses (street, metropolis, state, zip_code) SELECT street, city, state, zip_code FROM sales.stores WHERE metropolis IN ('Santa Cruz', 'Baldwin')
Lawmaking language: SQL (Structured Query Language) ( sql ) SQL Server returned the following message indicating that two rows have been inserted successfully.
Code language: SQL (Structured Query Linguistic communication) ( sql )
(ii rows afflicted)
iii) Insert the height N of rows
First, you use the following statement to delete all rows from the addresses table:
TRUNCATE Table sales.addresses;
Code linguistic communication: SQL (Structured Query Language) ( sql ) Second, to insert the top ten customers sorted by their first names and terminal names, you use the INSERT Meridian INTO SELECT statement as follows:
INSERT TOP (10) INTO sales.addresses (street, metropolis, land, zip_code) SELECT street, city, state, zip_code FROM sales.customers Society BY first_name, last_name;
Code language: SQL (Structured Query Language) ( sql ) SQL Server returned the post-obit message showing that ten rows have been inserted successfully.
Code language: SQL (Structured Query Linguistic communication) ( sql )
(x rows affected)
iv) Insert the top pct of rows
Instead of using an accented number of rows, yous can insert a percent number of rows into a table.
Start, truncate all rows from the addresses table:
TRUNCATE TABLE sales.addresses;
Code language: SQL (Structured Query Linguistic communication) ( sql ) Second, insert the top 10 pct of rows from the customers table sorted by first names and concluding names into the addresses table:
INSERT TOP (10) Percent INTO sales.addresses (street, metropolis, state, zip_code) SELECT street, city, country, zip_code FROM sales.customers ORDER By first_name, last_name;
Code language: SQL (Structured Query Language) ( sql ) SQL Server issued the post-obit message indicating that 145 rows have been inserted successfully.
Code language: SQL (Structured Query Linguistic communication) ( sql )
(145 rows affected)
In this tutorial, you have learned how to use the SQL Server INSERT INTO SELECT statement to insert rows from other tables into a table.
Source: https://www.sqlservertutorial.net/sql-server-basics/sql-server-insert-into-select/
0 Response to "Insert Table Into Same Table Again"
Postar um comentário