May 4, 2009 at 6:45 am
Hi,
I hv plan to upgrade the system on the same server.However the current and new databases are different but having tables for a similar purpose.
Example In Current DB i have a table called users with userid,username,logindate as the fields
In New DB i have a table with the name called users with the following fields inuserid, txtusername,dtlogin as the fields
If the structure is some what like this in my 2 different database and i need to migrate the datas from DB1 to DB1 how do i go with this using BCP or osql
please advised the best way because i need to migrate abt 200 server.
With Regards
Duens:-)
May 4, 2009 at 11:40 am
Have you considered using DTS? You can map columns in the source tables to columns in the destination tables regardless of the column names and you can do some transformation of data if, for example, you need to change a data type or split a column into multiple columns.
You could also do it with T-SQL scripts that select from the source tables and insert into the destination tables.
Greg
May 5, 2009 at 2:21 am
Greg,thanks for advised.I did not consider to use the DTS because data migration involved of many server and sql 2000 engine will be reinstalled when data pulled out.
I have tried T-SQL but still got the error due to max buffer size is 1023 bytes.
In example,
This query statement is okey because of not many hints involved.
bcp "select DISTID, NAME, SEX, BIRTHDAY, MARRIAGE, NATIONALID from Department" queryout Department.txt -U sa -P jr_pwd -c
However this query did not work because of many hints involved.
bcp "select DISTID, NAME, SEX, BIRTHDAY, MARRIAGE, NATIONALID, ADDRESS, ZIP, FAX, PHONE1, PHONE2, SPONSORID, ISPREORDER, ISBACKORDER, BBID, OCCUPATION, EDUCATION, DELIVERTYPE, CITY, PROVINCE, TITLEID, ZONEID, LOA, REMARK, GBSSID, IDADDRESS, STARLEVEL, STARLEVELDATE, ISDELETED, DELETEDATE, ISLOCKED, REGISTERDATE, UPDATEDATE, CLUBINFO, TRANSFERDATE, SECTION, SUPPLIER, ARPAYLEADTIME, BRANCHID, REGIONID, CONTRACTEFFDATE, CHECKBILLINGDAY, SETTLECONDITION, SUPPLYARCHDEPTH, ISNEW, LOCKREASON, LSTTITLECHGDATE, STATUS, LSTSTATUSCHGDATE, EMAIL, DISTRICT, STREET, COMMISSIONBBID, MOBILE, MANAGERID, SRZONEID, PAYGROUPID, DOMINATIONSTORE, CONTACTPERSON, DELIVERCONFIRM, DISCOUNTGROUP, USERID, FIRSTNAME, PRICEGROUP, RECOMMENDERID, BALANCEAMT, CHECKSTATUS, PASTDUEAMT, PASTDUECODE, DOWNLOADDATE, DOWNLOADTIME, CREDITLEVELID, CREDITAMT, ADDITIONCREDITAMT, BPCREDITAMT, CREDITFACTOR, CREDITSETDATE, DEALERCREDITAMT, PAYMETHOD, ISALLOWRETURN, ISLOCKCREDIT, LOCKCREDITREASON, MINISALESAMT, SYSCREDITAMT, TMPCREDITAMT, FESTIVALCODE, ETHNICGROUP, PREFEDLANGUAGE, NATIONALITY, MARKETCODE, BANKCODE, APPOINTMENTKIT, APPOINTMENTSOURCE, HOUSEHOLDINCOME, DELIVERYADDR, CREDITCARDNO, CREDITCARDNAME, CREDITCARDEXPDATE, BANKACCOUNT, CREATEDDATE from Department" queryout Department.txt -U sa -P jr_pwd -c
Hope pls advise
May 5, 2009 at 3:54 am
Sorry,i have not try for T-SQL yet.Pls prefer for BCP,pls advise
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply