Solved

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

Posted on 2006-06-29
2
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

739 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