How to Add a Block or Rows to a SQL Table

By Allen G. Taylor

Loading a SQL database table one row at a time by using INSERT statements can be tedious, particularly if that’s all you do. Even entering the data into a carefully human-engineered ergonomic screen form gets tiring after a while. Clearly, if you have a reliable way to enter the data automatically, you’ll find occasions in which automatic entry is better than having a person sit at a keyboard and type.

Automatic data entry is feasible, for example, if the data exists in electronic form because somebody has already entered the data manually. If so, transferring data from one data file to another is a task that a computer can perform with minimal human involvement. If you know the characteristics of the source data and the desired form of the destination table, a computer can perform the data transfer automatically.

How to copy from a foreign data file

Suppose you’re building a database for a new application. Some data that you need already exists in a computer file. The file may be a flat file or a table in a database created by a DBMS different from the one you use. The data may be in ASCII or EBCDIC code or in some arcane proprietary format. What do you do?

The first things you do are hope and pray that the data you want is in a widely used format. If the data is in a popular format, you have a good chance of finding a format-conversion utility that can translate the data into one or more other popular formats.

Your development environment can probably import one of these formats; if you’re really lucky, your development environment can handle the current data format directly. On personal computers, the Access, xBASE, and MySQL formats are the most widely used. If the data you want is in one of these formats, conversion should be easy. If the format is less common, you may have to put it through a two-step conversion.

If the data is in an old, proprietary, or defunct format, as a last resort, you can turn to a professional data-translation service. These businesses specialize in translating computer data from one format to another.

How to transfer all rows between tables

A less severe problem than dealing with foreign data is taking data that already exists in one table combining that data with compatible data in another table. This process works great if the structure of the second table is identical to the structure of the first table.

In that case, you can combine the contents of the two tables by using the UNION relational operator. The result is a virtual table (that is, one that has no independent existence) that contains data from both source tables.

How to transfer selected columns and rows between tables

Generally, the structure of the data in the source table isn’t identical to the structure of the table into which you want to insert the data. Perhaps only some of the columns match, and these are the columns that you want to transfer. By combining SELECT statements with a UNION, you can specify which columns from the source tables to include in the virtual result table.

By including WHERE clauses in the SELECT statements, you can restrict the rows that you place into the result table to those that satisfy specific conditions.

Suppose that you have two tables, PROSPECT and CUSTOMER, and you want to list everyone living in the state of Maine who appears in either table. You can create a virtual result table that contains the desired information; just use the following command:

SELECT FirstName, LastName
  FROM PROSPECT
  WHERE State = 'ME'
UNION
SELECT FirstName, LastName
  FROM CUSTOMER
  WHERE State = 'ME' ;

Here’s a closer look:

  • The SELECT statements specify that the columns included in the result table are FirstName and LastName.

  • The WHERE clauses restrict the rows included to those with the value ‘ME’ in the State column.

  • The State column isn’t included in the results table but is present in both the PROSPECT and CUSTOMER tables.

  • The UNION operator combines the results of the SELECT statement on PROSPECT with the results of the SELECT on CUSTOMER, deletes any duplicate rows, and then displays the result.

Another way to copy data from one table in a database to another is to nest a SELECT statement within an INSERT statement. This method doesn’t create a virtual table; instead, it duplicates the selected data. You can take all the rows from the CUSTOMER table, for example, and insert those rows into the PROSPECT table.

Of course, this works only if the structures of the CUSTOMER and PROSPECT tables are identical. If you want to place only those customers who live in Maine into the PROSPECT table, a simple SELECT with one condition in the WHERE clause does the trick, as shown in the following example:

INSERT INTO PROSPECT
 SELECT * FROM CUSTOMER
 WHERE State = 'ME' ;

Even though this operation creates redundant data, you may want to do it anyway to improve the performance of retrievals. Beware of the redundancy, however! To maintain data consistency, make sure that you don’t insert, update, or delete rows in one table without inserting, updating, or deleting the corresponding rows in the other table. Another potential problem is the possibility that the INSERT statement might generate duplicate primary keys.

If even one pre-existing prospect has a primary key of ProspectID that matches the corresponding primary key (CustomerID) of a customer you’re trying to insert into the PROSPECT table, the insert operation will fail. If both tables have autoincrementing primary keys, you don’t want them to start with the same number. Make sure the two blocks of numbers are far apart from each other.