sql - 使用其他参数从另一个表创建表

Question

我想从表“ProjectTable”创建一个表“Customer”:

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

我需要在这里添加更多内容，因为附加列“id”将是一个主键列，或者“客户”将是对另一个表列的引用。如何做到这一点？

Answer 1

您可以创建具有与 ProjectTable 相同的表结构和相同数据的新表，然后您可以添加所需的额外字段:

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)