Solved

From MS Access 2003 table to "identical" MS SQL Server 2005 table

Posted on 2006-06-29
2
248 Views
Last Modified: 2008-02-01

Hello there!

When I try

SET IDENTITY_INSERT Stocks ON
INSERT INTO Stocks
SELECT Access.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\PathToDatabase\DatabaseName.mdb';'admin';'',Stocks) As Access

I get the following error:

Msg 8101, Level 16, State 1, Line 5
An explicit value for the identity column in table 'Stocks' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Please explain the error and correct what is wrong.

Note: there are 2 identical Primary Key´s at Access and SQL Server, both called StockID.

Thanks,
fskilnik.


0
Comment
Question by:fskilnik
2 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 300 total points
ID: 17011912
you need to specifically name your columns

SET IDENTITY_INSERT Stocks ON
INSERT INTO Stocks (StockID, columnA, columnB, ...)
SELECT StockID, columnA, columnB, ...
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\PathToDatabase\DatabaseName.mdb';'admin';'',Stocks) As Access
0
 

Author Comment

by:fskilnik
ID: 17011969

Perfect, BriCrowe. Thanks for the quick and explicit reply.

Please have a look at my other (I guess harder) question, if you don´t mind!
Here: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21902340.html

Thanks a lot,
fskilnik

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now