How to use SQL Insert SELECT statements

How to use SQL Insert SELECT statements

The SELECT subquery in the INSERT statement can be used to add the values of one or more other tables or views to the table. Use a SELECT subquery to insert multiple rows at the same time.

The following INSERT statement inserts the data in titles for all rows of modern cooking into a separate table:

Use Pubsinsert into MyBooks select title_id, title, type from titles WHERE type = ' Mod_cook ' The selection list of the subquery must be the column with the INSERT statement column Table Matching. If you do not specify a list of columns, the select list must match the columns that you are inserting into the table or view.

INSERT ... Another effect of the SELECT statement is to insert data from an external data source in Microsoft®sql Server™. The SELECT in the INSERT statement can:

Use a four-part name to refer to a remote table on a linked server. For more information, see Identifying a data source using a linked server name.

Use OPENROWSET to refer to a remote table. For more information, see Identifying a data source with a special name.

7> CREATE TABLE Customers (
8> CustomerID NCHAR (5) Not NULL,
9> CompanyName nvarchar () not NULL,
10> contactname nvarchar () NULL,
11> contacttitle nvarchar () NULL,
12> address nvarchar NULL,
13> City nvarchar (a) NULL,
14> Region nvarchar () NULL,
15> PostalCode nvarchar () NULL,
16> Country nvarchar () NULL,
17> Phone nvarchar () NULL,
18> Fax nvarchar () NULL
19>)
20> Go
1>
2>
3> INSERT into Customers (CustomerID, CompanyName) SELECT ' 1 ', ' Bam Bam '
4> Go

(1 rows affected)
1>
2> select * from Customers;
3> Go
CustomerID CompanyName ContactName ContactTitle Address City Region
PostalCode Country Phone Fax
---------- ---------------------------------------- ------------------------------ ------------------------------ - ----------------------------------------------------------- --------------- ---------
------ ---------- --------------- ------------------------ ------------------------
1          Bam bam                                    null                            null                             null                                                            null             NULL
NULL-NULL NULL NULL

(1 rows affected)
1>
2> drop table Customers;
3> Go

Instance

3> INSERT into Billingarchive
4> (Bankerid, Billingnumber, Billingtotal, Credittotal,
5> paymenttotal, Termsid, BillingDate, Billingduedate)
6> SELECT
7> Bankerid, Billingnumber, Billingtotal, Credittotal,
8> paymenttotal, Termsid, BillingDate, billingduedate
9> from Billings
10> WHERE billingtotal-paymenttotal-credittotal = 0
11> Go

You can also use stored procedures to handle

3> CREATE PROCEDURE spins_employee
4> @FirstName     NVarChar (x),
5> @LastName   NVarChar,
6> @Salary    money
7> as
8> INSERT into Employee (ID, first_name, last _name, Salary)
9> SELECT, @FirstName, @LastName, @Salary
10> go
1> spins_employee ' Gadget ', ' Bond ' , 49.95
2> go