SQL Server 2000
Exercise 2:
Manipulate Data and Stored Procedure

-----Week 5-----
Please follow those steps to practise:
1. Use bcp to export all data from Orders table of PracticeDB to
c:\Orders.txt (or to c:\Orders.csv)
2. Change some data in the c:\Orders.txt and save. Then import to Orders
table from the text file using bcp
3. Import Orders.txt to Orders table using BULK INSERT
4. Create a Linked Server ‘LinkedPracticeDB’ which link to an Access
database ‘PracticeDB.mdb’ (firstly you have to create an Access database
similar to PracticeDB in SQL Server and input some data). Then do a
select data using four-part name and OPENQUERY
5. Using ad hoc computer name with OPENROWSET and
OPENDATASOURCE functions to select data from ‘PracticeDB.mdb’
6. Create the following Cursor

DECLARE @au_lname varchar(40), @au_fname varchar(20)
DECLARE Employee_Cursor CURSOR FOR

SELECT LastName, FirstName FROM Northwind.dbo.Employees

OPEN Employee_Cursor

FETCH NEXT FROM Employee_Cursor INTO @au_lname, @au_fname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Author:' + @au_fname + ' ' + @au_lname
FETCH NEXT FROM Employee_Cursor INTO @au_lname,

END
Else
BEGIN
INSERT INTO
[Orders](OrderID,ProductName,CustomerID)
VALUES(@OrderID,@ProductName,@CustomerID)
SELECT @Result=0
COMMIT TRANSACTION
END
END
Else
BEGIN
If Exists(SELECT OrderID FROM [Orders] WHERE
OrderID=@OrderID)
BEGIN
SELECT @Result=1
ROLLBACK TRANSACTION
END
Else
BEGIN
INSERT INTO
[Orders](OrderID,ProductName,CustomerID)
VALUES(@OrderID,@ProductName,@CustomerID)
SELECT @Result=0
COMMIT TRANSACTION
END
END
Print @Result
Return