- From the Excel file, import data into the SQL database, very simple, directly with the following statement: / * ======================== ========================================================= * / - if the data import The table already exists insert INTO table select * from openrowset ('microsoft.jet.OleDb.4.0', 'Excel 5.0; HDR = YES; Database = C: /Test.xls' ,sheet1)
- If you import data and generate a 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 has been created in accordance with the data to receive. Head, you can use: INSERT INTO OPENROWSET ('Microsoft.jet.Oledb.4.0', 'Excel 5.0; HDR = YES; Database = C: /Test.xls', SHEET1 $) Select * From
- If the Excel file does not exist, you can also use BCP to guide the class Excel file, pay 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 name "-p" password "'
- Export query case EXEC MASTER..XP_CMDSHELL 'BCP "SELECT AU_FNAME, AU_LNAME FROM PUBS..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 / export Excel file name. SHEET1 $ is the work name name of the Excel file, usually add $ to use. - * /
- The following is a method of exporting the true Excel file:
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 / * - Data Export 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 will be based on versatility, only support Export standard data type
- Zou Jian 2003.10 (Please keep this information) - * /
/ * - Call example
P_exporttb @ TBNAME = 'Area Information', @ path = 'c: /', @ fname = 'aa.xls' - * / create proc p_exporttb @ TbName sysname, - To export table name @Path nVarchar (1000) , - File Storage Directory @Fname NVARCHAR (250) = '- File Name, Default is Table name asDeclare @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 = @ 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 = " '; 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
/ * - If you override the already existing table, add the following statement - to delete the table before creating / if you exist, select @ SQL = 'drop table [' @ TBNAME ']' Exec @ Err = sp_oamethod @obj , 'execute', @ out out, @ SQL - * / - Create table sqlselect @SQL = ', @ fdlist =' 'select @ fdlist = @ fdlist ', [' a.name '] ', @ SQL = @ SQL ', [' a.name ']' Case When B.Name Like '% Char' Ten Case When A.Length> 255 TEN 'MEMO' ELSE 'TEXT (' Cast (A.LENGTH As varchar) ')' end when b.name = '% int' ort 'in b.name Like'% datetime 'Ten' datetime 'when b.name like'% Money 'then' money 'when b.name like'% text 'then' memo 'else b.name endFROM syscolumns a left join systypes b on a.xtype = b.xusertypewhere b.name not in (' image ',' uniqueidentifier ' , 'sql_variant', '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
- Import data set @ SQL = 'OpenRowSet (' 'microsoft.jet.OleDb.4.0', '' Excel 8.0; HDR = YES; IMEX = 1; 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
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
/ * - Data Export Excel Export Data into Excel, contains the field name, files for real Excel files If the file does not exist, if the table does not exist, the automatic creation table is based on versatility, only support Export standard data type
- Zou Jian 2003.10 (Please keep this information) - * /
/ * - Call example
P_exporttb @ Sqlstr = 'SELECT * FROM Area, @ Path =' C: / ', @ fname =' aa.xls', @ SheetName = 'Area Information' - * / CREATE P_EXPORTTB @ SQLSTR VARCHAR (8000) , - query the statement, if you use Order by in the query statement, please add TOP 100 Percent @ path nvarchar (1000), - File Storage Directory @Fname NVARCHAR (250), - File Name @Sheetname Varchar (250) = '' - Work Table Name to create, default as file name as declare @err int, @ src nvarchar (255), @ desc nvarchar (255), @ outten @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 = " '; 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 like '% char' THEN CASE WHEN A.LENGTH> 255 TEN 'MEMO' ELSE 'TEXT (' Cast (A.Length As Varchar) 'End When B.Name Like'% Int 'OR B. Name = 'bit' Then 'int' when b.name like '% DateTime' Ten 'datetime' when b.name Like '% Money' TEN 'MONEY' WHEN B.NAME LIKE '% Text' TEN 'MEMO' ELSE B .name endFROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype = b.xusertypewhere b.name not in ( 'image', 'uniqueidentifier', 'sql_variant', 'varbinary', 'binary', ' TimeStamp ') And A.ID = (Select ID from Tempdb..sysObjects where name = @ TBNAME)
IF @@ rowcount = 0 returnselect @ 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
- Imported data set @ SQL = 'OpenRowSet (' 'Microsoft.jet.OleDb.4.0', '' Excel 8.0; HDR = YES; Database = ' @ Path @ fname ' ', [' @ Sheetname ' $]) '
EXEC ('INSERT INTO' @ SQL '(' @ fdlist ') SELECT' @ fdlist 'from [' @ TBNAME ']')
Set @ SQL = 'Drop Table [' @ TBNAME ']' EXEC (@SQL) 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
