Create a new table and adding a data from old one in SQL Server 2017 in one query

Question

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.

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'

Answer 1

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 

Source