Re: Bulk Inserts - High Speed

• Previous message: Jerry Pisk: "Re: SqlConnection question"
• In reply to: Microsoft News Server: "Bulk Inserts - High Speed"
• Messages sorted by: [ date ] [ thread ]

Date: Wed, 21 Apr 2004 10:40:47 +0700

"Microsoft News Server" <ms@ms> wrote in message news:eLmyPHpJEHA.3040@TK2MSFTNGP09.phx.gbl...
> I have a VB application that needs to do many many (1Million) inserts into a
> SQLServer Database. I am doing so in 1000-10000 record chunks. The problem
> is, if I use ADO and AddNew the memory usuage is phenomenal. Once I insert
> the record I don't care about the record any longer in VB. If I use INSERT
> commands SQLServer seems to bog down and get really slow.
>
> What is the best way to insert huge record counts from one DB (ODBC) to
> SQLServer using VB without the nasty memory penalty and while maintaining
> high speed throughout from SQLServer?
>
> Cheers
> Mark
>
>

    See This example OPENROWSET Transact SQL it may be useful (From SQL Books Online)
    

A. Use OPENROWSET with a SELECT and the Microsoft OLE DB Provider for SQL Server
This example uses the Microsoft OLE DB Provider for SQL Server to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized from the datasource, user_id, and password, and a SELECT is used to define the row set returned.

USE pubs
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
   'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO
B. Use OPENROWSET with an object and the OLE DB Provider for ODBC
This example uses the OLE DB Provider for ODBC and the SQL Server ODBC driver to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized with a provider_string specified in the ODBC syntax used by the ODBC provider, and the catalog.schema.object syntax is used to define the row set returned.

USE pubs
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
   'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
   pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
GO
C. Use the Microsoft OLE DB Provider for Jet
This example accesses the orders table in the Microsoft Access Northwind database through the Microsoft OLE DB Provider for Jet.

 
Note This example assumes that Access is installed.

USE pubs
GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
   AS a
GO
D. Use OPENROWSET and another table in an INNER JOIN
This example selects all data from the customers table from the local SQL Server Northwind database and from the orders table from the Access Northwind database stored on the same computer.

 
Note This example assumes that Access is installed.

USE pubs
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c INNER JOIN
   OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
   AS o
   ON c.CustomerID = o.CustomerID
