如何将一个表列值保存到另一个表值？ [重复]

This question already has an answer here:

这个问题在这里已有答案:

• How to create a new table from an existing table? 3 answers

如何从现有表创建新表? 3个答案

I have 2 tables Table 1 = Profile_Update Table 2 = PostalDB

我有2个表表1 = Profile_Update表2 = PostalDB

Here Table 1 column(BusinessName, ContactName, City, State, Postalcode{PrimaryKey}, etc..) values wants to store Table 2(City, State, Postalcode{PrimaryKey}) column also. How can I save one table column values to another table values.

这里的表1列(BusinessName,ContactName,City,State,Postalcode {PrimaryKey}等)值也要存储表2(City,State,Postalcode {PrimaryKey})列。如何将一个表列值保存到另一个表值。

2 个解决方案

#1

Ignoring that having a primary key on postal code is not smart (since in some cases multiple cities can share a postal code, and surely you don't want to allow only one user from any postal code), maybe what you are looking for is this:

忽略在邮政编码上使用主键是不聪明的(因为在某些情况下,多个城市可以共享邮政编码,当然你不希望只允许一个用户使用任何邮政编码),也许你正在寻找的是这个:

UPDATE p SET city = pu.city, state = pu.state
  FROM dbo.PostalDB AS p
  INNER JOIN dbo.Profile_Update AS pu
  ON p.Postalcode = pu.Postalcode;

INSERT dbo.PostalDB(city, state, Postalcode)
  SELECT city, state, postalcode
  FROM dbo.Profile_Update AS pu
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.PostalDB
      WHERE Postalcode = pu.Postalcode
  );

#2

Use

SELECT ... INTO 

when you are creating a new table.

当您创建新表时。

Otherwise

Insert INTO table (columns...)   (SELECT ... FROM ... where ...)