Copying GL Accounts from One Company to Another

[35mph]

We need to copy gl accounts from one company to another... this would be pretty simple if accounts did not already exist in the destination company... which we want to keep... but consider this sql statement:

insert into THREE..GL00100
select ACTINDX, ACTNUMBR_1, ACTNUMBR_2, ACTNUMBR_3, ACTNUMBR_4, ACTNUMBR_5, ACTNUMBR_6, ACTNUMBR_7,
ACTNUMBR_8, ACTNUMBR_9, ACTNUMBR_10, ACTALIAS, MNACSGMT, ACCTTYPE, ACTDESCR, PSTNGTYP, ACCATNUM, ACTIVE,
TPCLBLNC, DECPLACS, FXDORVAR, BALFRCLC, DSPLKUPS, CNVRMTHD, HSTRCLRT, NOTEINDX, CREATDDT, MODIFDT, USERDEF1,
USERDEF2, PostSlsIn, PostIvIn, PostPurchIn, PostPRIn, ADJINFL, INFLAREV, INFLAEQU, ACCTENTR, USRDEFS1,
USRDEFS2
from TWO..GL00100
where TWO..GL00100.ACTNUMBR_1 not in (select ACTNUMBR_1 from THREE..GL00100)

Basically, if we leave the ACTINDX column in the script, it won't run since there may already be a row with that value in it...

If we leave that column out, then the script will fail since a blank ACTINDX will fail, either because it's the key, or because the 2nd row with a blank index will match the first row with a blank index... hence a failure...

I guess I could turn off the Key to the table while I insert, but that seems a little dangerous....

any thoughts on the best way to proceed?

 

[barbola]

You can use DTS to copy the GL accounts from another database, and select "Append" to existing data.

Then use mass update to weed out the ones you want or not.

Thanks!
Barb E.

 

[jymm]

personally I would look through a dex log to figure out what SP is called on the insert and use that to move the data - that way the actindex is the way you want...

 

[35mph]

barbola, admittedly I may be missing something, but your idea didn't work for me because of primary key constraints... the existing records kept getting in the way.

I solved this problem by exporting all the data to Excel, combining the data, massaging the records, deleting the data in the original table, then importing. Seems convoluted, but it worked... I'm sure there must be an easier way.

jymm, interesting idea... thanks for the advice.

 

[barbola]

I think you have to uncheck (or leave checked?) the insert identity thingy.

As for the other suggestion - wayyy above my head! lol

Thanks!
Barb E.