How to Create Structure for a Table Using Existing Table

How to Create Structure for a Table Using Existing Table

How to create structure for a table using existing table ?

You can use SELECT INTO command to create a new table using an existing database table.

Syntax: SELECT * INTO New_Table FROM Old_Table

Example: Lets assume i have database Test1 and a database table tbl_Employee with below structure.

CREATE TABLE Test1.dbo.tbl_Employee

(

ID BIGINT IDENTITY(1,1) PRIMARY KEY,

FirstName VARCHAR(10) NOT NULL,

LastName VARCHAR(100),

EmployeeEmail VARCHAR(200) UNIQUE

)

GO

I want to create a new table tbl_Employee_1 in the same database using tbl_Employee then you can use below command to create copy of existing database table.

USE Test1

GO

SELECT * INTO tbl_Employee_1 FROM Test1.dbo.tbl_Employee

GO

Note: The above query is going to create only structure. It is not going to create constraints, Keys, Indexes and etc...

You can create copy of same table in different database (Test2) using below command

SELECT * INTO Test2.dbo.tbl_Employee FROM Test1.dbo.tbl_Employee

GO

If you do not want to copy data in new table provide a flase where clause in your query. Below query is going to create a new database table without any data.

SELECT * INTO Test2.dbo.tbl_Employee_1 FROM Test1.dbo.tbl_Employee

WHERE 1=2

GO

You can also mentioned the columns you want to add in new database table. Below two queries going to create a new database table with the columns mentioned in the select clause.

SELECT FirstName,LastName INTO Test2.dbo.tbl_Employee_2 FROM Test1.dbo.tbl_Employee

GO

SELECT FirstName+ ISNULL(' '+LastName,'') AS FullName

INTO Test2.dbo.tbl_Employee_3 FROM Test1.dbo.tbl_Employee

WHERE 1=1

GO

0 Comments
Leave A Comment

Please login to post your valuable comments.

Join the newsletter

Get the latest vLemonn news first

share