SQL Server data import and export commands

1. Import. DBF (FoxPro data file) to SQL Server:

 

------------- If the SQL table that accepts the imported data does not exist, create

Select * Into the SQL table name to be generated from OpenRowSet ('msdasql ', 'driver = Microsoft Visual FoxPro driver; sourcetype = DBF; sourcedb = C :/', 'select * From DBF table name. dbf ')

 

Instance:

-- Import MDF data files
Select * into t_tdd from OpenRowSet ('msdasql ',
'Driver = Microsoft Visual FoxPro driver; sourcetype = DBF; sourcedb = D:/My Documents/desktop/Ningxia first data', 'select * from [t_tdd.dbf] ')

 

Before executing the command, download a Microsoft ole db provider for Visual FoxPro installation.

 

When you use this command, the following error message is displayed:

SQL Server blocks access to the statement 'openrowset/OpenDataSource 'of the 'ad hoc distributed queries' component because this component has been disabled as part of the server's security configuration. The system administrator can enable 'ad hoc distributed queries 'by using sp_configure '. For more information about enabling 'ad hoc distributed querys', see "peripheral application configurator" in SQL Server books online ".

 

Query relevant information and find the solution:

Enable ad hoc distributed queries:
Exec sp_configure 'show advanced options', 1
Reconfigure
Exec sp_configure 'ad hoc distributed queries ', 1
Reconfigure
After use, disable ad hoc distributed queries:
Exec sp_configure 'ad hoc distributed queries ', 0
Reconfigure
Exec sp_configure 'show advanced options', 0
Reconfigure

 

-- If the SQL table that accepts the imported data already exists
Insert into an existing SQL table name select * From OpenRowSet ('msdasql ', 'driver = Microsoft Visual FoxPro driver; sourcetype = DBF; sourcedb = C :', 'select * from AA. dbf ')

 

The fastest way to connect to DBF with sqlserver
Run
Select * into BmK
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "E:/share"; user id = admin; Password =; extended properties = DBASE 5.0 ')... BmK
In this way, you can import the BmK. DBF table in E:/share to sqlserver,
The fastest speed

Note: OpenDataSource and OpenRowset do not accept parameter variables.

 

Ii. Import and export data from SQL Server and Excel

 

1. query Excel Data in SQL Server:

-- ===================================================== ====================

Select *

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "C:/book1.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')...

[Sheet1 $]

 

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

2. Import Excel Data to SQL Server:

-- ===================================================== ====================

Select * into newtable

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "C:/book1.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')...

[Sheet1 $]

 

Instance:

Select * into newtable

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "C:/finance/account.xls"; user id = admin; Password =; extended properties = Excel

5.0 ')... xactions

3. Export the data queried in SQL Server into an Excel file

-- ===================================================== ====================

T-SQL code:

Exec master.. xp_mongoshell 'bcp database name. DBO. Table name out C:/temp.xls-C-Q-s "servername"-U "sa"-P ""'

Parameter: s indicates the SQL server name, u indicates the user, and P indicates the password.

Note: You can also export text files and other formats.

 

Instance: exec master .. xp_mongoshell 'bcp saletesttmp. DBO. cusaccount out C:/temp1.xls-C-Q-

S "pmserver"-U "sa"-P "sa "'

 

Exec master .. xp_mongoshell 'bcp "select au_fname, au_lname from pubs .. authors order

Au_lname "queryout C:/authors.xls-C-sservername-USA-ppassword'

 

 

4. insert data to excel in SQL Server:

-- ===================================================== ====================

Insert into OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "C:/temp.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... Table1

(A1, A2, A3) values (1, 2, 3)

 

T-SQL code:

Insert

OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Extended properties = Excel 8.0; Data Source = C:/training/inventur.xls ')... [filiale1 $]

(Bestand, Produkt) values (20, 'test ')

 

Select * into excelimport from OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'Excel 8.0; database = F:/class and teaching materials/05 net Class 1 Documents/test.xls ', 'select * from [sheet1 $]')

 

 

Iii. SQL Server and access data import and export

General data import and export:

Use the DTS wizard to migrate your access data to SQL Server. You can use these steps:

1. On the Tools menu in SQL Server Enterprise Manager, select data transformation

2 services, and then select czdimport data (import data ).

3. In the choose a data source dialog box, select Microsoft Access as the source, and then click

Enter the file name of Your. mdb database (. MDB File Extension) or browse to find the file.

4. In the choose a destination (select target) dialog box, select Microsoft ole db prov ider for SQL

Server, select the database server, and then click the required authentication method.

5. In the specify table copy (specify table copy) or query dialog box, click Copy tables (copy table)

).

6. In the select Source tables dialog box, click Select All ). Next, complete.

 

Use a Transact-SQL statement to import and export data:

1. query access data in SQL Server:

-- ===================================================== ====================

Select *

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "C:/DB. mdb"; user id = admin; Password = ')... table name

2. Import access to SQL Server

-- ===================================================== ====================

Run in SQL Server:

Select *

Into newtable

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "C:/DB. mdb"; user id = admin; Password = ')... table name

3. insert data in the SQL Server table to the Access Table

-- ===================================================== ====================

Run in SQL Server:

Insert into OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "C:/DB. mdb"; user id = admin; Password = ')... table name

(Column name 1, column name 2)

Select column name 1, column name 2 from SQL table

 

Instance:

Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ',

'C:/DB. mdb '; 'admin'; '', test)

Select ID, name from test
Note:

Here, I want to import my sqlserver test table data to the test table in Access Database C:/DB. MDB. The test table in Access database must exist first. The test is successful.

Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'c:/trade. mdb'; 'admin'; '', table name)

Select *

From sqltablename
Summary: with the above statements, we can easily convert data in SQL Server, access, and Excel spreadsheet software, which provides us with great convenience!

 

 

The red font content is an example verified by myself. Others are excerpted from the Internet.