GO
Another FAQQ. Are there any examples of heterogeneous data queries from SQL 7 to other sources?(v1.4 1999.04.27)A. Here are a variety of examples for several different datasources. Note that you will have to change filenames, drives, regions etc. as necessary for your environment :-1. Selecting from an Excel spreadsheet using OpenRowSet. Here, c:\ramsql7.xls is a spreadsheet (note we haven't specified the extension). sheet1 is a sheet within the spreadsheet - note the trailing $.SELECT * FROM OpenRowSet ('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=c:\ramsql7', 'SELECT * FROM [sheet1$]') as a2. Selecting from an Access linked server database via Jet. The Access database is at c:\msd\invent97.mdbprint 'add Jet 4.0 Invent'-- Clear up old entryif exists(select * from sysservers where srvname = N'INV')exec sp_dropserver N'INV', N'droplogins'go-- create linked serverexec sp_addlinkedserver @server = N'INV', @srvproduct = '', @provider =N'Microsoft.Jet.OLEDB.4.0', @datasrc = N'c:\msd\invent97.mdb'go-- setup default admin login for Accessexec sp_addlinkedsrvlogin @rmtsrvname = N'INV', @useself = N'FALSE',@locallogin = NULL, @rmtuser = N'admin', @rmtpassword = N''go-- Lists all tables in the linked serverexec sp_tables_ex N'INV'go-- Now select from a table in the Access db called INVENT select * from INV...INVENTgo3. DB/2 accessed via Star SQL Driver with SNA 4.0.print 'add DB2 LinkedServer'if exists(select * from sysservers where srvname = N'DB2')exec sp_dropserver N'DB2', N'droplogins'exec sp_addlinkedserver @server = 'DB2', @provider = 'MSDASQL', @srvproduct= 'StarSQL 32',@location = 'DBT1', @datasrc = 'DB2IBM'exec sp_addlinkedsrvlogin @rmtsrvname = 'DB2', @locallogin = 'sa', @useself= 'false',@rmtuser = 'HDRUSER' ,@rmtpassword = 'SQL7'go-- test to see is catalog is accesiblesp_tables_ex N'DB2'-- create view to see if select workscreate view V007MUNI as select * from DB2..T1ADM007.V007MUNIgoselect * from V007MUNIgo4. DBASE IVprint 'add DBase IV LinkedServer'if exists(select * from sysservers where srvname = N'DBFs')exec sp_dropserver N'DBFs', N'droplogins'EXEC sp_addlinkedserver'DBFs','Jet 4.0','Microsoft.Jet.OLEDB.4.0','F:\DBFs',NULL,'dBase IV'GOexec sp_addlinkedsrvlogin@rmtsrvname = 'DBFs',@useself = false,@locallogin = NULL,@rmtuser = NULL,@rmtpassword = NULLgoSELECT * FROM DBFs...testgo5. Visual FoxPro. Using a FoxPro DBC file to group the DBF files. ODBC DSN pre-defined called FOX using the Microsoft Visual FoxPro Driver 6.01.8440.01-- FOX using Visual FoxPro Database file .DBCprint 'add FOXSERVER'if exists(select * from sysservers where srvname = N'FOXSERVER')exec sp_dropserver N'FOXSERVER', N'droplogins'exec sp_addlinkedserver @server=N'FOXSERVER',@srvproduct ='',@provider = N'MSDASQL',@datasrc=N'FOX'exec sp_addlinkedsrvlogin @rmtsrvname=N'FOXSERVER',@useself = N'FALSE',@locallogin = NULL,@rmtuser = N'',@rmtpassword =N''exec sp_tables_ex N'FOXSERVER'select * from [FOXSERVER].[D:\SQL\FOX\TESTDATA.DBC]..[customer]6. FoxPro using plain DBF files in a directory. Using an ODBC system DSN (Called DBF) using the Microsoft Visual FoxPro Driver 6.01.8440.01-- DBF using plain .DBF filesprint 'add DBFSERVER'if exists(select * from sysservers where srvname = N'DBFSERVER')exec sp_dropserver N'DBFSERVER', N'droplogins'exec sp_addlinkedserver @server=N'DBFSERVER',@srvproduct ='',@provider = N'MSDASQL',@datasrc=N'DBF'exec sp_addlinkedsrvlogin @rmtsrvname=N'DBFSERVER',@useself = N'FALSE',@locallogin = NULL,@rmtuser = N'',@rmtpassword =N''exec sp_tables_ex N'DBFSERVER'select * from [DBFSERVER].[D:\SQL\DBF]..[country]7. FoxPro using installable Jet 3.51 ISAM drivers.print 'add FOXDBC using Jet 3.51'if exists(select * from sysservers where srvname = N'FOXDBC')exec sp_dropserver N'FOXDBC', N'droplogins'exec sp_addlinkedserver 'FOXDBC', 'Jet 3.51', 'Microsoft.Jet.OLEDB.3.51','c:\sql\fox', NULL, 'FoxPro 3.0'exec sp_addlinkedsrvlogin @rmtsrvname = N'FOXDBC', @useself = N'FALSE',@locallogin = NULL, @rmtuser = NULL, @rmtpassword = NULLexec sp_helplinkedsrvlogin N'FOXDBC'exec sp_tables_ex N'FOXDBC'I use this code to append a million rows of dbf to SQL2K. so should be help. What you have to do is use append or select into query. Have fun Jeerakarn W. watakit@hotmail.com

• Previous message: Jerry Pisk: "Re: SqlConnection question"
• In reply to: Microsoft News Server: "Bulk Inserts - High Speed"
• Messages sorted by: [ date ] [ thread ]