search
HomeDatabaseMysql TutorialAccess转成Sql 2008步骤,同时解决自动编号问题,主键,id数值不

1.开始-Microsoft SQL Server 2008-导入和导出数据(32 位) 2.选择数据源,数据源里选择Microsoft Access,浏览选mdb文件,下一步。 3.选择目标,数据库点新建,名称自定,下一步。 4.复制一个或多个表或视图的数据,下一步。 5.选择源表和源视图,全勾选,选

1.开始->Microsoft SQL Server 2008->导入和导出数据(32 位)
2.选择数据源,数据源里选择Microsoft Access,浏览选mdb文件,下一步。

Access转成Sql 2008步骤,同时解决自动编号问题,主键,id数值不
3.选择目标,数据库点新建,名称自定,下一步。
Access转成Sql 2008步骤,同时解决自动编号问题,主键,id数值不

4.复制一个或多个表或视图的数据,下一步。

Access转成Sql 2008步骤,同时解决自动编号问题,主键,id数值不
5.选择源表和源视图,全勾选,选第一个表,点编辑映射;

Access转成Sql 2008步骤,同时解决自动编号问题,主键,id数值不
出现列映射框,点击编辑SQL,

Access转成Sql 2008步骤,同时解决自动编号问题,主键,id数值不
弹出SQL语句编辑框

Access转成Sql 2008步骤,同时解决自动编号问题,主键,id数值不

在[ID] int NOT NULL,中间加入IDENTITY(1,1),后显示为:[ID] int IDENTITY(1,1) NOT NULL,

Access转成Sql 2008步骤,同时解决自动编号问题,主键,id数值不

确定,确定,回到选择源表和源视图框,继续修改其它表,完成后,下一步。
6.立即运行,下一步。
7.完成。
最后用SQL Server Management Studio连接数据库查看刚转换的数据中的表,ID列。

Access转成Sql 2008步骤,同时解决自动编号问题,主键,id数值不

标识为True,标识种子为1,标识增量为1,

Access转成Sql 2008步骤,同时解决自动编号问题,主键,id数值不

以后添加数据就跟Access里的自动编号效果一样了。

补充:关于主键设置,可以第5步:[ID] int IDENTITY(1,1) NOT NULL, 里插入:Primary key,语句为:

[ID] int Primary key IDENTITY(1,1) NOT NULL,

最后,如果要复制来的id数据不重置(转换后id重新从1来编号),可以勾选第5步列表映射框里:启用标识插入。

Access转成Sql 2008步骤,同时解决自动编号问题,主键,id数值不

 

 

 

====ACCESS转SQLSERVER后代码需要修改的语句=====

 

1,对于日期字段字段 

 access表示为:#1981-28-12# 

 SQLSERVER2000表示为:‘‘1981-02-12‘‘ 

2,SQL语句区别,select ,update 在对单表操作时都差不多, 

 但多表操作时update语句的区别ACCESS与SQLSERVER中的Update语句对比: 

 SQLSERVER中更新多表的Update语句: 

 Update Tab1 

 SET a.Name = b.Name  FROM Tab1 a,Tab2 b  Where a.ID = b.ID; 

 同样功能的SQL语句在ACCESS中应该是 

 Update Tab1 a,Tab2 b  SET a.Name = b.Name  Where a.ID = b.ID; 

 即:ACCESS中的Update语句没有FROM子句,所有引用的表都列在Update关键字后. 

 更新单表时:都为: 

 Update table1 set ab=‘12‘,cd=444 where .... 

3,delete语句 

 access中删除时用:delete * from table1 where a>2 即只要把select 语句里的select 换成delete就可以了。 

 sqlserve 中则为: delete from table1 where a>2 即没有*号 

4,as 后面的计算字段区别 

 access中可以这样:select a,sum(num) as kc_num,kc_num*num as all_kc_num 即可以把AS后的字段当作一个数据库字段参与计算。 

 sqlserver 中则为:select a,sum(num) as kc_num,sum(num)*num as all_kc_num 即不可以把AS后的字段当作一个数据库字段参与计算。 

5,[.]与[!]的区别 

 access中多表联合查询时:select tab1!a as tab1a,tab2!b tab2b from tab1,tab2 ,中间的AS可以不要。 

 sqlserve 中则:select tab1.a as tab1a,tab2.b tab2b from tab1,tab2 ,中间的AS可以不要。 

6,联合查询时, 

   access中多表联合查询:‘select a,b from( 

select a,b from tab1 where a>3 union select c,d from tab2 ) group by a,b 

sqlserve 中则‘select a,b from( 

select a,b from tab1 where a>3 union select c,d from tab2 ) tmptable group by a,b即要加一个虚的表tmptable,表名任意。--- 

