Can I Export/Import My Data from Sql Server To ...?

Can I Export/Import My Data from Sql Server To ...?

Post by Bpk. Adi W » Wed, 30 Mar 2005 10:32:52


Can I Export/Import My Data from Sql Server To Excel with SQL Statement?
 
 
 

Can I Export/Import My Data from Sql Server To ...?

Post by Marc Scheu » Wed, 30 Mar 2005 14:34:27

>Can I Export/Import My Data from Sql Server To Excel with SQL Statement?

No - but SQL Server contains the "Data Transformation Services" DTS -
you'll find it under the SQL SErver program group in your start menu,
as "Import / Export". That tool will allow you to do just this -
import and export to and from a variety of formats.

Marc

================================================================
Marc Scheuner May The Source Be With You!
Berne, Switzerland m.scheuner -at- inova.ch

 
 
 

Can I Export/Import My Data from Sql Server To ...?

Post by Kevin Frev » Wed, 30 Mar 2005 22:25:34

Adi,

I've never tried to update an Excel spreadsheet with SQL Server, but you
can read data via DTS (Data Transformation Services, see Books Online for
more info) or the OpenDataSource procedure (again, Books Online for more
info)
Example...

DECLARE
Excel_Cursor cursor
LOCAL
FORWARD_ONLY
OPTIMISTIC
FOR
Select
{Your fields}
From
OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Test.xls";
Persist Security Info=False;
Extended properties=Excel 8.0')...[Sheet1$]

/* Loop throuh a result set ... */
OPEN Excel_Cursor
FETCH NEXT from Excel_Cursor INTO {Some fields}
while (@@fetch_status = 0)
begin
{Insert something, call a stored procedure, etc}
FETCH NEXT from Excel_Cursor INTO {Some fields
end
CLOSE Excel_Cursor
DEALLOCATE Excel_Cursor

The "trick" is the SQL Server must be able to see the file. In the above
example, SQL Server is going to look at it's C:\ drive for the file and not
the machine running the script.

Good luck,
krf
 
 
 

Can I Export/Import My Data from Sql Server To ...?

Post by Stig Johan » Fri, 01 Apr 2005 14:24:45


Actually, you /can/ do that.
If you register the excel file as a linked server, you can do this example:
INSERT INTO DestTable SELECT * FROM LinkedServer..ExcelTableName
(Out of my head, so check the syntax).

We have had such a beast running for a couple of years.


--
Best regards
Stig Johansen