From the Excel file, import data into the SQL database, very simple, directly with the following statement:
/ * ================================================================================================================================================================ ==================== * /
- If the table that accepts data is imported 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 you export data from the SQL database, if the Excel file already exists, and you have created a good header 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 guide the file of EXCEL, 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"'
- Situation of exporting queries
Exec master..xp_cmdshell 'bcp "SELECT AU_FNAME, AU_LNAME" queryout "c: /test.xls" / c - / s "server name" / u "user name" -p "password" '
Description.
C: /Test.xls is the import / exported Excel file name.
Sheet1 $ is the work name name of the Excel file, usually add $ to use it.
Below is a method of exporting a true Excel file:
/ * - Data Export Excel
Export data in the table to Excel, contain field names, files as true Excel files, if the file does not exist, will automatically create files
If the table does not exist, the table will be automatically created.
Based on versatility, only the standard data type is only supported
--- * /
/ * - Call example
P_exporttb @ TBNAME = 'Regional 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, - The table name to be exported, pay attention to only the table name / view name
@Path nvarchar (1000), - File Storage Directory
@fname nvarchar (250) = '' - File name, default is a table name
AS
Declare @ERR INT, @ src nvarchar (255), @ desc nvarchar (255), @ out Int
Declare @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 '/'
Create Table #TB (A Bit, B bit, C Bit)
Set @ SQL = @ Path @ fname
INSERT INTO #TB EXEC MASTER..XP_FILEEXIST @SQL
- Database creation statement
Set @ SQL = @ Path @ fname
IF EXISTS (SELECT 1 from #tb where a)
Set @ constr = 'driver = {Microsoft Excel Driver (* .xls)}; DSN =' '' '; readonly = false'
'; Create_db = "' @ SQL '"; DBQ =' @ SQL
Else
Set @ Constr = 'provider = microsoft.jet.oledb.4.0; extended Properties = "Excel 5.0; HDR = YES'
'; Database =' @ SQL '"'
--Connect to the database
Exec @ Err = sp_oacreate 'adodb.connection', @ obj OUT
IF @err <> 0 goto lberr
Exec @ Err = SP_OAMETHOD @ Obj, 'Open', NULL, @10 @err <> 0 goto lberr
- Create a table 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', 'datetime') Then 'datetime'
When B.Name in ('Money', 'Smallmoney') Then 'Money'
Else B.Name End
From syscolumns a left join systemypes b on a.xtype = b.xUsertype
WHERE B.NAME NOT IN ('Image', 'Text', 'UNIQUEIDENTIFIER', 'SQL_VARIANT', 'NTEXT', 'VARBINARY', 'Binary', 'TimeStamp'
And Object_ID (@tbname) = ID
SELECT @ SQL = 'Create Table [' @ TBNAME
'] (' substring (@ sql, 2,8000) ')'
, @ fdlist = Substring (@ fdlist, 2,8000)
Exec @ err = sp_oamethod @ obj, 'execute', @ out out, @ SQL
IF @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 ' ', [' @ TBNAME '$])'
EXEC ('INSERT INTO' @ SQL '(' @ fdlist ') SELECT' @ fdlist 'from' @ tbname) Return
lberr:
EXEC SP_OAGETERRORINFO 0, @ src out, @ DESC OUT
Lbexit:
SELECT CAST (@err as varbinary (4)) AS error number
, @ src AS error source, @ DESC AS error description
SELECT @ SQL, @ Constr, @ fdlist
Go
* - Data Export Excel
Export data in the query to Excel, contain field name, file as true Excel file
If the file does not exist, the file will be created automatically.
If the table does not exist, the table will be automatically created.
Based on versatility, only the standard data type is only supported
- * /
/ * - Call example
P_exporttb @ SQLSTR = 'SELECT * FROM Area Information'
, @ path = 'c: /', @ fname = 'aa.xls', @ SheetName = 'Area Information'
- * /
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
@Sqlstr sysname, - query statement, if you use Order by in the query statement, add TOP 100 Percent, note that if you export table / view, use 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 Int
Declare @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 '/'
Create Table #TB (A Bit, B bit, C Bit)
Set @ SQL = @ Path @ fname
INSERT 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 =' @ SQL
Else
Set @ Constr = 'provider = microsoft.jet.oledb.4.0; extended Properties = "Excel 5.0; HDR = YES'
'; Database =' @ SQL '"'
--Connect to the database
Exec @ Err = sp_oacreate 'adodb.connection', @ obj OUT
IF @err <> 0 goto lberr
Exec @ err = sp_oamethod @ obj, 'open', null, @ Constr
IF @err <> 0 goto lberr
- Create a table SQL
Declare @tbname sysname
Set @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', 'datetime') Then 'datetime'
When B.Name in ('Money', 'Smallmoney') Then 'Money'
Else B.Name End
From Tempdb..syscolumns a left join tempdb..systypes b on a.xtype = B.xUsertype
WHERE 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, @ SQL
IF @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)
Return
lberr:
EXEC SP_OAGETERRORINFO 0, @ src out, @ DESC OUT
Lbexit:
SELECT CAST (@err as varbinary (4)) AS error number
, @ src AS error source, @ DESC AS error description
SELECT @ SQL, @ Constr, @ fdlist
Go
