<p>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:</p>

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

<p>I tried but get a syntax error message.</p>

<p>What would you do if you want to do this?</p>

[{'answer_id': 74196, 'author': 'Forgotten Semicolon', 'author_id': 1960, 'author_profile': 'https://Stackoverflow.com/users/1960', 'pm_score': 3, 'selected': False, 'text': '<p>Remove <code>VALUES</code> from your SQL.</p>\n'}, {'answer_id': 74204, 'author': 'pilsetnieks', 'author_id': 6615, 'author_profile': 'https://Stackoverflow.com/users/6615', 'pm_score': 9, 'selected': True, 'text': '<p>No "VALUES", no parenthesis:</p>\n\n<pre><code>INSERT INTO Table2(LongIntColumn2, CurrencyColumn2)\nSELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1;\n</code></pre>\n'}, {'answer_id': 74214, 'author': 'GSerg', 'author_id': 11683, 'author_profile': 'https://Stackoverflow.com/users/11683', 'pm_score': 5, 'selected': False, 'text': '<p>Remove both VALUES and the parenthesis.</p>\n\n<pre><code>INSERT INTO Table2 (LongIntColumn2, CurrencyColumn2)\nSELECT LongIntColumn1, Avg(CurrencyColumn) FROM Table1 GROUP BY LongIntColumn1\n</code></pre>\n'}, {'answer_id': 74222, 'author': 'Jorge Ferreira', 'author_id': 6508, 'author_profile': 'https://Stackoverflow.com/users/6508', 'pm_score': 5, 'selected': False, 'text': '<p>You have two syntax options:</p>\n\n<p><strong>Option 1</strong></p>\n\n<pre><code>CREATE TABLE Table1 (\n    id int identity(1, 1) not null,\n    LongIntColumn1 int,\n    CurrencyColumn money\n)\n\nCREATE TABLE Table2 (\n    id int identity(1, 1) not null,\n    LongIntColumn2 int,\n    CurrencyColumn2 money\n)\n\nINSERT INTO Table1 VALUES(12, 12.00)\nINSERT INTO Table1 VALUES(11, 13.00)\n\nINSERT INTO Table2\nSELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1\n</code></pre>\n\n<p><strong>Option 2</strong></p>\n\n<pre><code>CREATE TABLE Table1 (\n    id int identity(1, 1) not null,\n    LongIntColumn1 int,\n    CurrencyColumn money\n)\n\nINSERT INTO Table1 VALUES(12, 12.00)\nINSERT INTO Table1 VALUES(11, 13.00)\n\n\nSELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1\nINTO Table2\nFROM Table1\nGROUP BY LongIntColumn1\n</code></pre>\n\n<p>Bear in mind that Option 2 will create a table with only the columns on the projection (those on the SELECT).</p>\n'}, {'answer_id': 74231, 'author': 'Philippe Grondier', 'author_id': 11436, 'author_profile': 'https://Stackoverflow.com/users/11436', 'pm_score': 2, 'selected': False, 'text': '<p>Well I think the best way would be (will be?) to define 2 recordsets and use them as an intermediate between the 2 tables. </p>\n\n<ol>\n<li>Open both recordsets</li>\n<li>Extract the data from the first table (SELECT blablabla) </li>\n<li>Update 2nd recordset with data available in the first recordset (either by adding new records or updating existing records</li>\n<li>Close both recordsets</li>\n</ol>\n\n<p>This method is particularly interesting if you plan to update tables from different databases (ie each recordset can have its own connection ...)</p>\n'}, {'answer_id': 74239, 'author': 'Sean', 'author_id': 8334, 'author_profile': 'https://Stackoverflow.com/users/8334', 'pm_score': 4, 'selected': False, 'text': '<p>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. </p>\n\n<p>Also, you really don\'t need the parentheses around the select statement.  </p>\n\n<p>From <a href="http://msdn.microsoft.com/en-us/library/bb208861.aspx" rel="noreferrer">msdn</a>:</p>\n\n<p>Multiple-record append query:</p>\n\n<pre><code>INSERT INTO target [(field1[, field2[, …]])] [IN externaldatabase]\nSELECT [source.]field1[, field2[, …]\nFROM tableexpression\n</code></pre>\n\n<p>Single-record append query:</p>\n\n<pre><code>INSERT INTO target [(field1[, field2[, …]])]     \nVALUES (value1[, value2[, …])\n</code></pre>\n'}, {'answer_id': 74285, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<p>Do you want to insert extraction in an existing table? </p>\n\n<p>If it does not matter then you can try the below query:</p>\n\n<pre><code>SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 INTO T1 FROM Table1 \nGROUP BY LongIntColumn1);\n</code></pre>\n\n<p>It will create a new table -> T1 with the extracted information</p>\n'}, {'answer_id': 74347, 'author': 'Chris OC', 'author_id': 11041, 'author_profile': 'https://Stackoverflow.com/users/11041', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n\n<p>If the column names are the same in both tables, your query would be like this:</p>\n\n<pre><code>INSERT INTO Table2 (LongIntColumn, Junk)\nSELECT LongIntColumn, avg(CurrencyColumn) as CurrencyColumn1\nFROM Table1\nGROUP BY LongIntColumn;\n</code></pre>\n\n<p>And it would work without an alias:</p>\n\n<pre><code>INSERT INTO Table2 (LongIntColumn, Junk)\nSELECT LongIntColumn, avg(CurrencyColumn)\nFROM Table1\nGROUP BY LongIntColumn;\n</code></pre>\n'}, {'answer_id': 348981, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': "<p>inserting data form one table to another table in different DATABASE</p>\n\n<pre><code>insert into DocTypeGroup \n    Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType \n    from Opendatasource( 'SQLOLEDB','Data Source=10.132.20.19;UserID=sa;Password=gchaturthi').dbIPFMCI.dbo.DocTypeGroup\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/74162', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/8203/']