HWA1_P6_1.sql

/* -------------------------------------------------------------------------
   Yu Cheng
   ICS 321 Assignment 1
   September 18, 2008

   PART 6.1
   The COMPUTER table will satisfy this requirement. Import the computer
   data into your database. Rename the imported table attributes to your
   personal preference. Rename the imported table name to COMPUTER. Modify
   the attributes, if required, to comply with the definitions in the
   Metadata Dictionary documentation.
   ------------------------------------------------------------------------- */

USE DB9975
GO

CREATE TABLE COMPUTER
(
	Id           NVARCHAR(15) NOT NULL,
	Make         NVARCHAR(15) NOT NULL,
	Model        NVARCHAR(20) NOT NULL,
	DiskCapacity NVARCHAR(10) NOT NULL

	CONSTRAINT PK_COMPUTER PRIMARY KEY (Id)
)
GO

-- NOTE: The XLS file does not use a heading for the columns, and so these
-- columns are automatically renamed F1, F2, F3, and F4 by (I assume) SQL
-- server.  I rename them here for clarity.

INSERT INTO COMPUTER
	SELECT * FROM OPENROWSET
	(
		'Microsoft.Jet.OLEDB.4.0',
		'Excel 8.0;Database=T:\COMPUTER_Data.xls;HDR=NO',
		'SELECT
			F1 AS Id,
			F2 AS Make,
			F3 AS Model,
			F4 AS DiskCapacity
			FROM [Sheet1$]'
	)
GO