创建一个新表并在一个查询中添加 SQL Server 2017 中旧表的数据 [英] Create a new table and adding a data from old one in SQL Server 2017 in one query

问题描述

我有一个包含瑞士所有城市和地区的主表.

I have a main table that contains all cities and regions in Switzerland.

我想为每个城市都建一张表，把城市数据直接从主复制到新的，按照它的缩写.

I want to create a table for each city, and copy the city data directly from the main to the new one according to its abbreviation.

例如，为Zurich创建一个表，当缩写为ZH时，从旧表中复制所有数据.为沙夫豪森创建一个表，当缩写为 SH 时，从旧表中复制所有数据.

For example, create a table for Zurich and copy all data from the old table when the abbreviation is ZH. create a table for Schaffhausen and copy all data from the old table when the abbreviation is SH.

我使用了以下查询，但没有奏效:

I've used the following query, but did not work:

CREATE TABLE Zurich AS SELECT * FROM [dbo].[Post Code]
WHERE abbreviation = 'ZH'

推荐答案

谢谢大家，我找到了解决方案.

Thanks guys, I found the solution.

USE [Post Code];  
GO  
CREATE TABLE dbo.Zurich  
( PostCode   int,  
  Place nvarchar(25),
  Canton nvarchar(50)  ,
  Abbreviation nvarchar(2),
  Country nvarchar(11)
);  
GO  
INSERT INTO dbo.Zurich  
    SELECT *   
    FROM [dbo].[Post Code] WHERE Abbreviation = 'ZH';  
GO 

来源

这篇关于创建一个新表并在一个查询中添加 SQL Server 2017 中旧表的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！