Create table from another one with additional parameters

Question

I want to create a table 'Customer' from table 'ProjectTable':

CREATE TABLE Customer AS
    (
        SELECT CustCompanyName, CustContactName, CustContactTitle, CustAddress, CustCity, CustRegion, CustPostalCode, CustCountry, CustPhone, CustFax FROM ProjectTable
    );

And I need to add here something more, as additional column 'id' with would be a primary key column, or 'customer' with will be a reference to another table column. How to do that?

Answer 1

You can just create your new table with the same table structure and with the same data that ProjectTable has and then you can add the additional fields you need:

SELECT CustCompanyName, CustContactName, CustContactTitle, CustAddress, CustCity, CustRegion, 
CustPostalCode, CustCountry, CustPhone, CustFax INTO Customer FROM ProjectTable

ALTER TABLE Customer ADD ID INT IDENTITY(1,1) PRIMARY KEY

ALTER TABLE Customer ADD customer VARCHAR(10)

ALTER TABLE Customer ADD CONSTRAINT Customer_customer_FK FOREIGN KEY ( customer ) REFERENCES MyOtherTable(PKColumn)