Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
796 views
in Technique[技术] by (71.8m points)

c# - Insert into Access from SQL Server

I'm looking to copy a few thousand records from SQL Server into Access in C#. The other direction works using SqlBulkCopy. Is there anything in place to do this in reverse?

I'm trying my best to stay away from looping through each field in each record and building a heinous Insert statement that not only would take forever to run, but would likely crash horribly if anything changes.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

This will run against the MS Access OleConnection connection:

SELECT fld1, fld2 INTO accessTable FROM [sql connection string].sqltable

For example:

SELECT * INTO newtable 
FROM 
[ODBC;Description=Test;DRIVER=SQL Server;SERVER=serverSQLEXPRESS;UID=uid;Trusted_Connection=Yes;DATABASE=Test].table_1

Or to append

INSERT INTO newtable
SELECT *
FROM [ODBC;Description=Test;DRIVER=SQL Server;SERVER=serverSQLEXPRESS;UID=uid;Trusted_Connection=Yes;DATABASE=Test].table_1;

Or with FileDSN

INSERT INTO newtable
SELECT * 
FROM [ODBC;FileDSN=z:docsest.dsn].table_1;

You will need to find the right driver to suit, for example

ODBC;Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword; 

From http://connectionstrings.com works for me, but check out your client version.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...