SQL Server tables and Excel and access data export

1. Export SQL Server as Excel: To use T-SQL statements to export directly to the Excel worksheet, you have to borrow an extended stored procedure from the SQL Server Manager: xp_cmdshell, this process is used to execute a given command string in the form of an operating system command line interpreter and return any output in the form of text lines." The following is a definition example: Exec master.. xp_mongoshell 'bcp database name. DBO. Table name out c: \ book3.xls-C-Q-s "servername"-U "sa"-P ""' -- Parameter: s indicates the SQL server name; U indicates the user name; P indicates the password. -- Note: in fact, the format exported in this process is essentially the text format. If you do not believe it, change it in the exported Excel table and save it. The actual example and description are as follows: /* If you want to export the entire table to excel */ Exec master .. xp_mongoshell 'bcp northwind. DBO. Orders out c: \ book1.xls-C-Q-s "(local)"-U "sa"-P ""' -- Note that the northwind. DBO. orders in the sentence is the database name + owner + Table Name -- Directly export the "out" parameter ------------------------------------------- /* If you want to use a query to export some fields to excel */ Exec master .. xp_mongoshell 'bcp "select orderid, cutomerid, freight from northwind .. orders order by orderid "queryout c: \ book2.xls-C-s" (local) "-U" sa "-P ""' -- A query statement is added to BCP and enclosed in double quotation marks. -- Use the keyword "queryout" to query 2. Import SQL Server tables in Excel: In SQL Server, an opendatesource function is defined to reference the infrequently accessed OLE DB data sources. Our data import operations are built on this function. First look at an example of T-SQL help, the description is as follows: -- The following is an example of a query. It queries an Excel spreadsheet through the ole db provider for jet. Select * From OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'Data source = "C: \ finance \ account.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions If you directly reference this example for query, It is passable. The key lies in the two points in the statement that need to be modified. One point is at the data source, which is the actual storage location of the Excel table in double quotation marks. You need to modify the actual full path of the Excel table you want to query; the second is the final... xactions, in fact, represents some of the actions to be performed. Here we will talk about it. modify it to the worksheet name (with a $) in the Excel table surrounded by brackets, for example, [sheet1 $]. Of course, you can also change Excel 5.0 to excel 8.0, because 5.0 is an old version. The following is an example: /* 1. The data inserted into excelis is saved to the current SQL database table. (assume that the C disk has an exceltable book2.xls. in book2.xls, there is a worksheet sheet1, and sheet1 has two columns of ID and fname. At the same time, the SQL ):*/ Insert into test select ID, fname From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C: \ book2.xls"; user id = admin; Password =; extended properties = Excel 8.0 ')... [sheet1 $] -- If select * is used, the column order will be disordered, the data content will also be disordered, and insertion will fail. Therefore, the column name is specified. ----------------------- /* 2. Insert data in the Excel table to the SQL database and create an SQL table (the definition and content of Excel are the same as above ):*/ Select convert (INT, ID) as ID, fname into test7 From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C: \ book2.xls"; user id = admin; Password =; extended properties = Excel 8.0 ')... [sheet1 $] -- Convert the display type in the Select column using convert. Otherwise, the data type is not as expected. Access is basically the same as Excel, but the extended properties Declaration must be removed.