SQL Query to Copy a Table Structure from another Table
Method 1 : INSERT INTO SELECT
Copying Specific subset of columns into an Existing Table from another Existing Table :
CREATE TABLE SampleTable(PID Int,FirstName VARCHAR(100), LastNameVARCHAR(100))
INSERT INTO SampleTable(PID,FirstName, LastName) SELECT PNo FName, LName FROM Persons.
Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are of same data type, they are are not required to list them. I always list them for readability and scalability purpose.
Copying Specific subset of columns into an Existing Table from another Existing Table :
CREATE TABLE SampleTable(PID Int,FirstName VARCHAR(100), LastNameVARCHAR(100))
INSERT INTO SampleTable(PID,FirstName, LastName) SELECT PNo FName, LName FROM Persons.
Notes :
Here the columns names may different but the data type should be same.
Copying all columns into an Existing Table from another Existing Table :
To do this bothe tables should have same no.of columns and same data type.
INSERT INTO SampleTable SELECT * FROM Persons.
Here the columns names may different but the data type should be same.
Copying all columns into an Existing Table from another Existing Table :
To do this bothe tables should have same no.of columns and same data type.
INSERT INTO SampleTable SELECT * FROM Persons.
Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
The easiest way to create a copy of a table is to use a
Transact-SQL command SELECT INTO to extract all the rows from an existing
table into the new table. The new table must not exist already. The following
example will copy the Customers table under the Sales Schema
to a new table called NewCustomers under the TPR Schema:
We can create a Table from an existing Table by copying the existing Table's Columns.
It is important to note that when creating a Table in this way, the New Table will be populated with the records from the existing Table (based on the SQL SELECT Statement).
SELECT * INTO TPR.NewCustomers FROM Sales.Customers;
Copying Specific subset of columns from Existing Table to New Table
You can also create the new table from a specific subset of
columns in the original table. In this case, you specify the names of the
columns to copy after the SELECT keyword. Any columns not specified are
excluded from the new table. The following example copies specific columns to a
new table:
SELECT CustName, Address, Telephone, Email INTO TPR.NewCustomers
FROM Sales.Customers;
Copying only Table Structure from Existing Table to New Table :
Creating a duplicate table with only the structure duplicated with a new name as follows :
SELECT * INTO TPR.NewCustomers FROM Sales.Customers where 1=2;
Oracle/PL-SQL :
Copying all Columns from another Table
The
syntax for CREATING a Table by copying all Columns from another Table is:
CREATE TABLE New_Table
AS (SELECT *
FROM Old_Table);
For
Example:
CREATE TABLE Vendors
AS (SELECT *
FROM Customers
WHERE id
> 1000);
This
would create a New Table called Vendors that included all Columns from the Customers Table.
If
there were records in the Customers Table, then the New Vendors Table would also contain the records
selected by the SELECT statement.
Copying selected Columns from another Table
The
syntax for CREATING a Table by copying selected Columns from another Table is:
CREATE TABLE New_Table
AS (SELECT Column_1,
Column2, ... Column_n FROM Old_Table);
For
Example:
CREATE TABLE Vendors
AS (SELECT
id, address, city, state, zip
FROM Customers
WHERE id
> 1000);
This
would create a New Table called Vendors, but the New Table would only include the specified Columns from
the Customers Table.
Again,
if there were records in the Customers Table, then the New Vendors Table would also contain the records
selected by the SELECT statement.
Copying selected Columns from multiple Tables
The
syntax for CREATING a Table by copying selected Columns from multiple Tables
is:
CREATE TABLE New_Table
AS (SELECT Column_1,
Column2, ... Column_n
FROM Old_Table_1,
Old_Table_2, ... Old_Table_n);
For
Example:
CREATE TABLE Vendors
AS (SELECT Customers.id,
Customers.address, Categories.cat_type
FROM Customers,
Categories
WHERE Customers.id = Categories.id
AND Customers.id
> 1000);
This
would create a New Table called Vendors based on Columns from both the Customers and Categories Tables.
F A Qs :
Question: How can I create an SQL Table from another Table without copying any values from the Old Table?
Question: How can I create an SQL Table from another Table without copying any values from the Old Table?
Answer: To do this, the syntax is:
CREATE TABLE New_Table
AS (SELECT *
FROM Old_Table WHERE 1=2);
For
Example:
CREATE TABLE Vendors
AS (SELECT *
FROM Customers WHERE 1=2);
This
would create a New Table called Vendors that included all Columns from the Customers Table, but no data from the CustomersTable