Use of INSERT ... SELECT

How does this variant of INSERT work exactly (see link)?

http://www.sqlteam.com/article/using-select-to-insert-records

INSERT california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'

Also can it be used to replace an INSERT and UPDATE? If it can be 开发者_JAVA百科used in this way, is this limited to inserting static values (select value1=blah ...) or is there a way to pass a NOW() value?

---

The rows returned from the SELECT query are simply inserted, just as if you were to specify all the rows yourself using a set of VALUES expressions. The field count and types should match, in both cases.

And yes, given you had a table like ARTICLES(ID, TITLE, BODY, DATE) and you wanted the DATE field to be set to NOW() during insert, you could have done something like this:

INSERT articles(title, body, date)
SELECT title, body, NOW()
FROM other_articles

Then the value of NOW() would be used instead of the date column from the other_articles table.

---

An Insert cannot perform updates - it can only insert data in to a table, not update existing data.

It is not limited to static values.

Insert Into SomeTable (FirstName, LastName, FullName, ModifiedDate, Comment, Age)

Select FirstName,
       LastName,
       FirstName + ' ' + LastName,
       GetDate(),
       'This is the best comment ever',
       dbo.CustomFunctionToCalculateAge(DateOfBirth)

From Users

You can also omit the column declaration (although not recommended)

Insert Into Ages
Select 10

You should always ensure that the data type that is being inserted matches the data type of the column.

---

it inserts data into the table california_authors with the column names au_id, au_lname, au_fname and it gets it's data from the select statement select au_id, au_lname, au_fname from authors where state = 'CA'

the above select statement gives you a table with 3 columns which matches the insert column number.

say you have the table (birthdates)

id | firstname | birthdate

then you could do an insert like this

insert birthdates (id,firstname,birthdate) select 1,'Steven',NOW()

which would insert a new row into birthdates with the defined values.

You can also combine fetching data from tables with adding the from and "statically" inserting data by defining the value in the select