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.

回答1:

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

回答2:

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.

回答3:

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.