7,access升级到sqlserver时, 

  可以用sqlserver的数据导入工具导入数据,但要做必要的处理。 

  access中的自动编号,不会自动转换SQL中的自动编号,只能转换为int型,要把它手工改成标识字段,种子为1,把所有导入被sqlserver转化成的以n开头的字段类型的n去掉,如nvarchar->varchar.把需要有秒类型的日期字段改成datatime类型(SQL会把所有的日期开转化成smalldatetime型) 

8,true与1=1 

 access用where true表示条件为真, 

sqlserver用where 1=1表示条件为真 

9,判断字段值为空的区别 

普通空: 

Access和sql server一样 where code is null 或 where code is nol null 

条件空: 

Access:iif([num] is null,0,[num]) 或 iif([num] is null,[num1],[num]) 

SQLServer: isnull([num],0) 或 isnull([num],[num1]) 

10,SQL语句取子串的区别 

access:MID(字段,n1,[n2]),LEFT(字段,n),RIGHT(字段,n) 

如:select left(cs1,4)+‘-‘+cs2 as cs3 

SQLServer: SUBSTRING(expression, start, length) 

如:select substring(cs1, 1, 2) + substring(cs1, 4, 2) + ‘-‘ + cs2 as cs3 

补充: 

ACCESS与SQL2000的SQL语句有区别的 

比如now()在SQL2000中必须改为getdate() 

还有关键词必须加[] ,像ACCESS中字段名用name SQL20000必须加[name] 否则出错 

数据库连接字重新配置 

1. access 转sql 数据库后需要建立各表关键字以及递增量设置部分数据类型需要重新定义 

2. now() 函数是可接受的,但在日期比较过程中需要用 getdate() 

3. 保留字需要加 [] 

4. 单双引号需要转变 

5. 遵循标准sql定义(最关键的一条) 

看看MSSQLServer联机丛书。 

1.ACCESS的数据库中的自动编号类型在转化时,sql server并没有将它设为自动编号型,我们需在SQL创建语句中加上identity,表示自动编号! 

2.转化时,跟日期有关的字段,SQL SERVER默认为smalldatetime型,我们最好将它变为datetime型,因为datetime型的范围比smalldatetime型大。我遇见这种情况,用smalldatetime型时,转化失败,而用datetime型时,转化成功。 

3.对此两种数据库进行操作的sql语句不全相同,例如:在对ACCESS数据库进行删除纪录时用:"delete * from user where id=10",而对SQL SERVER数据库进行删除是用:"delete user where id=10". 

4.日期函数不相同,在对ACCESS数据库处理中,可用date()、time()等函数,但对 

SQL SERVER数据库处理中,只能用datediff,dateadd等函数,而不能用date()、time()等函数。 

5.在对ACCESS数据库处理中,sql语句中直接可以用一些VB的函数,像cstr()函数,而对SQL SERVER数据库处理中,却不能用。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How does MySQL's licensing compare to other database systems?How does MySQL's licensing compare to other database systems?Apr 25, 2025 am 12:26 AM

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

When would you choose InnoDB over MyISAM, and vice versa?When would you choose InnoDB over MyISAM, and vice versa?Apr 25, 2025 am 12:22 AM

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

Explain the purpose of foreign keys in MySQL.Explain the purpose of foreign keys in MySQL.Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

What are the different types of indexes in MySQL?What are the different types of indexes in MySQL?Apr 25, 2025 am 12:12 AM

There are four main index types in MySQL: B-Tree index, hash index, full-text index and spatial index. 1.B-Tree index is suitable for range query, sorting and grouping, and is suitable for creation on the name column of the employees table. 2. Hash index is suitable for equivalent queries and is suitable for creation on the id column of the hash_table table of the MEMORY storage engine. 3. Full text index is used for text search, suitable for creation on the content column of the articles table. 4. Spatial index is used for geospatial query, suitable for creation on geom columns of locations table.

How do you create an index in MySQL?How do you create an index in MySQL?Apr 25, 2025 am 12:06 AM

TocreateanindexinMySQL,usetheCREATEINDEXstatement.1)Forasinglecolumn,use"CREATEINDEXidx_lastnameONemployees(lastname);"2)Foracompositeindex,use"CREATEINDEXidx_nameONemployees(lastname,firstname);"3)Forauniqueindex,use"CREATEU

How does MySQL differ from SQLite?How does MySQL differ from SQLite?Apr 24, 2025 am 12:12 AM

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

What are indexes in MySQL, and how do they improve performance?What are indexes in MySQL, and how do they improve performance?Apr 24, 2025 am 12:09 AM

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Explain how to use transactions in MySQL to ensure data consistency.Explain how to use transactions in MySQL to ensure data consistency.Apr 24, 2025 am 12:09 AM

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

Atom editor mac version download

Atom editor mac version download

The most popular open source editor