migrating data via bcp and sqlcmd

[Joe Boyle]

And just in case you don't have PH on the Windows side, I found that the
following works with 'MSSQLServer 2005 express' so it should'nt be a problem
to rewrite for 'MSSQLServer 2005',

 

bcp newschema.dbo. bcpin_table _preload in c:\path\feedbcp.ps -t ~ -c -r "|"
-e errout.log -U sa -P mypassword -S mySQLSERVERserver

 

sqlcmd  -U sa -P mypassword -S mySQLSERVERserver <  feedbcp_script.sql

 

The feedbcp_script.sql script file content is below,

 

use newone

go

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

begin tran

update bcpin_table set col7 = (select b.col7 from  bcpin_table_preload b
where bcpin_table.col1 = b.col1 and 

(bcpin_table.col7 is null ) or (bcpin_table.col7 != b.col7) )

go

commit

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

begin tran

delete from bcpin_table_preload where col1 in (select col1 from
bcpin_table)

go

commit

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

begin tran

insert into bcpin_table select * from bcpin_table_preload

go

commit

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

begin tran

delete from bcpin_table_preload

go

commit

exit

 

and use quiz code like that below to generate the .ps file ( I tested
something like this in the past and it worked ok then )

 

can cle

set veri error

set rep nolim

 

acc orders in axnwind

 

define d_OrderID char*10 = ascii(OrderID ,10)

 

define d_Freight char*14 = formatnumber ( Freight/100,
"+000000.0000","-000000.0000","0.00")

 

define d_date date century included = sysdate

 

define d_time char*14  =  formatnumber ( d_date*10000 + systime, "00000000
00:00")

 

 

define d_sep char*1 = "~"

define d_eol char*1 = "|"

 

set sub name c:\path\feedbcp portable

 

rep summ &

d_OrderID &

d_sep &

d_Freight &

d_sep &

d_freight &

d_sep &

employeeid &

d_sep &

shipvia &

d_sep &

d_date &

d_sep &

d_time &

d_eol

 

bui feedbcp

  _____  

From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
Viet Nguyen
Sent: 29 January 2007 18:42
To: Ken at Langendock.com; Robert Edis; powerh-l at lists.sowder.com
Subject: RE: migrating data

 

Thank you all - I will remove my index files - If this doesn't work well
enough I will try the bulk insert with portable files.

Viet.

 

 

-----Original Message-----
From: powerh-l-bounces+vnguyen=wsboces.org at lists.sowder.com
[mailto:powerh-l-bounces+vnguyen=wsboces.org at lists.sowder.com] On Behalf Of
Ken Langendock
Sent: Monday, January 29, 2007 1:27 PM
To: 'Robert Edis'; powerh-l at lists.sowder.com
Subject: RE: migrating data

 

Blue is correct.

 

When we converted from HP3000 to AIX/Oracle we did the same thing.

 

Loaded the tables without any indexes and built the indexes afterwards.

It was like night and day.

 

Good luck Viet

Ken

 

-----Original Message-----
From: powerh-l-bounces+ken.langendock=rogers.com at lists.sowder.com
[mailto:powerh-l-bounces+ken.langendock=rogers.com at lists.sowder.com] On
Behalf Of Robert Edis
Sent: January 29, 2007 1:18 PM
To: powerh-l at lists.sowder.com
Subject: RE: migrating data


Does the recieving table have a unique index on the primary key column?
Even though you are doing an add and no update the database engine still has
to confirm that you are not entering a duplicate row if a unique PK has been
defined.  If it has and you know that the new data has a PK that does not
already exist in the table then I suggest you drop the PK index before the
load and rebuild it afterwards. 

Blue 




From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
Viet Nguyen
Sent: 29 January 2007 16:07
To: powerh-l at lists.sowder.com
Subject: migrating data



Hi ,

Would anyone be able to show me how to speed up data exporting from AIX to
MSSQL?

It takes around 24 hours to export the data 



Here is my set up:



1.                 Powerhouse with CISAM file on AIX
2.                 MSSQL DB is on Windows box  
3.                 I am using Data Direct ODBC driver to connect to the
MSSQL db from
the AIX box and
4.                 we're on a 1gigabit network.
5.                 Here is a qtp run sample:





set process nolimit

commit at request

set input   nolimit



use setlimit



request abadj

access abadj alias oldabadj

output abadj in sqldevl add



request abadjdesc

access abadjdesc alias oldabadjdesc

output abadjdesc in sqldevl add



request abadjdet

access abadjdet alias oldabadjdet

output abadjdet in sqldevl add



;request abapprover

;access abapprover alias oldabapprover

;output abapprover in sqldevl add



request abbudexp

access abbudexp alias oldabbudexp

output abbudexp in sqldevl add



request abbudgroup

access abbudgroup alias oldabbudgroup

output abbudgroup in sqldevl add




-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20070130/3a933ffc/attachment-0001.html