Qualify your scripts when using INSERT and SELECT v7.0
When writing SQL statements or using stored procedures to insert data, many
developers use the INSERT / SELECT method to move data from one table to
another. There are two ways to accomplish this data movement.
�
An INSERT with an unqualified SELECT can be a timesaver but can also cause
problems downstream. For example:
�
INSERT INTO TableX
SELECT * FROM TableY
�
This statement looks harmless enough, but look deeper, and you'll see the
pitfall. If you use this syntax to transport data, the Table X and TableY must
have the exact same columns in precisely the same sequence to ensure that data
integrity is preserved. A downstream problem would arise if one of the tables
was altered in some way, such as a new column was added or an existing column
was deleted. Given this scenario, the above statement�located in a stored
procedure or script�would now be useless since the columns are now out of
sync.
�
A bit more time is necessary to qualify your inserts and selects against such
differences, but it can save you endless headaches downstream. As you can see
from the code below, a great deal more effort isn't required.
�
INSERT INTO TableX (Col1, Col2, Col3)
SELECT Col1, Col2, Col3 FROM TableY
�
Now the tables are not dependent upon each other to maintain homogeneity in
column count and sequence. You can actually ALTER either table by adding or
deleting a column, and this statement will still function. Of course, such
alterations are harmless only insomuch as one of the columns you delete is not
referenced by name in the stored transport statement.
�
Note that the transport statement can also be written to be independent of
sequence order when the column names are qualified. All that matters is that the
SELECT order and the INSERT order are identical.
�
INSERT INTO TableX (Col3, Col1, Col2)
SELECT Col3, Col1, Col2 FROM TableY
Home��Previous��Next