Access SQL Server database data export

Yesterday, I encountered a difficult problem. I downloaded a software from the Internet, and his database is accessible. After reading it, I feel good. It is suitable for my current project needs. Most of them can meet the needs of my project and I want to take it. However, the database of our project has always been using sqlserver, So I went crazy online. Here I want to talk about the information about the conversion from access to sqlserver:

Database promotion conversion access --- sqlserver:

1. The first thing you need to say is that your database must be installed through the installation wizard. The green version of access does not work.

2. If the database version to be converted is earlier, for example, Access97, you must first convert the database to 2000 or 2003 using access.

3. The steps in the conversion process are not mentioned, and there are a lot of network operations. Then open the converted Access database and run the "tool" --- "database utility" --- "promotion wizard" to convert the Access database to the sqlserver database. After the conversion, open the "Enterprise Manager" of the sqlserver database and you will find that the database is already in it.

 

Database data export problems:

Today, it takes a long time to complete this problem. It's still naive to search for "SQL data export" on Google.

Method 1: Use the Database Export wizard sqlserver to complete the Export

Open "sqlserver Enterprise Manager"> "Data Conversion service"> "export data" ------- click "Next". In the "Select data source" dialog box, select the data source and server, in the "Database" below, select the database to export. Click "Next"-in the "select target" dialog box, select "export to" Database "," text file ", or other options. If you select "text file", enter the file name and path. Click "Next"> "Next" and select "Source" (that is, the name of the table to be exported ". Select the file type and the row/column separator. . Click Next .. Select "Run now" and select "Next... That's done.

Method 2: Use stored procedures ". The above method does not meet my requirements. I want to export all the data in all the tables at a time. The above method does not work. So I searched for examples of stored procedures from the Internet. However, I was stupid, but I still didn't get it out. But I still learned a lot about this stored procedure, so I took it out and shared it for beginners to learn.

Stored Procedures found on the Internet:

 

/* -- Data export Excel
 
Export the queried data to excel, including the field name. The file is a real Excel file.
If the file does not exist, the file is automatically created.
If the table does not exist, the table is automatically created.
Only standard data types can be exported for the sake of universality.

-- Producer build 2003.10 (reference please keep this information )--
Added the paging function.
6.5w entries per page
-- Add by Xie xiaoman --
*/

----------------------------- Compile the Stored Procedure begin -----------------------
Create proc p_exporttb
@ Sqlstr varchar (8000), -- query statement. If order by is used in the query statement, add Top 100 percent.
@ Path nvarchar (1000), -- file storage directory
@ Fname nvarchar (250), -- file name
@ Sheetname varchar (250) = ''-- Name of the worksheet to be created. The default value 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), @ tmpsql varchar (8000)
Declare @ sheetcount int, @ sheetnow int, @ recordcount int, @ recordnow int
Declare @ sheetsql varchar (8000) -- SQL statement used to create a page

Declare @ pagesize int
Set @ pagesize = 65000 -- sheet page size
-- Set @ pagesize = 1000

-- Parameter detection
If isnull (@ fname, '') = ''set @fname='temp.xls'
If isnull (@ sheetname, '') ='' set @ sheetname = Replace (@ fname ,'.','#')

-- Check whether 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 = 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 8.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

-- SQL statement used to create a table
Declare @ tbname sysname

Set @ tbname = '# TMP _' + convert (varchar (38), newid ())

Declare @ tbtmpid nvarchar (50)
Set @ tbtmpid = 'tmp _ '+ convert (varchar (38), newid () +''

-- Temporary table with sequence ID @ tbtmpid
Set @ SQL = 'select identity (INT, 1, 1) as ['+ @ tbtmpid +'],. * Into ['+ @ tbname +'] From (select Top 100 percent B. * From ('+ @ sqlstr +') B)'
Exec (@ SQL)

-- Print (@ SQL)

-- Retrieve the total number of records
Set @ recordcount = @ rowcount
If @ recordcount = 0 return
-- Print @ recordcount

Select @ SQL = '', @ fdlist =''
Select @ fdlist = @ fdlist + ', [' + A. Name + ']'
, @ SQL = @ SQL + ', [' + A. Name + ']'
+ Case
When B. name like '% char'
Then case when a. length> 255 then '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' then 'datetime'
When B. name like '% money' then 'money'
When B. name like '% text' then 'memo'
Else B. Name end
From tempdb .. syscolumns a left join tempdb .. policypes B on A. xtype = B. xusertype
Where B. Name not in ('image', 'uniqueidentifier', 'SQL _ variant', 'varbinary', 'binary ', 'timestamp ')
And a. ID = (select ID from tempdb .. sysobjects where name = @ tbname)
And a. Name <> @ tbtmpid

Set @ fdlist = substring (@ fdlist, 2,8000)
-- Print @ fdlist

-- The number of columns is zero.
If @ rowcount = 0 return

Set @ sheetsql = @ SQL

-- Print @ sheetsql

-- Import data
-- Page number
Set @ sheetcount = ceiling (@ recordcount/cast (@ pagesize as float ))
-- Print @ sheetcount
-- Just a page
If @ sheetcount = 1 begin
-- Print 'only one page'

Set @ SQL = 'create table ['+ @ sheetname
+ '] (' + Substring (@ sheetsql, 2,8000) + ')'


Exec @ err = sp_oamethod @ OBJ, 'execute ', @ out, @ SQL
If @ err <> 0 goto lberr

 

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 + ']')
End

