June 13, 2006 at 4:29 am
hi everyone...
i have a problem (the cause of it i don't know or understand it !!)...
i'am trying to import into a table all the recors from another...
the first example :
INSERT INTO ACCEVENTIMPORT
SELECT * FROM _ACC
the problem: in the first table (_ACC) the data are order correctly but afet insert inthe second one (ACCEVENTIMPORT) the data ar order ...some how different !?!!?!
the second ex : i try to use a cursor to parse all the records from the first table but the same result !
i make a mistake somewhere ?!
i don't known...and i need help ...a lot !!!!
Please !!!!!
below part of the records from both tables :
1. table "source" : _ACC
1 401.2.003 4 just. F0369877 2006-01-01 00:00:00.000 1499.0 1 1
1 442.6.019 4 F0369877 2006-01-01 00:00:00.000 239.00 1
1 626.1.000.194 F0369877 2006-01-01 00:00:00.000 1260.00 1
2 129.1 21 PROF.2005 2006-01-01 00:00:00.000447521.01 1
2 121.0.005 21 PROF.2005 2006-01-01 00:00:00.000447521.00 1
3 901 27 F0369877 2006-01-01 00:00:00.000 1260.01 1
3 910.7.010 27 F0369877 2006-01-01 00:00:00.000 1260.00 1
..and so one
2. table "destination: ACCEVENTIMPORT
1 442.6.0194 F0369877 2006-01-01 00:00:00.000 239.00 1
53 891 29 2006-01-01 00:00:00.000 3502.00 1
106 891 29 2006-01-01 00:00:00.000 284.01 1
142 901 27 2006-01-04 00:00:00.000 445.01 1
...
the results are display after a simple select * from table
also the cursor code :
DECLARE @ARTLN INT, @GLCODE VARCHAR(255), @DSRID INT, @JUST VARCHAR(255)
DECLARE @COD VARCHAR(255), @data DATETIME, @val FLOAT, @ISCREDIT INT, @BAT INT
DECLARE IMPORTACC CURSOR FOR
SELECT ARTICLENUM, GLCODE, DSRID, JUSTIFICATION, TRADECODE, TRANSDATE, TRNSVALUE, ISCREDIT, BATCHID
FROM _ACC ORDER BY ARTICLENUM --, ISCREDIT
OPEN IMPORTACC
FETCH NEXT FROM IMPORTACC INTO @ARTLN, @GLCODE, @DSRID, @JUST, @COD, @data, @val, @ISCREDIT, @BAT
WHILE (@@FETCH_STATUS <> -1)
BEGIN
INSERT INTO ACCEVENTIMPORT (ARTICLENUM, GLCODE, DSRID, JUSTIFICATION, TRADECODE, TRANSDATE, TRNVALUE,ISCREDIT,BATCHID)
VALUES (@ARTLN, @GLCODE, @DSRID, @JUST, @COD, @data, @val, @ISCREDIT, @BAT)
FETCH NEXT FROM IMPORTACC INTO @ARTLN, @GLCODE, @DSRID, @JUST, @COD, @data, @val, @ISCREDIT, @BAT
END
CLOSE IMPORTACC
DEALLOCATE IMPORTACC
June 13, 2006 at 6:41 am
You shouldn't be doing this with a cursor.
The first method you were using was the correct method:
INSERT INTO ACCEVENTIMPORT
SELECT * FROM _ACC
The order of rows in the table ACCEVENTIMPORT does not necessarily have any correlation to how the data was inserted. It most likely has to do with the indexes on this table. Are you sure they are the same as the indexes in _ACC?
June 13, 2006 at 8:48 am
NEITHER THE ACCEVENTIMPORT TABLE OR THE _ACC TABLE HAVE INDEXES
...
Could this be cause of my problem ?! I'll tested !
thans Karl !
June 13, 2006 at 9:08 am
If you are selecting rows from a table, and if the ordering is important, you *must* use ORDER BY.
SELECT *
FROM ACCEVENTIMPORT
ORDER BY ARTICLENUM, GLCODE
Change the columns as required, I'm just guessing as to your required order.
An index is not required for correct sorting, but it may be useful to make the query run faster.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply