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

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

User · Answer

Remove VALUES from your SQL

User · Answer

Remove both VALUES and the parenthesis   INSERT INTO Table2  LongIntColumn2  CurrencyColumn2  SELECT LongIntColumn1  Avg CurrencyColumn  FROM Table1 GROUP BY LongIntColumn1

User · Answer

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

User · Answer

Remove VALUES from your SQL

User · Answer

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

User · Answer

Well I think the best way would be  will be   to define 2 recordsets and use them as an intermediate between the 2 tables     Open both recordsets Extract the data from the first table  SELECT blablabla   Update 2nd recordset with data available in the first recordset  either by adding new records or updating existing records Close both recordsets   This method is particularly interesting if you plan to update tables from different databases  ie each recordset can have its own connection

User · Answer

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

User · Answer

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

User · Answer

Remove both VALUES and the parenthesis   INSERT INTO Table2  LongIntColumn2  CurrencyColumn2  SELECT LongIntColumn1  Avg CurrencyColumn  FROM Table1 GROUP BY LongIntColumn1

User · Answer

Remove VALUES from your SQL

User · Answer

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

User · Answer

Remove VALUES from your SQL

User · Answer

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

User · Answer

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

User · Answer

Well I think the best way would be  will be   to define 2 recordsets and use them as an intermediate between the 2 tables     Open both recordsets Extract the data from the first table  SELECT blablabla   Update 2nd recordset with data available in the first recordset  either by adding new records or updating existing records Close both recordsets   This method is particularly interesting if you plan to update tables from different databases  ie each recordset can have its own connection

User · Answer

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

User · Answer

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

User · Answer

Well I think the best way would be  will be   to define 2 recordsets and use them as an intermediate between the 2 tables     Open both recordsets Extract the data from the first table  SELECT blablabla   Update 2nd recordset with data available in the first recordset  either by adding new records or updating existing records Close both recordsets   This method is particularly interesting if you plan to update tables from different databases  ie each recordset can have its own connection

User · Answer

No  VALUES   no parenthesis   INSERT INTO Table2 LongIntColumn2  CurrencyColumn2  SELECT LongIntColumn1  Avg CurrencyColumn  as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Remove both VALUES and the parenthesis   INSERT INTO Table2  LongIntColumn2  CurrencyColumn2  SELECT LongIntColumn1  Avg CurrencyColumn  FROM Table1 GROUP BY LongIntColumn1

User · Answer

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

User · Answer

No  VALUES   no parenthesis   INSERT INTO Table2 LongIntColumn2  CurrencyColumn2  SELECT LongIntColumn1  Avg CurrencyColumn  as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1

User · Answer

No  VALUES   no parenthesis   INSERT INTO Table2 LongIntColumn2  CurrencyColumn2  SELECT LongIntColumn1  Avg CurrencyColumn  as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1

User · Answer

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

User · Answer

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

User · Answer

Remove both VALUES and the parenthesis   INSERT INTO Table2  LongIntColumn2  CurrencyColumn2  SELECT LongIntColumn1  Avg CurrencyColumn  FROM Table1 GROUP BY LongIntColumn1

User · Answer

No  VALUES   no parenthesis   INSERT INTO Table2 LongIntColumn2  CurrencyColumn2  SELECT LongIntColumn1  Avg CurrencyColumn  as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1

User · Answer

Well I think the best way would be  will be   to define 2 recordsets and use them as an intermediate between the 2 tables     Open both recordsets Extract the data from the first table  SELECT blablabla   Update 2nd recordset with data available in the first recordset  either by adding new records or updating existing records Close both recordsets   This method is particularly interesting if you plan to update tables from different databases  ie each recordset can have its own connection

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How to do INSERT into a table records extracted from another table

Examples related to sql

Examples related to ms-access