-- Multiple pages

Set @ sheetnow = @ sheetcount
Set @ recordnow = 0
If @ sheetcount> 1 begin
-- Print 'Multiple pages'
While @ sheetnow> 0 begin

-- Create page
Set @ SQL = 'create table ['+ @ sheetname +' _ '+ convert (nvarchar (80), @ sheetcount-@ sheetnow + 1)
+ '] (' + Substring (@ sheetsql, 2,8000) + ')'


Exec @ err = sp_oamethod @ OBJ, 'execute ', @ out, @ SQL
If @ err <> 0 goto lberr

-- Print @ SQL
-- Create page end

If @ sheetnow = @ sheetcount begin
Set @ tmpsql = 'select top '+ STR (@ pagesize) + ''+ @ fdlist + 'from [' + @ tbname + ']'
Set @ SQL = 'openrowset (''microsoft. Jet. oledb.4.0 '', ''excel 8.0; HDR = Yes
; Database = '+ @ path + @ fname + ''', [' + @ sheetname + '_' + convert (nvarchar (80), @ sheetcount-@ sheetnow + 1) + '$])'

Exec ('insert into '+ @ SQL +' ('+ @ fdlist +') '+ @ tmpsql)
End
If @ sheetnow <@ sheetcount begin
Set @ tmpsql = 'select top '+ STR (@ pagesize) + ''+ @ fdlist +' from ['+ @ tbname +'] Where ['+ @ tbtmpid
+ '] Not in (select top' + STR (@ recordnow-@ pagesize) + '[' + @ tbtmpid + '] from [' + @ tbname + '])'

Set @ SQL = 'openrowset (''microsoft. Jet. oledb.4.0 '', ''excel 8.0; HDR = Yes
; Database = '+ @ path + @ fname + ''', [' + @ sheetname + '_' + convert (nvarchar (80), @ sheetcount-@ sheetnow + 1) + '$])'

Exec ('insert into '+ @ SQL +' ('+ @ fdlist +') '+ @ tmpsql)
-- Print (@ tmpsql)
-- Exec (@ tmpsql)
End

-- Print (@ tmpsql)

-- Exec (@ tmpsql)

Set @ recordnow = @ pagesize * (@ sheetcount-@ sheetnow + 2)
Set @ sheetnow = @ sheetnow-1
End
End

Set @ SQL = 'drop table ['+ @ tbname +']'
Exec (@ SQL)

Exec @ err = sp_oadestroy @ OBJ

-- End return
Return

Lberr:
Exec sp_oageterrorinfo 0, @ SRC out, @ DESC out
Lbexit:
Select cast (@ err as varbinary (4) as error code
, @ SRC as error source, @ DESC as error description
Select @ SQL, @ constr, @ fdlist

Set quoted_identifier off

Go
-------------------------------- Write end ----------------- In the Stored Procedure -----------------

 

The above Code only needs to get the query analyzer and execute the following code. This stored procedure is stored in the system.

 

Call the stored procedure:

Exec stored procedure name query statement, save location, keep name

Instance:

Exec p_exporttb 'select * from department ', 'c:/', 'department'

----------------------------------------

The preceding table can be exported, but all tables cannot be exported. I have studied it for a long time and I have not yet obtained it. Please, however, we can provide you with the following things. Below we can achieve a loop to get the table name in the database.

Declare @ path varchar (100)
Declare @ filename varchar (100)
Declare @ SQL varchar (100)
Declare @ esql varchar (100)
Set @ SQL = 'select * from'
Declare @ pre varchar (100)
Set @ pre = 'P _ exporttb'
Set @ Path =''
Declare ABC cursor
Select [name] From sysobjects where xtype = 'U'
Open ABC
Fetch next from ABC
Fetch ABC into @ filename
While @ fetch_status = 0
Begin

-- Set @ esql = @ pre + ''+ @ SQL + @ filename + ',' + @ path + ',' + @ filename
-- Exec (@ esql)

Print @ filename
Fetch next from ABC
Fetch ABC into @ filename
End
Close ABC
Deallocate ABC

 

Method 3: use external tools. Discuss the problem with colleagues during lunch. They said they had specialized tools. So I searched the internet for a software called "sqltotext. It is really easy to use, and the function is enough for my own use. You can search for it .. It is not difficult to understand the English version of the interface ..