Adding Data With Subquery

You can use subquery, SELECT in INSERT statement to add values to a table or several tables or views. Subquery allows you to add a few rows at once into a table.

What is the difference subquery usage, with usage VALUES?. Subquery will add a number of existing data, while the VALUES to add new data. For example, we create a new table named TmpPublisher into the pubs database. The table contains four fields and made with command :

CREATE TABLE TmpPublisher
SELECT pub_name varchar, city, state, country FROM Publisher WHERE COUNTRY =’USA’

If the subquery is used in INSERT command, then the subquery is not necessary are enclosed in brackets. Subquery produce the same four columns and fields in the destination table. For example in tanel pub_name TmpPublisher will be filled only for the data not from the USA, then the INSERT clause you should declare a column that will contain the data.

INSERT INTO TmpPublisher (pub_name)
 SELECT pub_name FROM Publisher WHERE NOT COUNTRY = 'USA'