Access 2k -> SQL Server 2k
My client has an app that is A2k FE with A2k BE. They have asked me
to move the BE to SQL Server.
I have a bit of experience with SQL Server, and I'm happy with
scripting the database etc.
However, when it comes time to move the data itself, I have a teensy
little concern.
Let's take our typical Customers -> Orders relation, where CustomerID
is the Foreign Key in the Orders table.
Let's say I have 4 customers, but I used to have 6. The CustomerID is
an AutoNum column, and they are 1, 2, 4, 6. However, when I insert
these records into the matching SQL Server table with an Identity
column, they will (presumably) be 1, 2, 3, 4.
So what can I do about the matching orders? Is there any alternative
to this, which seeems very long winded.
1. Insert the records as above, but copy the OLD Access AutoNum
column (called OldCustomerID) into a temporary column in the new SQL
Server table for both the Customers and Orders tables.
2. Insert the Orders records into the new SQL Server table.
3. Run an update query thus:
UPDATE O
SET CustomerID = C.CustomerID
FROM Orders O
INNER JOIN Customers C ON O.OldCustomerID = C.OldCustomerID
Can I do this any easier? For example, is there any way in SQL Server
to maintain the values of the AutoNum field in the Insert but have
them still be Identity fields? I know that I can import the AutoNum
data into a simple int column, and once the import is complete, turn
ON the identity, but is this reliable? Might I lose any data
integrity?
TIA
Edward
--
The reading group's reading group: http://www.bookgroup.org.uk 3 1832
Edward,
You have to turn identity insert on and off when adding values to a SQL
Server identity field.
SET IDENTITY_INSERT TableName ON;
.... issue your inserts
SET IDENTITY_INSERT TableName OFF;
This will maintain your existing Access AutoNumber IDs.
"Edward" <te********@hot mail.com> wrote in message
news:25******** *************** ***@posting.goo gle.com... Access 2k -> SQL Server 2k
My client has an app that is A2k FE with A2k BE. They have asked me to move the BE to SQL Server.
I have a bit of experience with SQL Server, and I'm happy with scripting the database etc.
However, when it comes time to move the data itself, I have a teensy little concern.
Let's take our typical Customers -> Orders relation, where CustomerID is the Foreign Key in the Orders table.
Let's say I have 4 customers, but I used to have 6. The CustomerID is an AutoNum column, and they are 1, 2, 4, 6. However, when I insert these records into the matching SQL Server table with an Identity column, they will (presumably) be 1, 2, 3, 4.
So what can I do about the matching orders? Is there any alternative to this, which seeems very long winded.
1. Insert the records as above, but copy the OLD Access AutoNum column (called OldCustomerID) into a temporary column in the new SQL Server table for both the Customers and Orders tables.
2. Insert the Orders records into the new SQL Server table.
3. Run an update query thus:
UPDATE O SET CustomerID = C.CustomerID FROM Orders O INNER JOIN Customers C ON O.OldCustomerID = C.OldCustomerID
Can I do this any easier? For example, is there any way in SQL Server to maintain the values of the AutoNum field in the Insert but have them still be Identity fields? I know that I can import the AutoNum data into a simple int column, and once the import is complete, turn ON the identity, but is this reliable? Might I lose any data integrity?
TIA
Edward -- The reading group's reading group: http://www.bookgroup.org.uk
Forgot to say something, which I'm sure you know: even with IDENTITY_INSERT ,
you still have to populate your tables in the correct order so as not to
violate referential integrity. Parent table first, then child, etc.
"DFS" <no****@nospam. com> wrote in message
news:vu******** ****@corp.super news.com... Edward,
You have to turn identity insert on and off when adding values to a SQL Server identity field.
SET IDENTITY_INSERT TableName ON; ... issue your inserts SET IDENTITY_INSERT TableName OFF;
This will maintain your existing Access AutoNumber IDs. "Edward" <te********@hot mail.com> wrote in message news:25******** *************** ***@posting.goo gle.com... Access 2k -> SQL Server 2k
My client has an app that is A2k FE with A2k BE. They have asked me to move the BE to SQL Server.
I have a bit of experience with SQL Server, and I'm happy with scripting the database etc.
However, when it comes time to move the data itself, I have a teensy little concern.
Let's take our typical Customers -> Orders relation, where CustomerID is the Foreign Key in the Orders table.
Let's say I have 4 customers, but I used to have 6. The CustomerID is an AutoNum column, and they are 1, 2, 4, 6. However, when I insert these records into the matching SQL Server table with an Identity column, they will (presumably) be 1, 2, 3, 4.
So what can I do about the matching orders? Is there any alternative to this, which seeems very long winded.
1. Insert the records as above, but copy the OLD Access AutoNum column (called OldCustomerID) into a temporary column in the new SQL Server table for both the Customers and Orders tables.
2. Insert the Orders records into the new SQL Server table.
3. Run an update query thus:
UPDATE O SET CustomerID = C.CustomerID FROM Orders O INNER JOIN Customers C ON O.OldCustomerID = C.OldCustomerID
Can I do this any easier? For example, is there any way in SQL Server to maintain the values of the AutoNum field in the Insert but have them still be Identity fields? I know that I can import the AutoNum data into a simple int column, and once the import is complete, turn ON the identity, but is this reliable? Might I lose any data integrity?
TIA
Edward -- The reading group's reading group: http://www.bookgroup.org.uk
Unfortunately there is a bug in MSSQL 2000 so that 'set identity_insert on'
does not work as expected.
My company managed to get a hotfix from Microsoft, so see if you can get it
too or use the other method from the article before
Regards, Robert
--
"DFS" <no****@nospam. com> schrieb im Newsbeitrag
news:vu******** ****@corp.super news.com... Edward,
You have to turn identity insert on and off when adding values to a SQL Server identity field.
SET IDENTITY_INSERT TableName ON; ... issue your inserts SET IDENTITY_INSERT TableName OFF;
This will maintain your existing Access AutoNumber IDs. "Edward" <te********@hot mail.com> wrote in message news:25******** *************** ***@posting.goo gle.com... Access 2k -> SQL Server 2k
My client has an app that is A2k FE with A2k BE. They have asked me to move the BE to SQL Server.
I have a bit of experience with SQL Server, and I'm happy with scripting the database etc.
However, when it comes time to move the data itself, I have a teensy little concern.
Let's take our typical Customers -> Orders relation, where CustomerID is the Foreign Key in the Orders table.
Let's say I have 4 customers, but I used to have 6. The CustomerID is an AutoNum column, and they are 1, 2, 4, 6. However, when I insert these records into the matching SQL Server table with an Identity column, they will (presumably) be 1, 2, 3, 4.
So what can I do about the matching orders? Is there any alternative to this, which seeems very long winded.
1. Insert the records as above, but copy the OLD Access AutoNum column (called OldCustomerID) into a temporary column in the new SQL Server table for both the Customers and Orders tables.
2. Insert the Orders records into the new SQL Server table.
3. Run an update query thus:
UPDATE O SET CustomerID = C.CustomerID FROM Orders O INNER JOIN Customers C ON O.OldCustomerID = C.OldCustomerID
Can I do this any easier? For example, is there any way in SQL Server to maintain the values of the AutoNum field in the Insert but have them still be Identity fields? I know that I can import the AutoNum data into a simple int column, and once the import is complete, turn ON the identity, but is this reliable? Might I lose any data integrity?
TIA
Edward -- The reading group's reading group: http://www.bookgroup.org.uk
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Stephen Ghelerter |
last post by:
I am moving a web site with a MySql database to another server. Can I create
a database on the new server with the same name and then move the tables
there, or is life not that simple? Or can I create tables with no data and
then replace them with the files from the other server? I know I can export
them as text files and them import them, but that is a lot of trouble.
Also, using my FTP program I can't access the mysql folder on the...
|
by: davidmdalle |
last post by:
Hello, I have been having a bit of trouble finding help on the safest
way to move data files to a different disk on the same server. Most
help is about moving data files to a different sqlserver. I just want
to move the files to a different drive on the same server. Any help
would be appreciated.
Thanks,
David
|
by: Mr. x |
last post by:
.... The second time I am sending this email ...
.... I want a good solution, please.
.... Thanks :)
What I need is moving xml data from client to server & vice versa, but not
using xml file.
I did something like this :
In server (VBScript) :
-----------------------------
|
by: Ron Mexico |
last post by:
Hi,
Currently have an app that maintain that is written in VB6 and uses an
access db (backend db only used as a data store not writing to the db).
This app is a client app not server multi-teir app. It multi-form app that
takes in a lot of user input and gives out high end calculations
(engineering app). This app is starting to grow beyond the design and
becoming very hard to maintian. I was considering moving it to C#. All of
our new...
|
by: Woody Splawn |
last post by:
I have been using SQL Server 2000 on my stand-alone machine as a back-end to
a VS.net application. It is time to switch environments and take the
application to the customer. I need to install SQL Server 2000 on the
server of the client's local area network, which is a Windows 2000 machine.
I am
concerned about doing this right. On my own machine I did the install
incorrectly at first and had to re-install. I had a very hard time...
| |
by: fuzzybr80 |
last post by:
I am using MySQL 5.0 with a number of innodb tables whose ibdata files
are growing quite quickly and filling up the /var partition (file is
/var/mysql/ibdata1). Earlier on I followed instructions in the docs to
create a new ibdata file on a bigger partition
/disk2/var/mysql/ibdata2) and set that to autoextend instead.
However I would like to reclaim the disk space on /var by moving the
existing ibdata1 file to /disk2/var/mysql/ibdata1....
|
by: UJ |
last post by:
I've got a working web service that we are moving to another machine and now
I suddenly get the following error.
While transferring the files, got the following message:
System.Web.Services.Protocols.SoapException: Server was unable to read
request. ---System.InvalidOperationException: There is an error in XML
document (1, 1422). ---System.Data.DataException: Undefined data type:
'anyType'.
at System.Data.XSDSchema.FindNameType(String...
|
by: =?Utf-8?B?TW9iaWxlTWFu?= |
last post by:
Hello everyone:
I am looking for everyone's thoughts on moving large amounts (actually, not
very large, but large enough that I'm throwing exceptions using the default
configurations).
We're doing a proof-of-concept on WCF whereby we have a Windows form client
and a Server. Our server is a middle-tier that interfaces with our SQL 05
database server.
|
by: rabbitrun |
last post by:
Hi Everyone,
I work for a financial company. I am planning to give a presentation
to rest of the development team (15 people) here on moving server side
logic to client-side javascript for an internal intranet application
rewrite. This approach will definitely stir up hot debate from
hardcore server-side Java folks who wants to do UI stuff even on the
server!. Since I am pretty much known as the JS or UI Guy of the
group, my Boss...
|
by: Nishanthmarathe |
last post by:
Hi There!!!
I am new to C# world.
I am getting huge chunk of data from a 3rd party applications thru SOAP request and updating to my SQL Server.
I cant implement Progress bar as there is no way to know how much data i will get back and its a complicated process.
So i decided to use a dummy progress bar, that is 5 moving dots, Which in turn gives user feeling of some progress happening.
But if i comment the actual getting data part...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |