Trasfer data to SQL Server

B

Billy Rogers

What's the best way to transfer data from Excel to SQL Server?

I have the SQL server table set up and the spreadsheet is set up like the
table with field names in the first row and data below. There are over 3000
rows and the number of rows could vary each month when the process is run.

Should I loop through each row and execute a SQL INSERT statement (using
ADO) for each row?

Should I add all 3000 rows to a recordset and then use the Update method of
the recordset?

Or is there some other method that's better for moving the data?

--
"Just because you don''t know how to do something doesn''t mean it can''t be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/
 
R

Rob Bovey

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
 
B

Billy Rogers

Do you know how you would execute the DTS package from excel?
--
"Just because you don''t know how to do something doesn''t mean it can''t be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/
 
G

gimme_this_gimme_that

For a one shot deal - no you should write the insert statements to a
file and then execute the statements in batch.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top