Often times we wish to copy data from one table into another. The methods of doing this where the new table does not exist will be slightly different from the case in which it does exist. Also I will show display the method of doing this in T-SQL and PL/SQL (just because I get confused all the time when I switch databases 
).
SQL copy tables to new table
When performing this command, a table will created using the same data types as the columns which as selected in the select statement. This is usually very useful when making a copy of the existing table before making any alterations to the data.
T-SQL:
|
1 2 3 |
SELECT * INTO Sales.CustomerBackup FROM Sales.Customer; |
PL/SQL:
|
1 2 3 |
CREATE TABLE CustomerBackup AS SELECT * FROM Customer; |
SQL copy tables to existing table
These following when executed will insert data into already existing table. It will match fields based on name if you choose to list the fields in the select clause. This normally useful for when you need to reinsert data (don’t ask why… )
T-SQL:
|
1 2 |
INSERT INTO Sales.CustomerBackup (PersonID, StoreID, ModifiedDate, AccountNumber, rowguid) SELECT PersonID, StoreID, ModifiedDate, AccountNumber, rowguid FROM Sales.Customer; |
PL/SQL:
|
1 2 |
INSERT INTO CustomerBackup SELECT * FROM Customer; |
