Sql Query Insert Problem

[jruiseco]

I am importing an Access table into a SQL Server 2000 table. I am importing using a query out of one db and into the other using ColdFusion.

Here's my problem...

Table A (Access) has the data with an id field (primary key). When I import into Table B (SQL Server) the insert does not go into the database sequentially ordered by the primary key. It appears that the data is inserted randomly.

How do I get the data to go in sequentially? I have tried sorting my output query by id asc, but it still goes in bad.

 

[arrowhouse]

Cant help on the cold fustion side, but if what you trying to do is keep the ids from access then you need

SET IDENTITY_INSERT <table name> ON

INSERT INTO (id_field, next_field, ... last_field)
SELECT
id_field, next_field, ... last_field
FROM <table_name>

SET IDENTITY_INSERT <table name> OFF

Hope this points you in the right direction. If you use DTS you can check the enable identity insert on the transform popup.

 

[jruiseco]

I used DTS and got the data in there, but when I open the table using the Enterprise Manager, the data is not sorted by the ID field. It appears that the data went in randomly again even though I checked the 'Enable Identity Insert' box.

Any other ideas?

 

[arrowhouse]

Does your original table have a clustered index? Any other indexes?

 

[rajeessh]

When using DTS the access primary key will not be tranfeered
as Primary key in access

We can write some modules in doing that.
If anyone can throw light pl let us know?

 

[jruiseco]

Arrowhouse,

My only index is the primary key field which is memberid, the field I want to have inserted sequentially.

 

[tlbroadbent]

SQL Server does not guaranteee order of data. By default, data in tables is unordered in the current ANSI standard. MS has attempted to adhere more fully to the standard in each version. You need to order your data in queries to guarantee order of a result set.

If you create a clustered index on key column(s), SQL physically orders the data by the column(s). Although, Microsoft doesn't guarantee that the data in a table will be returned in the order of the clustered index, I've found that it does return ordered sets. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.