SQL Server - Insert data from Excel to SQL server
Asked By MI
07-Mar-07 10:09 AM
Hi,
I need to insert data from Excel to SQL server. I can't use SSIS, because
many reason on of them is to have an oline Process.
My question is to know what is the best way between the 2 following solution:
1- To insert directly data with an insert wich is running after the user
press a button (with QueryTables.Add(Connection:=Array( ..........) or
2- To call a stored procedure and to pass the data as a parameter.
I think to use a stored procedure is the best way to avoid concurrent
access, but it's harder to pass all the information.
Stored procedure
(1)
Excel
(1)
Create
(1)
Uri Dimant replied...
MIB
1)
You can create a linked server to EXCEL and get the data
2)
You can use OPENDATASOURCE command
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
3) Linchi Shea has written the article for the subject
http://sqlblog.com/blogs/linchi_shea/archive/2007/03/06/a-little-scripting-saves-the-day.aspx
kalenthirababu replied...
when i execute this query ":Insert into mic.dbo.Eschool_TEACHERS Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\teachers.xls;HDR=YES','SELECT * FROM [teachers$]')" i am getting following error "Cannot process the object "SELECT * FROM [teachers$]". The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
how to over come this.
just using a temp table. When the table is created I would then have to create 3 indexes - would drop one index (the Guid). Then the table would go away when I exit the procedure. I do not think there is any necessity to drop the table (and indexes) as anyway. But now I do not have to delete the records at the end or create the Guid at the beginning. Anything I am missing here? Thanks, Tom SQL Server Programming Discussions SQL Server (1) Stored procedure (1) CREATE TABLE (1) Statistics (1) Describe (1) Foreign (1) Disk (1) Proc (1) In the 1960 not records), why not re-write the code to use a derived table in each procedure? The procedures would become independent, loosely coupled modules. You will probably have to do some than just that, but it is a start. Hi Tom Creating many temp tables can create a bottleneck on tempdb See http: / / support.microsoft.com / kb / 328551 Have you looked at
OPENROWSET / OPENDATASOURCE with Excel 2007 / SQL 2005 SQL Server Does anybody have an example of a query that successfully retrieves data from an Excel 2007 spreadsheet? The knowledgebase articles I can find provide examples only for mdb files and to run this query: SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source = U: \ Excel-SQL.xlsm; Extended Properties = Excel 12.0'). . .[Sheet1$] That query is my attempt to adapt the code found here http support.microsoft.com / kb / 321686 SQL Server Programming Discussions RHVrZSBDYXJleQ (1) SQL Server (1) Excel 2007 (1) Office (1) Stored procedure (1) IDBCreateCommand (1) SELECT INTO (1) CREATE PROC (1) On Jul 24, 2:50 pm, Duke Carey <DukeCa. . .@> You are using the
decide who has access and the network guys manage. What I would like is to create a SP in a database that calls sp_start_job. The problem is most users do not privs to do so, returning a message similar to: Msg 916, Level 14, State 1, Procedure Signed, Line 4 The server principal "Domain \ User" is not able to access the database on creating and certifying a user in both databases. What I would like it to create a role in a database, assign my Windows group to that role, then allowing anyone in that role to execute the procedure that executes sp_start_job. Is there any way to get around me actually having to load got the execution context error on the SPs. SQL Server Security Discussions SQL Server (1) Excel (1) CREATE PROCEDURE (1) Stored procedure (1) CREATE DATABASE (1) CREATE TABLE (1) PRIMARY KEY (1) Privileges (1) I found
running a stored procedure through different databases SQL Server Hi! I need to run my stored procedure on a monthly basis through all the databases and spit out a report in Excel. Here is my stored procedure for the report: CREATE PROCEDURE usp_DR_Billing (@StartDate datetime, @EndDate datetime, @PreviousDateEnd datetime) AS - -insert all the fields from Matters
columns have the same name. Thanks in advance! SQL Server Programming Discussions SQL Server (1) Excel (1) Stored procedure (1) CREATE TABLE (1) SELECT INTO (1) INNER JOIN (1) Bit (1) Report (1) Who is picking are there? A You CAN insert into a temp table the same column if you create your temp table first. The few extra keystrokes insure that you have actually thought out your table structure and you know what data you are putting into it. create table junk (id int, value int); insert into junk (id, value) select 1, 21 union union select 7, 27 union select 8, 28 union select 9, 29; select * from junk; create table #temptable (id1 int, id2 int, id3 int); insert into #temptable (id1, id2, id3) select On Mar 26, 4:25 = A0pm, "Aaron Bertrand [SQL Server MVP]" t SELECT INTO to create the temp table. Once the data is in a sql table (temp or permanent), I