How to do INSERT into a table records extracted from another table

You have two syntax options:

Option 1

CREATE TABLE Table1 (
    id int identity(1, 1) not null,
    LongIntColumn1 int,
    CurrencyColumn money
)

CREATE TABLE Table2 (
    id int identity(1, 1) not null,
    LongIntColumn2 int,
    CurrencyColumn2 money
)

INSERT INTO Table1 VALUES(12, 12.00)
INSERT INTO Table1 VALUES(11, 13.00)

INSERT INTO Table2
SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1

Option 2

CREATE TABLE Table1 (
    id int identity(1, 1) not null,
    LongIntColumn1 int,
    CurrencyColumn money
)

INSERT INTO Table1 VALUES(12, 12.00)
INSERT INTO Table1 VALUES(11, 13.00)


SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1
INTO Table2
FROM Table1
GROUP BY LongIntColumn1

Bear in mind that Option 2 will create a table with only the columns on the projection (those on the SELECT).

Remove both VALUES and the parenthesis.

INSERT INTO Table2 (LongIntColumn2, CurrencyColumn2)
SELECT LongIntColumn1, Avg(CurrencyColumn) FROM Table1 GROUP BY LongIntColumn1

I believe your problem in this instance is the "values" keyword. You use the "values" keyword when you are inserting only one row of data. For inserting the results of a select, you don't need it.

Also, you really don't need the parentheses around the select statement.

From msdn:

Multiple-record append query:

INSERT INTO target [(field1[, field2[, …]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, …]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, …]])]     
VALUES (value1[, value2[, …])

No "VALUES", no parenthesis:

INSERT INTO Table2(LongIntColumn2, CurrencyColumn2)
SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1;