Need suggestion: importing data into SQL Express

[VB Programmer]

I have an ASP.NET 2.0 website with a SQL Express database. I also have an
excel spreadsheet with alot of data on it. (If need be I can save it as a
CSV, XML, or whatever...)

I want the user to be able to import the data into an SQL Express database
(existing table) via a button on my ASPX page.

Do you recommend importing the data as XML into like a dataset, then
updating the SQL Express table with this dataset? If so, what's the easiest
way to update a table with an existing dataset? Additional
recommendations/suggestions appreciated...

Thanks!

 

[Cowboy \(Gregory A. Beamer\)]

1. Create a table that has columns that match the Excel spreadsheet (data
type, length, etc.)
2. Create a DataSet from the table (empty)
3. Fill the DataSet from the Excel spreadsheet
4. Attach to SQL with the Adapter
5. Fire Update
6. Run a stored proc to move the data to permanent tables
7. Empty the temp table

Another option is Integration Services (formerly DTS). I believe the base
implementation ships with Express.

SQL Bulk Load is another option to get it into the table. You can wrap the
Bulk Load in a Process object and fire it off. You then run the clean and
move operations (#6 above).

The point here? Moving the data and cleaning it up into permanent tables are
two different steps. I would pretty much ALWAYS do the clean and move
separate from the import into temp tables. Overall, I would prefer the
Integration or Bulk Load method, but it has a steeper learning curve.
Sucking into a DataSet and firing Update is fairly simple.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************

 

[VB Programmer]

Thanks Gregory. I'll take a look at these...

1. Create a table that has columns that match the Excel spreadsheet (data
type, length, etc.)
2. Create a DataSet from the table (empty)
3. Fill the DataSet from the Excel spreadsheet
4. Attach to SQL with the Adapter
5. Fire Update
6. Run a stored proc to move the data to permanent tables
7. Empty the temp table

Another option is Integration Services (formerly DTS). I believe the base
implementation ships with Express.

SQL Bulk Load is another option to get it into the table. You can wrap the
Bulk Load in a Process object and fire it off. You then run the clean and
move operations (#6 above).

The point here? Moving the data and cleaning it up into permanent tables
are two different steps. I would pretty much ALWAYS do the clean and move
separate from the import into temp tables. Overall, I would prefer the
Integration or Bulk Load method, but it has a steeper learning curve.
Sucking into a DataSet and firing Update is fairly simple.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************

 

[William \(Bill\) Vaughn]

There is another easier way that leverages SQL Bulk Copy (DTS/SSIS): the
SqlBulkCopy class in ADO.NET 2.0. It makes it easy to move any data that can
be exposed by a DataReader (any kind of DataReader) into a table on the SQL
Server. Once it's there you can write all the TSQL you need to validate it
and post it to the production tables. You can even write the server-side
validation logic in a CLR routine.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________