How to do INSERT into a table records extracted fr

Question

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want some query like this:

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

I tried but get a syntax error message.

What would you do if you want to do this?

Answer 1

Remove "values" when you're appending a group of rows, and remove the extra parentheses. You can avoid the circular reference by using an alias for avg(CurrencyColumn) (as you did in your example) or by not using an alias at all.

If the column names are the same in both tables, your query would be like this:

INSERT INTO Table2 (LongIntColumn, Junk)
SELECT LongIntColumn, avg(CurrencyColumn) as CurrencyColumn1
FROM Table1
GROUP BY LongIntColumn;

And it would work without an alias:

INSERT INTO Table2 (LongIntColumn, Junk)
SELECT LongIntColumn, avg(CurrencyColumn)
FROM Table1
GROUP BY LongIntColumn;

Answer 2

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[, …])

Answer 3

inserting data form one table to another table in different DATABASE

insert into DocTypeGroup 
    Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType 
    from Opendatasource( 'SQLOLEDB','Data Source=10.132.20.19;UserID=sa;Password=gchaturthi').dbIPFMCI.dbo.DocTypeGroup

Answer 4

No "VALUES", no parenthesis:

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

Answer 5

Do you want to insert extraction in an existing table?

If it does not matter then you can try the below query:

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

It will create a new table -> T1 with the extracted information

Answer 6

Remove VALUES from your SQL.