CODEWITHSUNDEEP
sqlsql-serversql-server-2005
SDR
SDR

Reputation: 37

How to create a new table from an existing table?

dbo.Profile_Updated
  (
     BusinessName VARCHAR,
     ContactName  VARCHAR,
     Address      VARCHAR,
     City         VARCHAR,
     State        VARCHAR,
     Postalcode   INT PRIMARY KEY,
     Phonenumber  VARCHAR
  )

This is my current table but now I want to create new table from the existing Profile_Updated table column, I mean the new table (PostalDB) needs to contain the columns City, State, Postalcode based on Profile_Updated table.

Can anyone help me?

Thanks in advance.

Upvotes: 1

Views: 234

Answers (4)

Thomas Pamantung
Thomas Pamantung

Reputation: 1

You can create a new table based on an existing table using the SELECT INTO statement.

SELECT City, State, Postalcode
INTO PostalDB
FROM Profile_Updated

This will create a new table named PostalDB with the columns City, State, and Postalcode, and populate it with the data from the corresponding columns in the Profile_Updated table.

Alternatively, you can use the CREATE TABLE statement with a SELECT statement to achieve the same result:

CREATE TABLE PostalDB
(
    City VARCHAR,
    State VARCHAR,
    Postalcode INT
)

INSERT INTO PostalDB (City, State, Postalcode)
SELECT City, State, Postalcode
FROM Profile_Updated

Note that in the first example, the data types of the columns in the new table will be automatically determined based on the data types of the columns in the original table. In the second example, you need to specify the data types explicitly.

Also, if you want to create the table with the same data types as the original table, you can use the following syntax:

SELECT City, State, Postalcode
INTO PostalDB
FROM Profile_Updated
WITH ( City VARCHAR, State VARCHAR, Postalcode INT )

This will create the table with the specified data types.

Alternatively, you can also use the following syntax:

CREATE TABLE PostalDB
(
    City VARCHAR,
    State VARCHAR,
    Postalcode INT
)

INSERT INTO PostalDB (City, State, Postalcode)
SELECT City, State, Postalcode
FROM Profile_Updated

All of these methods will create a new table named PostalDB with the columns City, State, and Postalcode and populate it with the data from the corresponding columns in the Profile_Updated table.

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20794

You are doing things in the wrong order. You should have the PostalDB table created and populated first with a name like PostalDBId as the primary key. The PostalDBId should be effectively meaningless. It should not be a zip code or anything like that.

Then, your Profile_Updated needs a different primary key, something that's not related to the PostalDB table. You also want another field that is a foreign key to the PostalDb table.

Upvotes: 1

BellevueBob
BellevueBob

Reputation: 9618

Perhaps this will work:

SELECT DISTINCT City, State, Postalcode
INTO dbo.PostalDB
FROM dbo.Profile_Updated

The INTO clause will create a new table using the attributes from the source. The A little web searching will reveal many other possible solutions.

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

I assume you just mean SELECT INTO:

SELECT City, State, Postalcode
  INTO dbo.PostalDB
  FROM dbo.Profile_Updated;

CREATE UNIQUE CLUSTERED INDEX p ON dbo.PostalDB(PostalCode);
CREATE INDEX ... -- you'll need to fill in these details

Upvotes: 3

Related Questions