Reading Excel and the BPC language in SQL

1  　  -- open Import function 　 2     execsp_configure'Show advanced Options',13     Reconfigure4     execsp_configure'Ad Hoc Distributed Queries',15     Reconfigure6     --allow ace.oledb.12 to be used in the process7     EXECMaster.dbo.sp_MSset_oledb_prop N'microsoft.ace.oledb.12.0'N'allowinprocess',18     --Allow dynamic Parameters9     EXECMaster.dbo.sp_MSset_oledb_prop N'microsoft.ace.oledb.12.0'N'dynamicparameters',1

1 --If the table is not present, then use the SELECT * into table name ...2 --read Excel data, note that Excel must be closed in advance 3 SELECT * into Temp  from OpenDataSource('microsoft.ace.oledb.12.0','Data source= "C:\Users\amanda\Desktop\ new Microsoft Excel worksheet. xlsx"; User id=admin; password=; Extended Properties=excel 12.0')...[sheet1$]  

--If a table in the database is the INSERT into table name ...
INSERT into Temp1 SELECT * from OpenDataSource ( ' microsoft.ace.oledb.12.0 ', ' Data source= ' C:\Users\amandage\Desktop\ new Microsoft Excel worksheet. xlsx "; User id=admin; password=; Extended properties=excel 12.0 ') ... [sheet1$]

1 -- read  Access File 2 Select *  from OpenRowSet ('microsoft.ace.oledb.12.0',';d atabase=c:\users\amanda\ Desktop\ Mercedes-National quote 1204.mdb','select * from content')

 1 --  SQL write to Excel 2--1. Table must exist 3--2.excel table requires column name exists4 INSERT  into  5 OPENROWSET('microsoft.ace.oledb.12.0','Excel 12.0; Hdr=yes;database=c:\users\amanda\desktop\ new Microsoft Excel worksheet. xlsx',6             'SELECT V1 from [sheet1$]')  7 SELECT [Week Time]  from [dbo].[Table_1]8 9  --Write AllTen INSERT  into  One OPENROWSET('microsoft.ace.oledb.12.0','Excel 12.0;database=c:\users\amanda\desktop\ new Microsoft Excel worksheet. xlsx', A             'SELECT * FROM [sheet1$]')  - SELECT *  from [dbo].[Table_1] 

 -- write data using BPC --1. Do not have to change the line, do not have ODB and so on--2. Write to remote IPEXECMaster.. xp_cmdshell'bcp "select * from SG". [Table_1] "Queryout C:\11.xls -c-s amanda-pc1-t'---T indicates that the security output does not require an account password.GO---Write to local Excel--1. Must be in XLS format--2. Wait.ExecMaster.. xp_cmdshell'bcp "SELECT * from SG". [Table_1]"queryout"C:\test2.xls"-c-t'--write to remote and fill in your own server and password accountEXECMaster.. xp_cmdshell'bcp "Select Distinct url_shprice_series from Ymcuuubi. qkdskfdl_spider"queryout \\10.16.**.**\spiderurl\basicinfo_shprice.txt -c-s 10.16.**.** -u Server logon name -P password '--the black one needs to be filled out by himself. GO

1 // Distributed Queries: 2 exec ' Ad Hoc Distributed Queries ',03reconfigure4exec'Show Advanced options',05reconfigure

Reading Excel and the BPC language in SQL