你是Litware,Inc的数据库开发者。你正在修改该公司的sales数据库结构。该

8 查阅

你是Litware,Inc的数据库开发者。你正在修改该公司的sales数据库结构。该数据库将客户信息储存在Customers的表中。该表包含一个字段Country，其储存了客户所在的国家。你又创建一个新表Country。创建Customer和Country表的语法如下所示：CREATE TABLE dbo.Country(CountryID int IDENTITY(1,1) NOT NULL,CountryName char(20) NOT NULL,CONSTRAINT PK_Country PRIMARY KEY CLUSTERED (CountryID))CREATE TABLE dbo.Customers(CustomerID int NOT NULL,CustomerName char(30) NOT NULL,Country char(20) NULL,CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerID))你要尽可能快地将Customer表里头Country的信息转移到新Country表，该选用下面的哪条语句？

INSERT INTO Country (CountryName)SELECT DISTINCT CountryFROM Customers

SELECT (*) AS ColID, c1.CountryINTO CountryFROM (SELECT DISTINCT Country FROM Customers)AS c1,(SELECT DISTINCT Country FROM Customers) AS c2,WHERE c1.Country >=c2.CountryGROUP BY c1.Country ORDER BY 1

DECLARE @Country char (20)DECLARE cursor_country CURSORFOR SELECT Country FROM CustomersOPEN cursor_countryFETCH NEXT FROM cursor_country INTO @CountryWHILE (@@FETCH_STATUS <> -1)BEGINIf NOT EXISTS (SELECT CountryIDFROM CountryWHERE CountryName = @Country)INSERT INTO Country (CountryName) VALUES (@Country)FETCH NEXT FROM cursor_country INTO @CountryENDCLOSE cursor_countryDEALLOCATE cursor_country

DECLARE @SQL varchar (225)SELECT @SQL = ‘bcp “SELECT ColID = COUNT(*), c1. Country’ +‘FROM (SELECT DISTINCT Country FROM Sales..Customers) ASc1, ' +(SELECT DISTINCT Country FROM Sales..Customers) AS c2 '+WHERE c1.Country >= c2.Country’ +‘GROUP BY c1.Country ORDER BY 1’ +‘query out c:\country.txt -c’EXEC master..xp_cmdshell @SQL, no_outputEXEC master..xp_cmdshell ‘bcp Sales..Country in c:\country. Txt-c’, no_output

参考答案:

SQL试