From the Excel file, import data into the SQL database, very simple, directly with the following statement: / * ========================== ======================================================== * / - If you accept the data imported There is already an Insert INTO table select * from openrowset ('microsoft.jet.oledb.4.0', 'Excel 5.0; HDR = YES; Database = C: /Test.xls', SHEET1 $) - If the data is imported and generates table Select * INTO Table from OpenRowSet ('Microsoft.jet.OleDb.4.0', 'Excel 5.0; HDR = YES; Database = C: /Test.xls', SHEET1 $) / * =========== ============================================================================================================================================================================================================= ====== * / - If the data is exported from the SQL database, if the Excel file already exists, it can be used in accordance with the data to be received, you can use: Insert Into OpenRowSet (' Microsoft.jet.OleDb.4.0 ',' Excel 5.0; HDR = YES; Database = C: /Test.xls' ,sheet1 $) Select * From Table - If the Excel file does not exist, you can also use BCP to class Excel's file, paying attention to case: - Export table EXEC MASTER..XP_CMDSHELL 'BCP database name .dbo. Table name OUT "C: / Test.xls" / c - / s "server name" / u "user "-P" password "- Export inquiry case EXEC MASTER..XP_CMDSHELL 'BCP" SELECT AU_FNAME, AU_LNAME AUTHORS ORDER BY AU_LNAME "Queryout" c: /test.xls "/ c - / s" Server Name "/ U" User Name "-p" password "'description.
C: /Test.xls For the import / exported Excel file name. SHEET1 $ is the work table name of the Excel file, usually add $ to use it.
Below is a method of exporting a true Excel file:
/ * - Data exports the data in the Excel export table to Excel, contains the field name, and the file is a real Excel file. If the file does not exist, the file will be automatically created. If the table does not exist, the automatic creation table is based on versatility. Also supports export standard data type --- * // * - call example
P_exporttb @ TBNAME = 'Area Information', @ path = 'c: /', @ fname = 'aa.xls' - * / if exists (select * from dbo.sysObjects where id = Object_ID (n' [dbo]. [p_exporttb] ') And ObjectProperty (id, n'isprocedure') = 1) Drop Procedure [DBO]. [p_exporttb] Go
Create proc p_exporttb @ tbname sysname, - To export the table name, pay attention only to the table name / view name @Path nVarchar (1000), - File storage directory @fname nvarchar (250) = '' - file name, Default as a table name asdeclare @err int, @ src nvarchar (255), @ out @ @ out intdeclare @obj int, @ constr nvarchar (1000), @ SQL VARCHAR (8000), @ fdlist varchar (8000)
- Parameter detection if isnull (@fname, ') =' 'set @ fname = @ TBNAME '. XLS '
- Check if the file already exists if right (@ Path, 1) <> '/' set @ path = @ Path '/' crete table #TB (A bit, b bit, c bit) set @ SQL = @ PATH @ FNameInsert Into #tb exec master..xp_fileexist @SQL
- Database creation statement set @ SQL = @ path @ fnameif exists (select 1 from #tb where a = 1) set @ Constr = 'driver = {Microsoft Excel Driver (* .xls)}; DSN =' ''; Readonly = false ' '; CREATE_DB = "' @ SQL '"; DBQ = ' @ SQLSE SET @ Constr =' Provider = Microsoft.Jet.OleDb.4.0; Extended Properties = "Excel 5.0; HDR = YES ' '; Database =' @ SQL '"'
- Connect database EXEC @ Err = sp_oacreate 'adodb.connection', @ Obj outif @err <> 0 goto lberr
Exec @ err = sp_oamethod @ Obj, 'open', null, @ Constrif @err <> 0 goto lberr - created SQLSELECT @SQL = ', @ fdlist =' SELECT @ fdlist = @ fdlist ',' A.NAME, @ SQL = @ SQL ', [' a.name ']' Case When B.Name in ('char', 'nchar', 'varchar', 'nvarchar') Then 'Text (' CAST (Case When A.Length> 255 THEN 255 Else A.LENGTH End As Varchar) ')' When B.Name in ('Tynyint', 'Int', 'Bigint', 'Tinyint') Then 'Int' When B.Name in ('smalldatetime') Then 'DateTime' WHEN B.NAME IN ('Money', 'Smallmoney') Then 'Money' Else B.Name Endfrom Syscolumns a Left Join Systems B on A.xtype = b.xusertypewhere b.name not in ( 'image', 'text', 'uniqueidentifier', 'sql_variant', 'ntext', 'varbinary', 'binary', 'timestamp') and object_id (@tbname) = idselect @ SQL = 'Create Table [' @ TBNAME '] (' substring (@ SQL, 2, 8000) ')', @ fdlist = substring (@ fdlist, 2,8000)
Exec @ err = sp_oamethod @ obj, 'execute', @ out out, @ SQLIF @err <> 0 goto lberr
Exec @ Err = sp_oadestroy @obj
- Imported data set @ SQL = 'OpenRowSet (' 'Microsoft.jet.OleDb.4.0', '' Excel 5.0; HDR = YES; Database = ' @ Path @ fname ' ', [' @ TBName ' $]) '
EXEC ('INSERT INTO' @ SQL '(' @ fdlist ') SELECT' @ fdlist 'from' @ tbname) Return
LBERR: EXEC SP_OAGETERRORINFO 0, @ src out, @ DESC OUTLBEXIT: SELECT CAST (@err as varbinary (4)) AS error number, @ src AS error source, @ DESC AS error Description Select @ SQL, @ Constr, @ fdlistgo
* - Data Export Excel Export Introduction Data to Excel, contains the field name, file as a real Excel file, if the file does not exist, will automatically create files, if the table does not exist, the automatic creation table is based on versatility, only Support for export standard data types - * /
/ * - Call example
P_exporttb @ Sqlstr = 'SELECT * FROM area, @ Path =' c: / ', @ fname =' aa.xls', @shirename = 'area data' - * / if exists (Select * from dbo.sysObjects WHERE ID = Object_ID (n '[dbo]. [p_exporttb]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [DBO]. [p_exporttb] GO
CREATE P_EXPORTTB @ SQLSTR SYSNAME, - Query statement, if you use Order by in the query statement, add TOP 100 Percent, pay attention, if you export table / view, with the above stored procedure @Path nvarchar (1000), File storage directory @fname nvarchar (250), - File name @Sheetname varchar (250) = '' - the work table name to create, the default is the file name as Declare @err int, @ src nvarchar (255), @ Desc nvarchar (255), @ out intdeclare @obj int, @ Constr nvarchar (1000), @ SQL VARCHAR (8000), @ fdlist varchar (8000)
- Parameter detection if isnull (@fname, ') =' 'set @ fname =' temp.xls'if isnull (@Sheetname, ') =' 'set @ Sheetname = Replace (@fname,'. ', '#')
- Check if the file already exists if right (@ Path, 1) <> '/' set @ path = @ Path '/' crete table #TB (A bit, b bit, c bit) set @ SQL = @ PATH @ FNameInsert Into #tb exec master..xp_fileexist @SQL
- Database creation statement set @ SQL = @ path @ fnameif exists (select 1 from #tb where a = 1) set @ Constr = 'driver = {Microsoft Excel Driver (* .xls)}; DSN =' ''; Readonly = false ' '; CREATE_DB = "' @ SQL '"; DBQ = ' @ SQLSE SET @ Constr =' Provider = Microsoft.Jet.OleDb.4.0; Extended Properties = "Excel 5.0; HDR = YES ' '; Database =' @ SQL '"- Connect Database EXEC @ Err = SP_OACREATE' AdoDb.Connection ', @ Obj Outif @err <> 0 goto lberr
Exec @ Err = SP_OAMETHOD @ Obj, 'Open', NULL, @10 @err <> 0 goto lberr
- Create a table sqldeclare @tbname = '## tmp _' convert (varchar (38), newid ()) set @ SQL = 'SELECT * INTO [' @ TBNAME '] from (' @ SQLSTR ') a'exec (@SQL)
SELECT @SQL = ', @ fdlist =' 'select @ fdlist = @ fdlist ', ' a.name, @ SQL = @ SQL ', [' a.name '] Case When B.Name in ( 'char', 'nchar', 'varchar', 'nvarchar') Then 'Text (' CAST (Case When a.length> 255 THEN 255 Else A.LENGTH End As Varchar) ')' When B.Name in ('Tynyint', 'Int', 'Bigint', 'Tinyint') Then 'Int' When B.Name in ('SmallDatetime') Then 'DateTime' When B.Name in ('Money', ' smallmoney ') then' money 'else b.name endFROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype = b.xusertypewhere b.name not in (' image ',' text ',' uniqueidentifier ', 'sql_variant', 'ntext', 'varbinary', 'binary', 'timestamp') and a.id = (select id from tempdb..sysobjects where name = @ TBName) Select @ SQL = 'Create Table [' @ Sheetname '] (' substring (@ SQL, 2, 8000) ')', @ fdlist = substring (@ fdlist, 2,8000) exec @ err = sp_oamethod @ Obj, 'execute', @ out out, @ SQLIF @err <> 0 goto lberr
Exec @ Err = sp_oadestroy @obj
- Import data set @ SQL = 'OpenRowSet (' 'Microsoft.jet.OleDb.4.0', '' Excel 5.0; HDR = YES; Database = ' @ Path @ fname ' ', [' @ Sheetname ' $]) '
EXEC ('INSERT INTO' @ SQL '(' @ fdlist ') SELECT' @ fdlist 'from [' @ TBNAME ']')
Set @ SQL = 'Drop Table [' @ TBNAME ']' EXEC (@SQL) ReturnLberr: Exec sp_oageterrorinfo 0, @ src out, @DESC OUTLBEXIT: SELECT CAST (@err as varbinary (4)) AS error number, @ SRC AS Error Source, @ DESC AS Error Description Select @ SQL, @ Constr, @ fdlistgo
