Importar datos desde Excel a SQL Server

Para realizar esta tarea, Microsoft sugiere varios métodos:

  • SQL Server 2000 Data Transformation Services (DTS)
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • SQL Server servidores vinculados
  • SQL Server las consultas distribuidas
  • ActiveX Data Objects (ADO) y el proveedor Microsoft OLE DB para SQL Server
  • ADO y el proveedor Microsoft OLE DB para Jet 4.0
Una forma rápida es usar un servidor vinculado, o en inglés Linked Server.
Para lograr esto crea un  Linked Server llamado Excel.  Cambia la dirección del parámetro @datasrc=N’C:\Temporal\importar.xls’ a una ruta válida en tu PC.
/****** Object:  LinkedServer [EXCEL]   ******/
EXEC master.dbo.sp_addlinkedserver @server = N'EXCEL', @srvproduct=N'Excel', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'C:\Temporal\importar.xls', @provstr=N'Excel 8.0'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCEL',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'collation name', @optvalue=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
Para utilizar el Linked Server apoyate de Openquery
SET quoted_identifier off
sp_tables_ex EXCEL
SELECT * FROM Openquery(EXCEL,"select * from [Hoja1$]")

Notas relacionadas :

You can leave a response, or trackback from your own site.

Deje una respuesta

Webdesign