Export (0) Print
Expand All

How to: Migrate a Database by Using the Generate Scripts Wizard (Azure SQL Database)

Updated: May 29, 2012

When you deploy an application to Microsoft Microsoft Azure SQL Database, you may need to migrate a database from an on-premises instance of SQL Server to Azure SQL Database. This topic shows how to migrate a simple database to Azure SQL Database by using Transact-SQL scripts. 

Microsoft Azure SQL Database supports a subset of the Transact-SQL language. You must modify the generated script to only include supported Transact-SQL statements before you deploy the database to Azure SQL Database. For more information, see Azure SQL Database General Guidelines and Limitations and Azure SQL Database Transact-SQL Reference.

Create the School Sample Database

  1. In SQL Server Management Studio, on the File menu, point to New, and then click Database Engine Query.

  2. In the Connect to Database Engine dialog box, enter the information for your local SQL Server instance, and then click Connect.

  3. Paste the Transact-SQL script at the end of this topic in the query window and then click Execute.

Create the Transact-SQL Script

  1. In Object Explorer, right-click the School database, point to Tasks, and select Generate Scripts.

  2. In the Generate and Publish Scripts Wizard dialog box, click Next to get to the Choose Objects step. Select Script entire database and all database objects, and then click Next.

  3. In Set Scripting Options, set the following options:

    • Set Output Type as Save script to a specific location. Select Save to file. Click Single file. Type the file name and location in File name. Click Advanced.

    • In Advanced Scripting Options set “Script for the database engine type” option as “SQL Database”, set “Convert UDDTs to Base Types” option as “True”, and set “Types of data to script” option as “Schema and data”. Click OK.

  4. Click Next, click Next, and then click Finish.

Run the Script on SQL Database

  1. Create the School database on the Azure SQL Database server through the Azure Platform Management Portal or by executing the following Transact-SQL command:

    CREATE DATABASE School
    
  2. In SQL Server Management Studio, open the Query menu, point to Connection, and select Change Connection.

  3. Enter the Azure SQL Database server name and your credentials, and then click Options >>.

  4. In the Connection Properties tab, type School in the Connect to database drop-down menu, and then click Connect.

  5. Right click on the School database in Azure SQL Database and select New Query.

  6. In SQL Server Management Studio, on the File menu, point to Open, and then click File. In the Open File window, open the school script file from the location you specified earlier in the Generate and Publish Scripts Wizard.

  7. Press F5 to execute the script.

School Database Transact-SQL Definition

  • Run this script to create the School database in the local instance of SQL Server.

    
    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
    

See Also

Community Additions

ADD
Show:
© 2014 Microsoft