匯出 (0) 列印
全部展開
10 人當中有 5 人評分為有幫助- 為這個主題評分

如何:使用產生指令碼精靈移轉資料庫 (Windows Azure SQL Database)

當您將應用程式部署至 Microsoft Windows Azure SQL Database 時,可能需要從內部部署 SQL Server 執行個體將資料庫移轉至 SQL Database。本主題說明如何使用 Transact-SQL 指令碼將簡單的資料庫移轉至 SQL Database。 

Windows Azure SQL Database 支援 Transact-SQL 語言的子集。您必須將產生的指令碼修改為僅包含支援的 Transact-SQL 陳述式,才能將資料庫部署至 SQL Database。如需詳細資訊,請參閱<一般方針和限制 (Windows Azure SQL Database)>和<Transact-SQL 參考 (Windows Azure SQL Database)>。

建立 School 範例資料庫

  1. 在 SQL Server Management Studio 中的 [檔案] 功能表上指向 [新增],然後按一下 [Database Engine 查詢]

  2. [連接到 Database Engine] 對話方塊中,輸入本機 SQL Server 執行個體的資訊,然後按一下 [連接]

  3. 在查詢視窗中貼入本主題結尾的 Transact-SQL 指令碼,然後按一下 [執行]

建立 Transact-SQL 指令碼

  1. [物件總管] 中,以滑鼠右鍵按一下 [School] 資料庫,指向 [工作],然後選取 [產生指令碼]

  2. [產生和發佈指令碼精靈] 對話方塊中,按 [下一步] 移至 [選擇物件] 步驟。選取 [編寫整個資料庫和所有資料庫物件的指令碼],然後按 [下一步]

  3. [設定指令碼編寫選項] 中,設定下列選項:

    • [輸出類型] 設定為 [將指令碼儲存至特定位置]。選取 [儲存至檔案]。按一下 [單一檔案]。在 [檔案名稱] 中輸入檔案名稱和位置。按一下 [進階]

    • [進階編寫指令碼選項] 中,將 [適用於 Database Engine 類型的指令碼] 選項設定為 [SQL Database],將 [將 UDDT 轉換為基底類型] 選項設定為 [True],並且將 [要編寫指令碼的資料類型] 選項設定為 [結構描述和資料]。按一下 [確定]。

  4. [下一步],再按 [下一步],然後按一下 [完成]

在 SQL Database 上執行指令碼

  1. 透過 Windows Azure 平台管理入口網站或藉由執行下列 Transact-SQL 命令,在 SQL Database 伺服器上建立 School 資料庫:

    CREATE DATABASE School
    
  2. 在 SQL Server Management Studio 中開啟 [查詢] 功能表,指向 [連接],然後選取 [變更連接]

  3. 輸入 SQL Database 伺服器名稱和您的認證,然後按一下 [選項 >>]

  4. [連接屬性] 索引標籤的 [連接資料庫] 下拉式功能表中輸入 School,然後按一下 [連接]

  5. 以滑鼠右鍵按一下 SQL Database 中的 [School] 資料庫,然後選取 [新增查詢]

  6. 在 SQL Server Management Studio 中的 [檔案] 功能表上,指向 [開啟],然後按一下 [檔案]。在 [開啟檔案] 視窗中,從您稍早在 [產生和發佈指令碼精靈] 中指定的位置開啟 School 指令碼檔案。

  7. 按 F5 執行指令碼。

