SQL Inserting into a table with data from another table
This is one that I use a great deal. It is useful when building a scratch table to analyze data, for example.
In this case, a table is created, and then populated with data from another table, or tables. The mechanism for this is the "insert select" of SQL.
This is an easy method to use, once you understand the principle. The "insert" statement is used, but instead of using the "value" clause, a select statement is used to supply the data. The data generated by the select clause, is inserted into the table, based on the position. The first column selected, goes into the first column of the table being inserted to, and so on.
So, if let us assume that we have TABLE1, with columns FIELD1, FIELD2, and FIELD3. Then, say we want to build another table, TABLE2, with just FIELDA and FIELDB, and populate that with data from FIELD1 and FIELD3 from TABLE1. For this example, I will assume all fields are of type varchar, with a length of 20.
First we would create the new table (TABLE2), then populate it with the data from TABLE1, by using "insert select".
CREATE TABLE2 (FIELDA varchar(20), FIELDB varchar(20));
INSERT INTO TABLE2 SELECT FIELD1,FIELD3 from TABLE1;
It is that simple. Since no fields were specified in the insert, all fields are assumed. So, data is inserted into both FIELDA and FIELDB. This could also have been written as "INSERT INTO TABLE2 (FIELDA,FIELDB)".
The data inserted is positional, so since FIELD1 from TABLE1 is the first column in the select statement, it ends up in FILEDA of TABLE2. If you wanted to reverse the order of the data in TABLE2, it can be done in two ways.
You could reverse the tables in the select statement:
INSERT INTO TABLE2 (FIELDA,FIELDB) SELECT FIELD3,FIELD1 from TABLE1;
Or, you could reverse the fields specified in the insert statement:
INSERT INTO TABLE2 (FIELDB,FIELDA) SELECT FIELD1,FIELD3 from TABLE1;
Finally, if you only wanted to initially populate FIELDA of TABLE2, then only that column would be specified, and only on field in the select statement would be used:
INSERT INTO TABLE2 (FIELDA) SELECT FIELD3 from TABLE1;
In this case, FIELDB would be null for every row.
This is a very basic example. In my next blog, I will use this principle, and introduce updating information based on a tables content. That is something that I use frequently.
Contact CCS Retail Systems for help with your SQL systems and needs for customization.
Dave.