ÓÃSQL²éѯ·ÖÎöÆ÷²Ù×ÝExcel¼°µ¼Èëµ¼³öÊý¾Ý
ÓÃSQL²éѯ·ÖÎöÆ÷²Ù×ÝExcel¼°µ¼Èëµ¼³öÊý¾Ý
http://www.delphibbs.com/keylife/iblog_show.asp?xid=32983
SQL SERVER ºÍEXCELµÄÊý¾Ýµ¼Èëµ¼³ö
ͨ³£µÄ·½·¨ÊÇʹÓÃͼÐνçÃæµÄdts¹¤¾ß£¬µ«·¢¾õÓÐЩʹÓÃÃüÁîÐнçÃæµÄ·½Ê½¸ü¼òµ¥
1¡¢ÔÚSQL SERVERÀï²éѯExcelÊý¾Ý:
-- ======================================================
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
-------------------------------------------------------------------------------------
2¡¢½«ExcelµÄÊý¾Ýµ¼ÈëSQL server :
-- ======================================================
SELECT * into newtable
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
-------------------------------------------------------------------------------------
½áÂÛ£º ÕâÁ½¸ö¹¦Äܶ¼Ê¹ÓÃÁËopenDatasourceº¯ÊýÀ´·µ»ØÒ»¸öרÓõÄÊý¾Ý¿â·þÎñÆ÷£¬×÷Ϊ4²¿·ÖÃû³ÆµÄµÚÒ»²¿·Ö¡£
OPENDATASOURCE ( provider_name, init_string )
provider_name: ÈçSqloledb£¬ Microsoft.Jet.OLEDB.4.0µÈ
init_string: Á¬½Ó×Ö·û´®£¬Èç'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'
ÌṩÁË·þÎñÆ÷Ãû£¬¶ÔExcelÀ´Ëµ¾ÍÊÇÎļþÃû
Óû§ID£¬ÃÜÂë
Extended properties£º ÎÒÊÔÁËÌîExcel 5.0 »ò8.0¶¼¿ÉÒÔ£¬ÆäËû²»ÐÐ
×îºóµÄ[Sheet1$]ÊÇÒ³Ãû£¬ÕâÀïµ±×ö±íÃûÓã¬×¢ÒâÒ»¶¨Òª¼ÓÉÏ$
ÁíÍâÒ»¸öÀý×Ó£¬ÓÃÀ´È¡ÆäËûsql server·þÎñÆ÷ÉϵÄÊý¾Ý£º
SELECT *
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
¹þ¹þ£¬ÑÛÊì¶àÁ˰ɣ¬Ò»ÑùµÄ£¡
µ«ÊÇÕâ¸öÓ÷¨Ö»ÓÃÓÚad hoc£¬ ¶ÔÓÚ´óÁ¿µÄ²Ù×÷»¹ÊÇÓÃlinked server±È½ÏÕý¹æ£¬ linked serverÕâô¼Ó£º
EXEC sp_addlinkedserver 'Excel',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\book1.xls',
NULL,
Ïà¹ØÎĵµ£º
DECLARE @HDOC INT --Îĵµ¾ä±ú
DECLARE @XMLSTRING VARCHAR(200) --XML×Ö·û´®
SET @xmlString ='<?xml version="1.0"?>
<ROOT>
<USER ID="1" Name="SBQCEL"/>
<USER ID="2" Name="PEACELI"/>
<USER ID="3" Name="SHEEPCHANG"/>
</ROOT>'
--ʹÓÃϵͳ´æ´¢ ......
ÒÔÏÂÊÇ·¢ÔÚ÷×ÓÂÛ̳µÄÌù×Ó£¬×ª·¢¹ýÀ´£¬Ï£Íû¸øÓõÃ×ŵĺüÓѲο¼¡£
ÎҵijÌÐòÒÔǰһֱÊÇÓû¨Éú¿Ç°ó¶¨IPʵÏÖµÄÔ¶³Ì£¬ÓÉÓÚ¿Í»§·þÎñÆ÷ºÍ¿Í»§¶Ë¶¼ÊÇͨ¹ý¿í´øÉÏÍø£¬Ò»°ãµÄ²Ù×÷£¨¿ª½ø»õµ¥¡¢ÏúÊÛµ¥µÈ£©ËÙ¶ÈÒ²»¹²»´í£¬µ«ÊÇÔÚÔ¶³Ì¿Í»§¶ËÐÞ¸ÄÉÌÆ·×ÊÁÏ£¨10000¶àÌõ¼Ç¼£©¡¢²éѯһ¶ÎʱÆÚµÄ½øÏú´æÁ÷Ë®£¨Ò»ÖÜ5000ÌõÒÔÉÏ£©µÈÉæ¼°µ½È¡¼Ç¼Á¿´óµ ......
ÇåÀíSQL Server 2008Êý¾Ý¿âÈÕÖ¾
ÊÕ²Ø
µÚÒ»²½, ÔÚÊÕËõǰÏȲ鿴ÈÕÖ¾µÄ´óС:
SELECT
*
from
sysfiles
WHERE
name
LIKE
'
%
LOG
%'
GO
µÚ¶þ²½, °ÑÊý¾Ý¿âµÄ»Ö¸´Ä£Ê½Éè³É”¼òµ¥”:
ALTER
DATABASE
¿âÃû
SET
......
--1:»·¾³
¡¡¡¡·þÎñÆ÷»·¾³:
¡¡¡¡»úÆ÷Ãû³Æ£º ZehuaDb
¡¡¡¡²Ù×÷ϵͳ£ºWindows 2000 Server
¡¡¡¡Êý¾Ý¿â°æ±¾£ºSQL 2000 Server ¸öÈ˰æ
¡¡¡¡¿Í»§¶Ë
¡¡¡¡»úÆ÷Ãû³Æ£ºZlp
¡¡¡¡²Ù×÷ϵͳ£ºWindows 2000 Server
¡¡¡¡Êý¾Ý¿â°æ±¾£ºSQL 2000 Server ¸öÈ˰æ
¡¡¡¡--2:½¨Óû§ÕʺÅ
¡¡¡¡ÔÚ·þÎñÆ÷¶Ë½¨Á¢ÓòÓû§ÕʺÅ
¡¡¡¡ÎҵĵçÄÔ¹ÜÀí- ......
ORACLEÓëSQL SERVERÓï·¨Çø±ð
Ò»¡¢Êý¾ÝÀàÐÍ
ORACLEÓëSQL SERVERÔÚÊý¾ÝÀàÐ͵ĶԱÈÈçÏ£º
SQL SERVER
ORACLE
Êý×ÖÀàÐÍ
DECIMAL[(P[, S])]
NUMBER[(P[, S])]
NUMERIC[(P[, S])]
NUMBER[(P[, S])]
FLOAT[(N)]
NUMBER[(N)]
INT
NUMBER
SMALLINT
NUMBER
TINYINT
NUMBER
MONEY
NUMBER[19,4]
SM ......