 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Bpk. Adi Wira Kusuma
Guest
|
 Posted: Tue Mar 29, 2005 1:32 am Post subject: Can I Export/Import My Data from Sql Server To ...? |
 |
|
Can I Export/Import My Data from Sql Server To Excel with SQL Statement?
|
|
| Back to top |
|
 |
Marc Scheuner
Guest
|
| Posted: Tue Mar 29, 2005 5:34 am Post subject: Re: Can I Export/Import My Data from Sql Server To ...? |
|
|
| Quote: | 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
|
|
| Back to top |
|
|
Kevin Frevert
Guest
|
| Posted: Tue Mar 29, 2005 1:25 pm Post subject: Re: Can I Export/Import My Data from Sql Server To ...? |
|
|
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
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma (AT) yahoo (DOT) com.sg> wrote
| Quote: | Can I Export/Import My Data from Sql Server To Excel with SQL Statement?
|
|
|
| Back to top |
|
|
Stig Johansen
Guest
|
| Posted: Thu Mar 31, 2005 5:24 am Post subject: Re: Can I Export/Import My Data from Sql Server To ...? |
|
|
Marc Scheuner wrote:
| Quote: | 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.
|
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
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
