Hi Billy,
In this case, I'd suggest using the Data Transformation Services utility
built into SQL Server 2000. This assumes you have access to SQL Enterprise
Manager and appropriate permissions to use it on the server. The list below
looks like a lot of steps, but once you have it set up you can save it and
then re-run it as often as you like without having to redo anything
1) Open SQL Enterprise Manager and expand your server node so you can see
all the folders below it.
2) Directly below the Databases folder is the Data Transformation Services
folder. Right-click on this folder and choose "New Package". This will open
the DTS designer window.
3) You'll see a bunch of icons in two palette shaped toolbars docked on the
left side of the window. In the Connection toolbar click the upper left
icon, which will be called "Microsoft OLEDB Provider for SQL Server".
4) In the Connection Properties window pick and/or enter your SQL Server
name, authentication method and the name of the database you want to put
data into, then click OK. This will place a SQL Server icon onto the DTS
canvas below.
5) Back on the Connection toolbar, click the Excel icon. It's called
"Microsoft Excel 97-2000", but it will work with any version of Excel from
97 to 2003.
6) In the Connection Properties window point the File name entry to the
workbook containing the data you want to transfer to SQL Server and then
click OK. This will place an Excel icon onto the DTS canvas next to your SQL
Server icon.
7) On the DTS canvas, click the Excel icon you just added, then hold down
the Ctrl key and click the SQL Server icon next to it (you MUST select them
in this order). Now click the "Transform Data Task" icon in the Task
toolbar. This is the third icon in the top row of the Task toolbar to the
left. It has a black arrow over a yellow gear. This creates a data pump
between Excel and SQL Server, as shown by a black arrow that should now
point from the Excel icon to the SQL Server icon.
8) Select the new arrow you just added, right-click over it and choose
Properties. In the Source tab select the name of the worksheet that contains
the data (all worksheet names will have a $ after them). In the Destination
tab select the table in your database where you want the Excel data to be
loaded. Click the Transformation tab and verify that the arrows from the
Source to the Destination fields look correct, then click OK.
At this point you can load the data from Excel into SQL Server by
clicking the green Execute arrow on the top toolbar. You can also save this
DTS Package so you can open and re-run it later when you have new data to
load. Once you have the package created and saved, next time all you need to
do is open it and click the Execute button (assuming the name of the
workbook, source worksheet and all headers are exactly the same as when the
package was created).
--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/
* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm