migrating data via bcp and sqlcmd

[Joe Boyle]

Ooops, the update sql code should have been more along the lines of :

update tabl_joe3 set col7 = (select b.col7 from tabl_joe3_preload b where
tabl_joe3.col1 = b.col1 ) where (tabl_joe3.col7 is null ) 
or exists (select b.col7 from tabl_joe3_preload b where (tabl_joe3.col1 =
b.col1 and ( tabl_joe3.col7 != b.col7)))


________________________________________
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
Joe Boyle
Sent: 30 January 2007 16:22
To: 'Viet Nguyen'; powerh-l at lists.sowder.com
Subject: RE: migrating data via bcp and sqlcmd

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