将数据从SQL Server移至MS Access mdb [英] Move data from SQL Server to MS Access mdb

问题描述

我需要将某些信息从我们的SQL Server数据库转移到MS Access数据库中.我已经有了访问表结构的设置.我正在寻找一个纯SQL解决方案；我可以直接从ssms运行，而不必在c#或vb中编写任何代码.

I need to transfer certain information out of our SQL Server database into an MS Access database. I've already got the access table structure setup. I'm looking for a pure sql solution; something I could run straight from ssms and not have to code anything in c# or vb.

如果我首先设置odbc数据源，我知道这是可能的.我想知道如果没有odbc数据源是否可以做到这一点?

I know this is possible if I were to setup an odbc datasource first. I'm wondering if this is possible to do without the odbc datasource?

推荐答案

如果您想要纯" SQL解决方案，我的建议是使用

If you want a 'pure' SQL solution, my proposal would be to connect from your SQL server to your Access database making use of OPENDATASOURCE.

然后可以使用T-SQL编写INSERT指令.它看起来像:

You can then write your INSERT instructions using T-SQL. It will look like:

INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=myDatabaseName.mdb')...[myTableName] (insert instructions here)

INSERT的复杂性将取决于SQL数据库和ACCESS数据库之间的差异.如果表和字段具有相同的名称，这将非常容易.如果模型不同，则可能必须先构建特定的查询才能定型"数据，然后才能将其插入MS-Access表和字段.但是，即使它变得复杂，也可以通过纯SQL"对其进行处理.

The complexity of your INSERTs will depend on the differences between SQL and ACCESS databases. If tables and fields have the same names, it will be very easy. If models are different, you might have to build specific queries in order to 'shape' your data, before being able to insert it into your MS-Access tables and fields. But even if it gets complex, it can be treated through 'pure SQL'.

这篇关于将数据从SQL Server移至MS Access mdb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！