School 資料庫 Transact-SQL 定義

  • 執行這個指令碼,在本機 SQL Server 執行個體中建立 School 資料庫。

    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    USE [master];
    GO
    
    IF EXISTS (SELECT * FROM sys.databases WHERE name = 'School')
    	DROP DATABASE School;
    GO
    
    -- Create the School database.
    CREATE DATABASE School;
    GO
    
    -- Specify a simple recovery model 
    -- to keep the log growth to a minimum.
    ALTER DATABASE School 
    	SET RECOVERY SIMPLE;
    GO
    
    USE School;
    GO
    
    -- Create the Department table.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[Department]') 
    		AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[Department](
    	[DepartmentID] [int] NOT NULL,
    	[Name] [nvarchar](50) NOT NULL,
    	[Budget] [money] NOT NULL,
    	[StartDate] [datetime] NOT NULL,
    	[Administrator] [int] NULL,
     CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
    (
    	[DepartmentID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    
    -- Create the Person table.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[Person]') 
    		AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[Person](
    	[PersonID] [int] IDENTITY(1,1) NOT NULL,
    	[LastName] [nvarchar](50) NOT NULL,
    	[FirstName] [nvarchar](50) NOT NULL,
    	[HireDate] [datetime] NULL,
    	[EnrollmentDate] [datetime] NULL,
     CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED 
    (
    	[PersonID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    
    -- Create the OnsiteCourse table.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]') 
    		AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[OnsiteCourse](
    	[CourseID] [int] NOT NULL,
    	[Location] [nvarchar](50) NOT NULL,
    	[Days] [nvarchar](50) NOT NULL,
    	[Time] [smalldatetime] NOT NULL,
     CONSTRAINT [PK_OnsiteCourse] PRIMARY KEY CLUSTERED 
    (
    	[CourseID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    
    -- Create the OnlineCourse table.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[OnlineCourse]') 
    		AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[OnlineCourse](
    	[CourseID] [int] NOT NULL,
    	[URL] [nvarchar](100) NOT NULL,
     CONSTRAINT [PK_OnlineCourse] PRIMARY KEY CLUSTERED 
    (
    	[CourseID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    
    --Create the StudentGrade table.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[StudentGrade]') 
    		AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[StudentGrade](
    	[EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
    	[CourseID] [int] NOT NULL,
    	[StudentID] [int] NOT NULL,
    	[Grade] [decimal](3, 2) NULL,
     CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED 
    (
    	[EnrollmentID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    
    -- Create the CourseInstructor table.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[CourseInstructor]') 
    		AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[CourseInstructor](
    	[CourseID] [int] NOT NULL,
    	[PersonID] [int] NOT NULL,
     CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED 
    (
    	[CourseID] ASC,
    	[PersonID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    
    -- Create the Course table.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[Course]') 
    		AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[Course](
    	[CourseID] [int] NOT NULL,
    	[Title] [nvarchar](100) NOT NULL,
    	[Credits] [int] NOT NULL,
    	[DepartmentID] [int] NOT NULL,
     CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED 
    (
    	[CourseID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    
    -- Create the OfficeAssignment table.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]')
    		AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[OfficeAssignment](
    	[InstructorID] [int] NOT NULL,
    	[Location] [nvarchar](50) NOT NULL,
    	[Timestamp] [timestamp] NOT NULL,
     CONSTRAINT [PK_OfficeAssignment] PRIMARY KEY CLUSTERED 
    (
    	[InstructorID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    
    -- Define the relationship between OnsiteCourse and Course.
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
           WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnsiteCourse_Course]')
           AND parent_object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]'))
    ALTER TABLE [dbo].[OnsiteCourse]  WITH CHECK ADD  
           CONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY([CourseID])
    REFERENCES [dbo].[Course] ([CourseID])
    GO
    ALTER TABLE [dbo].[OnsiteCourse] CHECK 
           CONSTRAINT [FK_OnsiteCourse_Course]
    GO
    
    -- Define the relationship between OnlineCourse and Course.
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
           WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnlineCourse_Course]')
           AND parent_object_id = OBJECT_ID(N'[dbo].[OnlineCourse]'))
    ALTER TABLE [dbo].[OnlineCourse]  WITH CHECK ADD  
           CONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY([CourseID])
    REFERENCES [dbo].[Course] ([CourseID])
    GO
    ALTER TABLE [dbo].[OnlineCourse] CHECK 
           CONSTRAINT [FK_OnlineCourse_Course]
    GO
    
    -- Define the relationship between StudentGrade and Course.
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
           WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Course]')
           AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]'))
    ALTER TABLE [dbo].[StudentGrade]  WITH CHECK ADD  
           CONSTRAINT [FK_StudentGrade_Course] FOREIGN KEY([CourseID])
    REFERENCES [dbo].[Course] ([CourseID])
    GO
    ALTER TABLE [dbo].[StudentGrade] CHECK 
           CONSTRAINT [FK_StudentGrade_Course]
    GO
    
    --Define the relationship between StudentGrade and Student.
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
           WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Student]')
           AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]'))
    ALTER TABLE [dbo].[StudentGrade]  WITH CHECK ADD  
           CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID])
    REFERENCES [dbo].[Person] ([PersonID])
    GO
    ALTER TABLE [dbo].[StudentGrade] CHECK 
           CONSTRAINT [FK_StudentGrade_Student]
    GO
    
    -- Define the relationship between CourseInstructor and Course.
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
       WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]')
       AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))
    ALTER TABLE [dbo].[CourseInstructor]  WITH CHECK ADD  
       CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
    REFERENCES [dbo].[Course] ([CourseID])
    GO
    ALTER TABLE [dbo].[CourseInstructor] CHECK 
       CONSTRAINT [FK_CourseInstructor_Course]
    GO
    
    -- Define the relationship between CourseInstructor and Person.
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
       WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Person]')
       AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))
    ALTER TABLE [dbo].[CourseInstructor]  WITH CHECK ADD  
       CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID])
    REFERENCES [dbo].[Person] ([PersonID])
    GO
    ALTER TABLE [dbo].[CourseInstructor] CHECK 
       CONSTRAINT [FK_CourseInstructor_Person]
    GO
    
    -- Define the relationship between Course and Department.
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
           WHERE object_id = OBJECT_ID(N'[dbo].[FK_Course_Department]')
           AND parent_object_id = OBJECT_ID(N'[dbo].[Course]'))
    ALTER TABLE [dbo].[Course]  WITH CHECK ADD  
           CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
    REFERENCES [dbo].[Department] ([DepartmentID])
    GO
    ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
    GO
    
    --Define the relationship between OfficeAssignment and Person.
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys 
       WHERE object_id = OBJECT_ID(N'[dbo].[FK_OfficeAssignment_Person]')
       AND parent_object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]'))
    ALTER TABLE [dbo].[OfficeAssignment]  WITH CHECK ADD  
       CONSTRAINT [FK_OfficeAssignment_Person] FOREIGN KEY([InstructorID])
    REFERENCES [dbo].[Person] ([PersonID])
    GO
    ALTER TABLE [dbo].[OfficeAssignment] CHECK 
       CONSTRAINT [FK_OfficeAssignment_Person]
    GO
    
    -- Create InsertOfficeAssignment stored procedure.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[InsertOfficeAssignment]') 
    		AND type in (N'P', N'PC'))
    BEGIN
    EXEC dbo.sp_executesql @statement = N'
    CREATE PROCEDURE [dbo].[InsertOfficeAssignment]
    		@InstructorID int,
    		@Location nvarchar(50)
    		AS
    		INSERT INTO dbo.OfficeAssignment (InstructorID, Location)
    		VALUES (@InstructorID, @Location);
    		IF @@ROWCOUNT > 0
    		BEGIN
    			SELECT [Timestamp] FROM OfficeAssignment 
    				WHERE InstructorID=@InstructorID;
    		END
    ' 
    END
    GO
    
    --Create the UpdateOfficeAssignment stored procedure.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[UpdateOfficeAssignment]') 
    		AND type in (N'P', N'PC'))
    BEGIN
    EXEC dbo.sp_executesql @statement = N'
    CREATE PROCEDURE [dbo].[UpdateOfficeAssignment]
    		@InstructorID int,
    		@Location nvarchar(50),
    		@OrigTimestamp timestamp
    		AS
    		UPDATE OfficeAssignment SET Location=@Location 
    		WHERE InstructorID=@InstructorID AND [Timestamp]=@OrigTimestamp;
    		IF @@ROWCOUNT > 0
    		BEGIN
    			SELECT [Timestamp] FROM OfficeAssignment 
    				WHERE InstructorID=@InstructorID;
    		END
    ' 
    END
    GO
    
    -- Create the DeleteOfficeAssignment stored procedure.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[DeleteOfficeAssignment]') 
    		AND type in (N'P', N'PC'))
    BEGIN
    EXEC dbo.sp_executesql @statement = N'
    CREATE PROCEDURE [dbo].[DeleteOfficeAssignment]
    		@InstructorID int
    		AS
    		DELETE FROM OfficeAssignment
    		WHERE InstructorID=@InstructorID;
    ' 
    END
    GO
    
    -- Create the DeletePerson stored procedure.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]') 
    		AND type in (N'P', N'PC'))
    BEGIN
    EXEC dbo.sp_executesql @statement = N'
    CREATE PROCEDURE [dbo].[DeletePerson]
    		@PersonID int
    		AS
    		DELETE FROM Person WHERE PersonID = @PersonID;
    ' 
    END
    GO
    
    -- Create the UpdatePerson stored procedure.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]') 
    		AND type in (N'P', N'PC'))
    BEGIN
    EXEC dbo.sp_executesql @statement = N'
    CREATE PROCEDURE [dbo].[UpdatePerson]
    		@PersonID int,
    		@LastName nvarchar(50),
    		@FirstName nvarchar(50),
    		@HireDate datetime,
    		@EnrollmentDate datetime
    		AS
    		UPDATE Person SET LastName=@LastName, 
    				FirstName=@FirstName,
    				HireDate=@HireDate,
    				EnrollmentDate=@EnrollmentDate
    		WHERE PersonID=@PersonID;
    ' 
    END
    GO
    
    -- Create the InsertPerson stored procedure.
    IF NOT EXISTS (SELECT * FROM sys.objects 
    		WHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]') 
    		AND type in (N'P', N'PC'))
    BEGIN
    EXEC dbo.sp_executesql @statement = N'
    CREATE PROCEDURE [dbo].[InsertPerson]
    		@LastName nvarchar(50),
    		@FirstName nvarchar(50),
    		@HireDate datetime,
    		@EnrollmentDate datetime
    		AS
    		INSERT INTO dbo.Person (LastName, 
    					FirstName, 
    					HireDate, 
    					EnrollmentDate)
    		VALUES (@LastName, 
    			@FirstName, 
    			@HireDate, 
    			@EnrollmentDate);
    		SELECT SCOPE_IDENTITY() as NewPersonID;
    ' 
    END
    GO
    
    -- Create GetStudentGrades stored procedure.
    IF NOT EXISTS (SELECT * FROM sys.objects 
                WHERE object_id = OBJECT_ID(N'[dbo].[GetStudentGrades]') 
                AND type in (N'P', N'PC'))
    BEGIN
    EXEC dbo.sp_executesql @statement = N'
    CREATE PROCEDURE [dbo].[GetStudentGrades]
                @StudentID int
                AS
                SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade
                WHERE StudentID = @StudentID
    ' 
    END
    GO
    
    -- Insert data into the Person table.
    USE School
    GO
    SET IDENTITY_INSERT dbo.Person ON
    GO
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (1, 'Abercrombie', 'Kim', '1995-03-11', null);
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (2, 'Barzdukas', 'Gytis', null, '2005-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (3, 'Justice', 'Peggy', null, '2001-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (4, 'Fakhouri', 'Fadi', '2002-08-06', null);
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (5, 'Harui', 'Roger', '1998-07-01', null);
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (6, 'Li', 'Yan', null, '2002-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (7, 'Norman', 'Laura', null, '2003-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (8, 'Olivotto', 'Nino', null, '2005-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (9, 'Tang', 'Wayne', null, '2005-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (10, 'Alonso', 'Meredith', null, '2002-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (11, 'Lopez', 'Sophia', null, '2004-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (12, 'Browning', 'Meredith', null, '2000-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (13, 'Anand', 'Arturo', null, '2003-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (14, 'Walker', 'Alexandra', null, '2000-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (15, 'Powell', 'Carson', null, '2004-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (16, 'Jai', 'Damien', null, '2001-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (17, 'Carlson', 'Robyn', null, '2005-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (18, 'Zheng', 'Roger', '2004-02-12', null);
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (19, 'Bryant', 'Carson', null, '2001-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (20, 'Suarez', 'Robyn', null, '2004-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (21, 'Holt', 'Roger', null, '2004-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (22, 'Alexander', 'Carson', null, '2005-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (23, 'Morgan', 'Isaiah', null, '2001-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (24, 'Martin', 'Randall', null, '2005-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (25, 'Kapoor', 'Candace', '2001-01-15', null);
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (26, 'Rogers', 'Cody', null, '2002-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (27, 'Serrano', 'Stacy', '1999-06-01', null);
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (28, 'White', 'Anthony', null, '2001-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (29, 'Griffin', 'Rachel', null, '2004-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (30, 'Shan', 'Alicia', null, '2003-09-01');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (31, 'Stewart', 'Jasmine', '1997-10-12', null);
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (32, 'Xu', 'Kristen', '2001-7-23', null);
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (33, 'Gao', 'Erica', null, '2003-01-30');
    INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
    VALUES (34, 'Van Houten', 'Roger', '2000-12-07', null);
    GO
    SET IDENTITY_INSERT dbo.Person OFF
    GO
    
    -- Insert data into the Department table.
    INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
    VALUES (1, 'Engineering', 350000.00, '2007-09-01', 2);
    INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
    VALUES (2, 'English', 120000.00, '2007-09-01', 6);
    INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
    VALUES (4, 'Economics', 200000.00, '2007-09-01', 4);
    INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
    VALUES (7, 'Mathematics', 250000.00, '2007-09-01', 3);
    GO
    
    
    -- Insert data into the Course table.
    INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
    VALUES (1050, 'Chemistry', 4, 1);
    INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
    VALUES (1061, 'Physics', 4, 1);
    INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
    VALUES (1045, 'Calculus', 4, 7);
    INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
    VALUES (2030, 'Poetry', 2, 2);
    INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
    VALUES (2021, 'Composition', 3, 2);
    INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
    VALUES (2042, 'Literature', 4, 2);
    INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
    VALUES (4022, 'Microeconomics', 3, 4);
    INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
    VALUES (4041, 'Macroeconomics', 3, 4);
    INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
    VALUES (4061, 'Quantitative', 2, 4);
    INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
    VALUES (3141, 'Trigonometry', 4, 7);
    GO
    
    -- Insert data into the OnlineCourse table.
    INSERT INTO dbo.OnlineCourse (CourseID, URL)
    VALUES (2030, 'http://www.fineartschool.net/Poetry');
    INSERT INTO dbo.OnlineCourse (CourseID, URL)
    VALUES (2021, 'http://www.fineartschool.net/Composition');
    INSERT INTO dbo.OnlineCourse (CourseID, URL)
    VALUES (4041, 'http://www.fineartschool.net/Macroeconomics');
    INSERT INTO dbo.OnlineCourse (CourseID, URL)
    VALUES (3141, 'http://www.fineartschool.net/Trigonometry');
    
    --Insert data into OnsiteCourse table.
    INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
    VALUES (1050, '123 Smith', 'MTWH', '11:30');
    INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
    VALUES (1061, '234 Smith', 'TWHF', '13:15');
    INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
    VALUES (1045, '121 Smith','MWHF', '15:30');
    INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
    VALUES (4061, '22 Williams', 'TH', '11:15');
    INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
    VALUES (2042, '225 Adams', 'MTWH', '11:00');
    INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
    VALUES (4022, '23 Williams', 'MWF', '9:00');
    
    -- Insert data into the CourseInstructor table.
    INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
    VALUES (1050, 1);
    INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
    VALUES (1061, 31);
    INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
    VALUES (1045, 5);
    INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
    VALUES (2030, 4);
    INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
    VALUES (2021, 27);
    INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
    VALUES (2042, 25);
    INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
    VALUES (4022, 18);
    INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
    VALUES (4041, 32);
    INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
    VALUES (4061, 34);
    GO
    
    --Insert data into the OfficeAssignment table.
    INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
    VALUES (1, '17 Smith');
    INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
    VALUES (4, '29 Adams');
    INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
    VALUES (5, '37 Williams');
    INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
    VALUES (18, '143 Smith');
    INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
    VALUES (25, '57 Adams');
    INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
    VALUES (27, '271 Williams');
    INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
    VALUES (31, '131 Smith');
    INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
    VALUES (32, '203 Williams');
    INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
    VALUES (34, '213 Smith');
    
    -- Insert data into the StudentGrade table.
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (2021, 2, 4);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (2030, 2, 3.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (2021, 3, 3);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (2030, 3, 4);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (2021, 6, 2.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (2042, 6, 3.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (2021, 7, 3.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (2042, 7, 4);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (2021, 8, 3);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (2042, 8, 3);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4041, 9, 3.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4041, 10, null);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4041, 11, 2.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4041, 12, null);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4061, 12, null);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4022, 14, 3);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4022, 13, 4);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4061, 13, 4);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4041, 14, 3);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4022, 15, 2.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4022, 16, 2);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4022, 17, null);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4022, 19, 3.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4061, 20, 4);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4061, 21, 2);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4022, 22, 3);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4041, 22, 3.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4061, 22, 2.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (4022, 23, 3);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (1045, 23, 1.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (1061, 24, 4);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (1061, 25, 3);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (1050, 26, 3.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (1061, 26, 3);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (1061, 27, 3);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (1045, 28, 2.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (1050, 28, 3.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (1061, 29, 4);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (1050, 30, 3.5);
    INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
    VALUES (1061, 30, 4);
    GO
    

另請參閱

本文對您有任何幫助嗎?
(剩餘 1500 個字元)
感謝您提供意見

社群新增項目

新增
顯示:
© 2014 Microsoft. 著作權所有,並保留一切權利。