1.  Home
  2. WEB400
  3. May 2007

Re: SQLing data from a different iSeries

Hi

Sorry (all) about the length of this post but you could investigate the
ADO.NET bulk copy function. Code included below copies 100,000 iDB2 records
from iDB3 to SQL server 2005 in 10 seconds! (on an i5 Express 520) It could
be iDB2 (Test) to iDB2 (Live) raally just down to the connection string
settings.

It's obviously very specific code but maybe its worth a look and its easy to
modify (if your into VB.NET?) most of the code below is just duplicated for
each table.

The SQL could be improved but it appears to work quickly (although all
suggestions very welcome if it is a topic of interest)

Maurice O'Prey
XMLi5.com



'***************************************************************************
*******************
'
' Class: BulkCopy_Products.vb
'
' Function: Replicates all Product Data from iDB2 to SQL
'
' DataAccessLayer: None
'
'
' ==============
' METHOD SUMMARY
' ==============
'
' -------------------------
' 1-20 (GET)
' -------------------------
'
' 1/. Copy(FromDb, ToDb) As Integer
'
' Modification Summary
' --------------------
'
' Mod ID Date By Description
' ------ --------- --------
-------------------------------------------
' /999 99/99/99 Inits
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'
'***************************************************************************
*******************
Imports System.Data
Imports System.Data.SqlClient
Imports IBM.Data
Imports IBM.Data.DB2.iSeries
Imports XMLi5.Tools.Log


Namespace XMLi5.BulkCopy

Public Class Products



'===========================================================================
========================
'
' CONSTRUCTORS
'

'===========================================================================
========================

''' <summary>
''' Sub - New
''' </summary>
Sub New()
End Sub


'===========================================================================
========================
'
' PROPERTIES
'

'===========================================================================
========================



'===========================================================================
========================
'
' DATA ACCESS LAYER METHODS
'

'===========================================================================
========================

' -------------------------
' 1-20 (COPY)
' -------------------------
'
' 1/. Copy()
'
' 2/. DoExtract()
'
' 3/. DoExtractTable(SourceTable, ExtractTable,
DestinationTable)
'
' -------------------------
' 41-60 (UPDATE)
' -------------------------
'
' 40/. DoUpdateTable_WPPMP000()
' 41/. DoUpdateTable_WPPMP100()
' 41D/. DoUpdateTable_WPPMP100D()
' 42/. DoUpdateTable_WPPMP200()
' 42D/. DoUpdateTable_WPPMP200D()
' 43/. DoUpdateTable_WPPMP300()
' 43D/. DoUpdateTable_WPPMP300D()
' 44/. DoUpdateTable_WPPMP400()
' 44D/. DoUpdateTable_WPPMP900D()
' 49/. DoUpdateTable_WPPMP900()
' 49D/. DoUpdateTable_WPPMP900D()

'

'===========================================================================
========================
'
' 1/. Copy()
'

'===========================================================================
========================
Public Shared Sub Copy()

'========
' Extract
'========
DoExtract()

'=======================================================
' Clear the Cache so that the site reflects the new data
'=======================================================
Cache.EmptyCache()

End Sub

#Region "2/. DoExtract()"


'===========================================================================
========================
'
' 2/. DoExtract()
'

'===========================================================================
========================
Private Shared Sub DoExtract()

Dim SourceTable As String
Dim ExtractTable As String
Dim DestinationTable As String

'--------------------------------------------------------
' 2.0 iDB2.WPPMP000 to SQL.WXPMP000 to SQL.WPPMP000
'--------------------------------------------------------
SourceTable = "WPPMP000"
ExtractTable = "WXPMP000"
DestinationTable = "dbo.WPPMP000"

DoExtractTable(SourceTable, ExtractTable, DestinationTable)
DoUpdateTable_WPPMP000()

'--------------------------------------------------------
' 2.1 iDB2.WPPMP100 to SQL.WXPMP100 to SQL.WPPMP100
'--------------------------------------------------------
SourceTable = "WPPMP100"
ExtractTable = "WXPMP100"
DestinationTable = "dbo.WPPMP100"

DoExtractTable(SourceTable, ExtractTable, DestinationTable)
DoUpdateTable_WPPMP100()

'--------------------------------------------------------
' 2.1D iDB2.WPPMP100D to SQL.WXPMP100D to SQL.WPPMP100D
'--------------------------------------------------------
SourceTable = "WPPMP100D"
ExtractTable = "WXPMP100D"
DestinationTable = "dbo.WPPMP100D"

DoExtractTable(SourceTable, ExtractTable, DestinationTable)
DoUpdateTable_WPPMP100D()

'--------------------------------------------------------
' 2.2 iDB2.WPPMP200 to SQL.WXPMP200 to SQL.WPPMP200
'--------------------------------------------------------
SourceTable = "WPPMP200"
ExtractTable = "WXPMP200"
DestinationTable = "dbo.WPPMP200"

DoExtractTable(SourceTable, ExtractTable, DestinationTable)
DoUpdateTable_WPPMP200()

'--------------------------------------------------------
' 2.2D iDB2.WPPMP200D to SQL.WXPMP200D to SQL.WPPMP200D
'--------------------------------------------------------
SourceTable = "WPPMP200D"
ExtractTable = "WXPMP200D"
DestinationTable = "dbo.WPPMP200D"

DoExtractTable(SourceTable, ExtractTable, DestinationTable)
DoUpdateTable_WPPMP200D()

'--------------------------------------------------------
' 2.3 iDB2.WPPMP300 to SQL.WXPMP300 to SQL.WPPMP300
'--------------------------------------------------------
SourceTable = "WPPMP300"
ExtractTable = "WXPMP300"
DestinationTable = "dbo.WPPMP300"

DoExtractTable(SourceTable, ExtractTable, DestinationTable)
DoUpdateTable_WPPMP300()

'--------------------------------------------------------
' 2.3D iDB2.WPPMP300D to SQL.WXPMP300D to SQL.WPPMP300D
'--------------------------------------------------------
SourceTable = "WPPMP300D"
ExtractTable = "WXPMP300D"
DestinationTable = "dbo.WPPMP300D"

DoExtractTable(SourceTable, ExtractTable, DestinationTable)
DoUpdateTable_WPPMP300D()

'--------------------------------------------------------
' 2.4 iDB2.WPPMP400 to SQL.WXPMP400 to SQL.WPPMP400
'--------------------------------------------------------
SourceTable = "WPPMP400"
ExtractTable = "WXPMP400"
DestinationTable = "dbo.WPPMP400"

DoExtractTable(SourceTable, ExtractTable, DestinationTable)
DoUpdateTable_WPPMP400()

'--------------------------------------------------------
' 2.4D iDB2.WPPMP400D to SQL.WXPMP400D to SQL.WPPMP400D
'--------------------------------------------------------
SourceTable = "WPPMP400D"
ExtractTable = "WXPMP400D"
DestinationTable = "dbo.WPPMP400D"

DoExtractTable(SourceTable, ExtractTable, DestinationTable)
DoUpdateTable_WPPMP400D()

'--------------------------------------------------------
' 2.9 iDB2.WPPMP900 to SQL.WXPMP900 to SQL.WPPMP900
'--------------------------------------------------------
SourceTable = "WPPMP900"
ExtractTable = "WXPMP900"
DestinationTable = "dbo.WPPMP900"

DoExtractTable(SourceTable, ExtractTable, DestinationTable)
DoUpdateTable_WPPMP900()

'--------------------------------------------------------
' 2.9D iDB2.WPPMP900D to SQL.WXPMP900D to SQL.WPPMP900D
'--------------------------------------------------------
SourceTable = "WPPMP900D"
ExtractTable = "WXPMP900D"
DestinationTable = "dbo.WPPMP900D"

DoExtractTable(SourceTable, ExtractTable, DestinationTable)
DoUpdateTable_WPPMP900D()

End Sub

#End Region

#Region "3/. DoExtractTable(SourceTable, ExtractTable, DestinationTable)"


'===========================================================================
========================
'
' 3/. DoExtractTable(SourceTable, ExtractTable, DestinationTable)
'

'===========================================================================
========================
Private Shared Sub DoExtractTable( _
ByVal SourceTable As String, _
ByVal ExtractTable As String, _
ByVal DestinationTable As String)

'======================================================
' DELETE ALL records from the EXTRACT Table
'======================================================
Dim sbDelete As New StringBuilder
With sbDelete
.Append("Delete from ")
.Append(ExtractTable)
End With

Dim connectionString As String = AppSettings.SQLConnectionString
Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbDelete.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using

'======================================================
' COPY the SOURCE table to the EXTRACT table
'======================================================
Dim sbSelect As New StringBuilder
With sbSelect
.Append("Select * from ")
.Append(SourceTable)
.Append(" as ")
.Append(ExtractTable)
End With

' Initialise the number of records copied to zero
Dim RecordsCopied As Integer = 0

' Initialise Connection Objects
Dim iDB2Con As iDB2Connection = New iDB2Connection()
Dim SQLCon As SqlConnection = New SqlConnection()

' Initialise Command Objects
Dim iDB2Cmd As iDB2Command = New iDB2Command()
Dim SQLCmd As SqlCommand = New SqlCommand()

' Initialise the Source Data Reader
Dim iDB2Reader As iDB2DataReader

' Attach the connection strings to the connection objects
iDB2Con.ConnectionString = AppSettings.iDB2ConnectionString
SQLCon.ConnectionString = AppSettings.SQLConnectionString

' Attach the connection objects to the commands
iDB2Cmd.Connection = iDB2Con
SQLCmd.Connection = SQLCon

' Set Source Command SQL
iDB2Cmd.CommandText = sbSelect.ToString()
iDB2Cmd.CommandType = CommandType.Text


' Open the Source Connection
iDB2Cmd.Connection.Open()

Dim SQLBulkOp As SqlBulkCopy
SQLBulkOp = New SqlBulkCopy(AppSettings.SQLConnectionString,
SqlBulkCopyOptions.UseInternalTransaction)

' Set Destination Table Name and Timeout
SQLBulkOp.DestinationTableName = ExtractTable
SQLBulkOp.BulkCopyTimeout = 500000000

' Execute the From Reader
iDB2Reader = iDB2Cmd.ExecuteReader()

Try
SQLBulkOp.WriteToServer(iDB2Reader)
Catch ex As Exception
WriteLog(ex.Message)
Finally
iDB2Reader.Close()
iDB2Cmd.Connection.Close()
End Try

End Sub

#End Region

#Region "40/. DoUpdateTable_WPPMP000()"


'===========================================================================
========================
'
' 40/. DoUpdateTable_WPPMP000()
'

'===========================================================================
========================
Private Shared Sub DoUpdateTable_WPPMP000()

Dim connectionString As String = AppSettings.SQLConnectionString


'===========================================================================
==========
' 1/. UPDATE the DESTINATION Records from the SOURCE Records

'===========================================================================
==========

Dim sbUpdate As New StringBuilder
With sbUpdate
.Append("Update WPPMP000 ")
.Append("Set ")
.Append("PM0ACT = RTrim(( SELECT WXPMP000.PM0ACT From
WXPMP000 Where WXPMP000.PM0SIT = WPPMP000.PM0SIT and WXPMP000.PM0CTL =
WPPMP000.PM0CTL and WXPMP000.PM0COD = WPPMP000.PM0COD)) ")
.Append("Where Exists ")
.Append("(")
.Append("Select WXPMP000.PM0COD ")
.Append("From WXPMP000 ")
.Append("Where WXPMP000.PM0SIT = WPPMP000.PM0SIT ")
.Append("And WXPMP000.PM0CTL = WPPMP000.PM0CTL ")
.Append("And WXPMP000.PM0COD = WPPMP000.PM0COD ")
.Append(")")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbUpdate.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 2/. INSERT new Records into the DESTINATION Table

'===========================================================================
==========
Dim sbInsert As New StringBuilder
With sbInsert
.Append("INSERT INTO WPPMP000 ")
.Append("(")
.Append("PM0SIT, ")
.Append("PM0CTL, ")
.Append("PM0COD, ")
.Append("PM0ACT, ")
.Append("PM0PG1, ")
.Append("PM0PG2, ")
.Append("PM0PG3, ")
.Append("PM0PG4, ")
.Append("PM0PG5, ")
.Append("PM0PG6, ")
.Append("PM0PG7, ")
.Append("PM0PG8, ")
.Append("PM0PRD ")
.Append(")")
.Append("SELECT ")
.Append("RTrim(WXPMP000.PM0SIT) AS Expr1, ")
.Append("RTrim(WXPMP000.PM0CTL) AS Expr2, ")
.Append("RTrim(WXPMP000.PM0COD) AS Expr3, ")
.Append("RTrim(WXPMP000.PM0ACT) AS Expr4, ")
.Append("RTrim(WXPMP000.PM0PG1) AS Expr5, ")
.Append("RTrim(WXPMP000.PM0PG2) AS Expr6, ")
.Append("RTrim(WXPMP000.PM0PG3) AS Expr7, ")
.Append("RTrim(WXPMP000.PM0PG4) AS Expr8, ")
.Append("RTrim(WXPMP000.PM0PG5) AS Expr9, ")
.Append("RTrim(WXPMP000.PM0PG6) AS Expr10, ")
.Append("RTrim(WXPMP000.PM0PG7) AS Expr11, ")
.Append("RTrim(WXPMP000.PM0PG8) AS Expr12, ")
.Append("RTrim(WXPMP000.PM0PRD) AS Expr13 ")
.Append("FROM WXPMP000 ")
.Append("WHERE WXPMP000.PM0SIT Not In (SELECT PM0SIT from
WPPMP000)")
.Append("AND WXPMP000.PM0CTL Not In (SELECT PM0CTL from
WPPMP000)")
.Append("AND WXPMP000.PM0COD Not In (SELECT PM0COD from
WPPMP000)")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbInsert.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 3/. DELETE Records from the DESTINATION Table that do NOT
Exist in the EXTRACT Table

'===========================================================================
==========
Dim sbDelete As New StringBuilder
With sbDelete
.Append("Delete from WPPMP000 ")
.Append("Where PM0SIT Not In (Select PM0SIT From WXPMP000)")
.Append("Or PM0CTL Not In (Select PM0CTL From WXPMP000)")
.Append("Or PM0COD Not In (Select PM0COD From WXPMP000)")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbDelete.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using

End Sub

#End Region

#Region "41/. DoUpdateTable_WPPMP100()"


'===========================================================================
========================
'
' 41/. DoUpdateTable_WPPMP100()
'

'===========================================================================
========================
Private Shared Sub DoUpdateTable_WPPMP100()

Dim connectionString As String = AppSettings.SQLConnectionString


'===========================================================================
==========
' 1/. UPDATE the DESTINATION Records from the SOURCE Records

'===========================================================================
==========

Dim sbUpdate As New StringBuilder
With sbUpdate
.Append("Update WPPMP100 ")
.Append("Set ")
.Append("PM1ACT = RTrim(( SELECT WXPMP100.PM1ACT From
WXPMP100 Where WXPMP100.PM1SIT = WPPMP100.PM1SIT And WXPMP100.PM1CTL =
WPPMP100.PM1CTL And WXPMP100.PM1GRP = WPPMP100.PM1GRP)) ")
.Append(",")
.Append("PM1ACS = RTrim(( SELECT WXPMP100.PM1ACS From
WXPMP100 Where WXPMP100.PM1SIT = WPPMP100.PM1SIT And WXPMP100.PM1CTL =
WPPMP100.PM1CTL And WXPMP100.PM1GRP = WPPMP100.PM1GRP)) ")
.Append(",")
.Append("PM1SEQ = RTrim(( SELECT WXPMP100.PM1SEQ From
WXPMP100 Where WXPMP100.PM1SIT = WPPMP100.PM1SIT And WXPMP100.PM1CTL =
WPPMP100.PM1CTL And WXPMP100.PM1GRP = WPPMP100.PM1GRP)) ")
.Append(",")
.Append("PM1IM1 = RTrim(( SELECT WXPMP100.PM1IM1 From
WXPMP100 Where WXPMP100.PM1SIT = WPPMP100.PM1SIT And WXPMP100.PM1CTL =
WPPMP100.PM1CTL And WXPMP100.PM1GRP = WPPMP100.PM1GRP)) ")
.Append(",")
.Append("PM1IM2 = RTrim(( SELECT WXPMP100.PM1IM2 From
WXPMP100 Where WXPMP100.PM1SIT = WPPMP100.PM1SIT And WXPMP100.PM1CTL =
WPPMP100.PM1CTL And WXPMP100.PM1GRP = WPPMP100.PM1GRP)) ")
.Append(",")
.Append("PM1IM3 = RTrim(( SELECT WXPMP100.PM1IM3 From
WXPMP100 Where WXPMP100.PM1SIT = WPPMP100.PM1SIT And WXPMP100.PM1CTL =
WPPMP100.PM1CTL And WXPMP100.PM1GRP = WPPMP100.PM1GRP)) ")
.Append("Where Exists ")
.Append("(")
.Append("Select WXPMP100.PM1GRP ")
.Append("From WXPMP100 ")
.Append("Where WXPMP100.PM1SIT = WPPMP100.PM1SIT ")
.Append("And WXPMP100.PM1CTL = WPPMP100.PM1CTL ")
.Append("And WXPMP100.PM1GRP = WPPMP100.PM1GRP ")
.Append(")")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbUpdate.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 2/. INSERT new Records into the DESTINATION Table

'===========================================================================
==========
Dim sbInsert As New StringBuilder
With sbInsert
.Append("INSERT INTO WPPMP100 ")
.Append("(")
.Append("PM1SIT, ")
.Append("PM1CTL, ")
.Append("PM1GRP, ")
.Append("PM1ACT, ")
.Append("PM1ACS, ")
.Append("PM1SEQ, ")
.Append("PM1IM1, ")
.Append("PM1IM2, ")
.Append("PM1IM3 ")
.Append(")")
.Append(" SELECT ")
.Append("RTrim(WXPMP100.PM1SIT) AS Expr1, ")
.Append("RTrim(WXPMP100.PM1CTL) AS Expr2, ")
.Append("RTrim(WXPMP100.PM1GRP) AS Expr3, ")
.Append("RTrim(WXPMP100.PM1ACT) AS Expr4, ")
.Append("RTrim(WXPMP100.PM1ACS) AS Expr5, ")
.Append("RTrim(WXPMP100.PM1SEQ) AS Expr6, ")
.Append("RTrim(WXPMP100.PM1IM1) AS Expr7, ")
.Append("RTrim(WXPMP100.PM1IM2) AS Expr8, ")
.Append("RTrim(WXPMP100.PM1IM3) AS Expr9 ")
.Append("FROM WXPMP100 ")
.Append("WHERE WXPMP100.PM1SIT Not In (SELECT PM1SIT from
WPPMP100) ")
.Append("Or WXPMP100.PM1CTL Not In (SELECT PM1CTL from
WPPMP100) ")
.Append("Or WXPMP100.PM1GRP Not In (SELECT PM1GRP from
WPPMP100) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbInsert.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 3/. DELETE Records from the DESTINATION Table that do NOT
Exist in the EXTRACT Table

'===========================================================================
==========
Dim sbDelete As New StringBuilder
With sbDelete
.Append("Delete from WPPMP100 ")
.Append("Where PM1SIT Not In (Select PM1SIT From WXPMP100)")
.Append("Or PM1CTL Not In (Select PM1CTL From WXPMP100)")
.Append("Or PM1GRP Not In (Select PM1GRP From WXPMP100)")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbDelete.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using

End Sub

#End Region

#Region "41D/. DoUpdateTable_WPPMP100D()"


'===========================================================================
========================
'
' 41D/. DoUpdateTable_WPPMP100D()
'

'===========================================================================
========================
Private Shared Sub DoUpdateTable_WPPMP100D()

Dim connectionString As String = AppSettings.SQLConnectionString


'===========================================================================
==========
' 1/. UPDATE the DESTINATION Records from the SOURCE Records

'===========================================================================
==========

' NOTE - No Update Required as all maintainable fields (i.e.
Text Descriptions)
' are maintained on the server (via the Browser)


'===========================================================================
==========
' 2/. INSERT new Records into the DESTINATION Table

'===========================================================================
==========
Dim sbInsert As New StringBuilder
With sbInsert
.Append("INSERT INTO WPPMP100D ")
.Append("(")
.Append("PM1SITD, ")
.Append("PM1CTLD, ")
.Append("PM1LIDD, ")
.Append("PM1GRPD, ")
.Append("PM1DS1D, ")
.Append("PM1DS2D, ")
.Append("PM1DS3D ")
.Append(")")
.Append("SELECT ")
.Append("RTrim(WXPMP100D.PM1SITD) AS Expr1, ")
.Append("RTrim(WXPMP100D.PM1CTLD) AS Expr2, ")
.Append("RTrim(WXPMP100D.PM1LIDD) AS Expr3, ")
.Append("RTrim(WXPMP100D.PM1GRPD) AS Expr4, ")
.Append("RTrim(WXPMP100D.PM1DS1D) AS Expr5, ")
.Append("RTrim(WXPMP100D.PM1DS2D) AS Expr6, ")
.Append("RTrim(WXPMP100D.PM1DS3D) AS Expr7 ")
.Append("FROM WXPMP100D ")
.Append("Where WXPMP100D.PM1SITD Not In (SELECT PM1SITD from
WPPMP100D)")
.Append("Or WXPMP100D.PM1CTLD Not In (SELECT PM1CTLD from
WPPMP100D)")
.Append("Or WXPMP100D.PM1LIDD Not In (SELECT PM1LIDD from
WPPMP100D)")
.Append("Or WXPMP100D.PM1GRPD Not In (SELECT PM1GRPD from
WPPMP100D)")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbInsert.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 3/. DELETE Records from the DESTINATION Table that do NOT
Exist in the EXTRACT Table

'===========================================================================
==========
Dim sbDelete As New StringBuilder
With sbDelete
.Append("Delete from WPPMP100D ")
.Append("Where PM1SITD Not In (Select PM1SITD From
WXPMP100D)")
.Append("Or PM1CTLD Not In (Select PM1CTLD From WXPMP100D)
")
.Append("Or PM1LIDD Not In (Select PM1LIDD From WXPMP100D)
")
.Append("Or PM1GRPD Not In (Select PM1GRPD From WXPMP100D)
")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbDelete.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using

End Sub

#End Region

#Region "42/. DoUpdateTable_WPPMP200()"


'===========================================================================
========================
'
' 42/. DoUpdateTable_WPPMP200()
'

'===========================================================================
========================
Private Shared Sub DoUpdateTable_WPPMP200()

Dim connectionString As String = AppSettings.SQLConnectionString


'===========================================================================
==========
' 1/. UPDATE the DESTINATION Records from the SOURCE Records

'===========================================================================
==========

Dim sbUpdate As New StringBuilder
With sbUpdate
.Append("Update WPPMP200 ")
.Append("Set ")
.Append("PM2ACT = RTrim(( SELECT WXPMP200.PM2ACT From
WXPMP200 Where WXPMP200.PM2SIT = WPPMP200.PM2SIT And WXPMP200.PM2CTL =
WPPMP200.PM2CTL And WXPMP200.PM2GRP = WPPMP200.PM2GRP)) ")
.Append(",")
.Append("PM2ACS = RTrim(( SELECT WXPMP200.PM2ACS From
WXPMP200 Where WXPMP200.PM2SIT = WPPMP200.PM2SIT And WXPMP200.PM2CTL =
WPPMP200.PM2CTL And WXPMP200.PM2GRP = WPPMP200.PM2GRP)) ")
.Append(",")
.Append("PM2SEQ = RTrim(( SELECT WXPMP200.PM2SEQ From
WXPMP200 Where WXPMP200.PM2SIT = WPPMP200.PM2SIT And WXPMP200.PM2CTL =
WPPMP200.PM2CTL And WXPMP200.PM2GRP = WPPMP200.PM2GRP)) ")
.Append(",")
.Append("PM2IM1 = RTrim(( SELECT WXPMP200.PM2IM1 From
WXPMP200 Where WXPMP200.PM2SIT = WPPMP200.PM2SIT And WXPMP200.PM2CTL =
WPPMP200.PM2CTL And WXPMP200.PM2GRP = WPPMP200.PM2GRP)) ")
.Append(",")
.Append("PM2IM2 = RTrim(( SELECT WXPMP200.PM2IM2 From
WXPMP200 Where WXPMP200.PM2SIT = WPPMP200.PM2SIT And WXPMP200.PM2CTL =
WPPMP200.PM2CTL And WXPMP200.PM2GRP = WPPMP200.PM2GRP)) ")
.Append(",")
.Append("PM2IM3 = RTrim(( SELECT WXPMP200.PM2IM3 From
WXPMP200 Where WXPMP200.PM2SIT = WPPMP200.PM2SIT And WXPMP200.PM2CTL =
WPPMP200.PM2CTL And WXPMP200.PM2GRP = WPPMP200.PM2GRP)) ")
.Append("Where Exists ")
.Append("(")
.Append("Select WXPMP200.PM2GRP ")
.Append("From WXPMP200 ")
.Append("Where WXPMP200.PM2SIT = WPPMP200.PM2SIT ")
.Append("And WXPMP200.PM2CTL = WPPMP200.PM2CTL ")
.Append("And WXPMP200.PM2GRP = WPPMP200.PM2GRP ")
.Append(")")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbUpdate.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 2/. INSERT new Records into the DESTINATION Table

'===========================================================================
==========
Dim sbInsert As New StringBuilder
With sbInsert
.Append("INSERT INTO WPPMP200 ")
.Append("(")
.Append("PM2SIT, ")
.Append("PM2CTL, ")
.Append("PM2GRP, ")
.Append("PM2ACT, ")
.Append("PM2ACS, ")
.Append("PM2SEQ, ")
.Append("PM2IM1, ")
.Append("PM2IM2, ")
.Append("PM2IM3 ")
.Append(")")
.Append("SELECT ")
.Append("RTrim(WXPMP200.PM2SIT) AS Expr1, ")
.Append("RTrim(WXPMP200.PM2CTL) AS Expr2, ")
.Append("RTrim(WXPMP200.PM2GRP) AS Expr3, ")
.Append("RTrim(WXPMP200.PM2ACT) AS Expr4, ")
.Append("RTrim(WXPMP200.PM2ACS) AS Expr5, ")
.Append("RTrim(WXPMP200.PM2SEQ) AS Expr6, ")
.Append("RTrim(WXPMP200.PM2IM1) AS Expr7, ")
.Append("RTrim(WXPMP200.PM2IM2) AS Expr8, ")
.Append("RTrim(WXPMP200.PM2IM3) AS Expr9 ")
.Append("FROM WXPMP200 ")
.Append("WHERE WXPMP200.PM2SIT Not In (SELECT PM2SIT from
WPPMP200) ")
.Append("Or WXPMP200.PM2CTL Not In (SELECT PM2CTL from
WPPMP200) ")
.Append("Or WXPMP200.PM2GRP Not In (SELECT PM2GRP from
WPPMP200) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbInsert.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 3/. DELETE Records from the DESTINATION Table that do NOT
Exist in the EXTRACT Table

'===========================================================================
==========
Dim sbDelete As New StringBuilder
With sbDelete
.Append("Delete from WPPMP200 ")
.Append("Where PM2SIT Not In (Select PM2SIT From WXPMP200)
")
.Append("Or PM2CTL Not In (Select PM2CTL From WXPMP200) ")
.Append("Or PM2GRP Not In (Select PM2GRP From WXPMP200) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbDelete.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using

End Sub

#End Region

#Region "42D/. DoUpdateTable_WPPMP200D()"


'===========================================================================
========================
'
' 42D/. DoUpdateTable_WPPMP200D()
'

'===========================================================================
========================
Private Shared Sub DoUpdateTable_WPPMP200D()

Dim connectionString As String = AppSettings.SQLConnectionString


'===========================================================================
==========
' 1/. UPDATE the DESTINATION Records from the SOURCE Records

'===========================================================================
==========

' NOTE - No Update Required as all maintainable fields (i.e.
Text Descriptions)
' are maintained on the server (via the Browser)


'===========================================================================
==========
' 2/. INSERT new Records into the DESTINATION Table

'===========================================================================
==========
Dim sbInsert As New StringBuilder
With sbInsert
.Append("INSERT INTO WPPMP200D ")
.Append("(")
.Append("PM2SITD, ")
.Append("PM2CTLD, ")
.Append("PM2LIDD, ")
.Append("PM2GRPD, ")
.Append("PM2DS1D, ")
.Append("PM2DS2D, ")
.Append("PM2DS3D ")
.Append(")")
.Append("SELECT ")
.Append("RTrim(WXPMP200D.PM2SITD) AS Expr1, ")
.Append("RTrim(WXPMP200D.PM2CTLD) AS Expr2, ")
.Append("RTrim(WXPMP200D.PM2LIDD) AS Expr3, ")
.Append("RTrim(WXPMP200D.PM2GRPD) AS Expr4, ")
.Append("RTrim(WXPMP200D.PM2DS1D) AS Expr5, ")
.Append("RTrim(WXPMP200D.PM2DS2D) AS Expr6, ")
.Append("RTrim(WXPMP200D.PM2DS3D) AS Expr7 ")
.Append("FROM WXPMP200D ")
.Append("Where WXPMP200D.PM2SITD Not In (SELECT PM2SITD from
WPPMP200D) ")
.Append("Or WXPMP200D.PM2CTLD Not In (SELECT PM2CTLD from
WPPMP200D) ")
.Append("Or WXPMP200D.PM2LIDD Not In (SELECT PM2LIDD from
WPPMP200D) ")
.Append("Or WXPMP200D.PM2GRPD Not In (SELECT PM2GRPD from
WPPMP200D) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbInsert.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 3/. DELETE Records from the DESTINATION Table that do NOT
Exist in the EXTRACT Table

'===========================================================================
==========
Dim sbDelete As New StringBuilder
With sbDelete
.Append("Delete from WPPMP200D ")
.Append("Where PM2SITD Not In (Select PM2SITD From
WXPMP200D) ")
.Append("Or PM2CTLD Not In (Select PM2CTLD From WXPMP200D)
")
.Append("Or PM2LIDD Not In (Select PM2LIDD From WXPMP200D)
")
.Append("Or PM2GRPD Not In (Select PM2GRPD From WXPMP200D)
")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbDelete.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using

End Sub

#End Region

#Region "43/. DoUpdateTable_WPPMP300()"


'===========================================================================
========================
'
' 43/. DoUpdateTable_WPPMP300()
'

'===========================================================================
========================
Private Shared Sub DoUpdateTable_WPPMP300()

Dim connectionString As String = AppSettings.SQLConnectionString


'===========================================================================
==========
' 1/. UPDATE the DESTINATION Records from the SOURCE Records

'===========================================================================
==========

Dim sbUpdate As New StringBuilder
With sbUpdate
.Append("Update WPPMP300 ")
.Append("Set ")
.Append("PM3ACT = RTrim(( SELECT WXPMP300.PM3ACT From
WXPMP300 Where WXPMP300.PM3SIT = WPPMP300.PM3SIT And WXPMP300.PM3CTL =
WPPMP300.PM3CTL And WXPMP300.PM3GRP = WPPMP300.PM3GRP)) ")
.Append(",")
.Append("PM3ACS = RTrim(( SELECT WXPMP300.PM3ACS From
WXPMP300 Where WXPMP300.PM3SIT = WPPMP300.PM3SIT And WXPMP300.PM3CTL =
WPPMP300.PM3CTL And WXPMP300.PM3GRP = WPPMP300.PM3GRP)) ")
.Append(",")
.Append("PM3SEQ = RTrim(( SELECT WXPMP300.PM3SEQ From
WXPMP300 Where WXPMP300.PM3SIT = WPPMP300.PM3SIT And WXPMP300.PM3CTL =
WPPMP300.PM3CTL And WXPMP300.PM3GRP = WPPMP300.PM3GRP)) ")
.Append(",")
.Append("PM3IM1 = RTrim(( SELECT WXPMP300.PM3IM1 From
WXPMP300 Where WXPMP300.PM3SIT = WPPMP300.PM3SIT And WXPMP300.PM3CTL =
WPPMP300.PM3CTL And WXPMP300.PM3GRP = WPPMP300.PM3GRP)) ")
.Append(",")
.Append("PM3IM2 = RTrim(( SELECT WXPMP300.PM3IM2 From
WXPMP300 Where WXPMP300.PM3SIT = WPPMP300.PM3SIT And WXPMP300.PM3CTL =
WPPMP300.PM3CTL And WXPMP300.PM3GRP = WPPMP300.PM3GRP)) ")
.Append(",")
.Append("PM3IM3 = RTrim(( SELECT WXPMP300.PM3IM3 From
WXPMP300 Where WXPMP300.PM3SIT = WPPMP300.PM3SIT And WXPMP300.PM3CTL =
WPPMP300.PM3CTL And WXPMP300.PM3GRP = WPPMP300.PM3GRP)) ")
.Append("Where Exists ")
.Append("(")
.Append("Select WXPMP300.PM3GRP ")
.Append("From WXPMP300 ")
.Append("Where WXPMP300.PM3SIT = WPPMP300.PM3SIT ")
.Append("And WXPMP300.PM3CTL = WPPMP300.PM3CTL ")
.Append("And WXPMP300.PM3GRP = WPPMP300.PM3GRP ")
.Append(")")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbUpdate.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 2/. INSERT new Records into the DESTINATION Table

'===========================================================================
==========
Dim sbInsert As New StringBuilder
With sbInsert
.Append("INSERT INTO WPPMP300 ")
.Append("(")
.Append("PM3SIT, ")
.Append("PM3CTL, ")
.Append("PM3GRP, ")
.Append("PM3ACT, ")
.Append("PM3ACS, ")
.Append("PM3SEQ, ")
.Append("PM3IM1, ")
.Append("PM3IM2, ")
.Append("PM3IM3 ")
.Append(")")
.Append("SELECT ")
.Append("RTrim(WXPMP300.PM3SIT) AS Expr1, ")
.Append("RTrim(WXPMP300.PM3CTL) AS Expr2, ")
.Append("RTrim(WXPMP300.PM3GRP) AS Expr3, ")
.Append("RTrim(WXPMP300.PM3ACT) AS Expr4, ")
.Append("RTrim(WXPMP300.PM3ACS) AS Expr5, ")
.Append("RTrim(WXPMP300.PM3SEQ) AS Expr6, ")
.Append("RTrim(WXPMP300.PM3IM1) AS Expr7, ")
.Append("RTrim(WXPMP300.PM3IM2) AS Expr8, ")
.Append("RTrim(WXPMP300.PM3IM3) AS Expr9 ")
.Append("FROM WXPMP300 ")
.Append("WHERE WXPMP300.PM3SIT Not In (SELECT PM3SIT from
WPPMP300) ")
.Append("Or WXPMP300.PM3CTL Not In (SELECT PM3CTL from
WPPMP300) ")
.Append("Or WXPMP300.PM3GRP Not In (SELECT PM3GRP from
WPPMP300) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbInsert.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 3/. DELETE Records from the DESTINATION Table that do NOT
Exist in the EXTRACT Table

'===========================================================================
==========
Dim sbDelete As New StringBuilder
With sbDelete
.Append("Delete from WPPMP300 ")
.Append("Where PM3SIT Not In (Select PM3SIT From WXPMP300)
")
.Append("Or PM3CTL Not In (Select PM3CTL From WXPMP300) ")
.Append("Or PM3GRP Not In (Select PM3GRP From WXPMP300) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbDelete.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using

End Sub

#End Region

#Region "43D/. DoUpdateTable_WPPMP300D()"


'===========================================================================
========================
'
' 43D/. DoUpdateTable_WPPMP300D()
'

'===========================================================================
========================
Private Shared Sub DoUpdateTable_WPPMP300D()

Dim connectionString As String = AppSettings.SQLConnectionString


'===========================================================================
==========
' 1/. UPDATE the DESTINATION Records from the SOURCE Records

'===========================================================================
==========

' NOTE - No Update Required as all maintainable fields (i.e.
Text Descriptions)
' are maintained on the server (via the Browser)


'===========================================================================
==========
' 2/. INSERT new Records into the DESTINATION Table

'===========================================================================
==========
Dim sbInsert As New StringBuilder
With sbInsert
.Append("INSERT INTO WPPMP300D ")
.Append("(")
.Append("PM3SITD, ")
.Append("PM3CTLD, ")
.Append("PM3LIDD, ")
.Append("PM3GRPD, ")
.Append("PM3DS1D, ")
.Append("PM3DS2D, ")
.Append("PM3DS3D ")
.Append(")")
.Append("SELECT ")
.Append("RTrim(WXPMP300D.PM3SITD) AS Expr1, ")
.Append("RTrim(WXPMP300D.PM3CTLD) AS Expr2, ")
.Append("RTrim(WXPMP300D.PM3LIDD) AS Expr3, ")
.Append("RTrim(WXPMP300D.PM3GRPD) AS Expr4, ")
.Append("RTrim(WXPMP300D.PM3DS1D) AS Expr5, ")
.Append("RTrim(WXPMP300D.PM3DS2D) AS Expr6, ")
.Append("RTrim(WXPMP300D.PM3DS3D) AS Expr7 ")
.Append("FROM WXPMP300D ")
.Append("Where WXPMP300D.PM3SITD Not In (SELECT PM3SITD from
WPPMP300D) ")
.Append("Or WXPMP300D.PM3CTLD Not In (SELECT PM3CTLD from
WPPMP300D) ")
.Append("Or WXPMP300D.PM3LIDD Not In (SELECT PM3LIDD from
WPPMP300D) ")
.Append("Or WXPMP300D.PM3GRPD Not In (SELECT PM3GRPD from
WPPMP300D) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbInsert.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 3/. DELETE Records from the DESTINATION Table that do NOT
Exist in the EXTRACT Table

'===========================================================================
==========
Dim sbDelete As New StringBuilder
With sbDelete
.Append("Delete from WPPMP300D ")
.Append("Where PM3SITD Not In (Select PM3SITD From
WXPMP300D)")
.Append("Or PM3CTLD Not In (Select PM3CTLD From WXPMP300D)")
.Append("Or PM3LIDD Not In (Select PM3LIDD From WXPMP300D)")
.Append("Or PM3GRPD Not In (Select PM3GRPD From WXPMP300D)")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbDelete.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using

End Sub

#End Region

#Region "44/. DoUpdateTable_WPPMP400()"


'===========================================================================
========================
'
' 44/. DoUpdateTable_WPPMP400()
'

'===========================================================================
========================
Private Shared Sub DoUpdateTable_WPPMP400()

Dim connectionString As String = AppSettings.SQLConnectionString


'===========================================================================
==========
' 1/. UPDATE the DESTINATION Records from the SOURCE Records

'===========================================================================
==========

Dim sbUpdate As New StringBuilder
With sbUpdate
.Append("Update WPPMP400 ")
.Append("Set ")
.Append("PM4ACT = RTrim(( SELECT WXPMP400.PM4ACT From
WXPMP400 Where WXPMP400.PM4SIT = WPPMP400.PM4SIT And WXPMP400.PM4CTL =
WPPMP400.PM4CTL And WXPMP400.PM4GRP = WPPMP400.PM4GRP)) ")
.Append(",")
.Append("PM4ACS = RTrim(( SELECT WXPMP400.PM4ACS From
WXPMP400 Where WXPMP400.PM4SIT = WPPMP400.PM4SIT And WXPMP400.PM4CTL =
WPPMP400.PM4CTL And WXPMP400.PM4GRP = WPPMP400.PM4GRP)) ")
.Append(",")
.Append("PM4SEQ = RTrim(( SELECT WXPMP400.PM4SEQ From
WXPMP400 Where WXPMP400.PM4SIT = WPPMP400.PM4SIT And WXPMP400.PM4CTL =
WPPMP400.PM4CTL And WXPMP400.PM4GRP = WPPMP400.PM4GRP)) ")
.Append(",")
.Append("PM4IM1 = RTrim(( SELECT WXPMP400.PM4IM1 From
WXPMP400 Where WXPMP400.PM4SIT = WPPMP400.PM4SIT And WXPMP400.PM4CTL =
WPPMP400.PM4CTL And WXPMP400.PM4GRP = WPPMP400.PM4GRP)) ")
.Append(",")
.Append("PM4IM2 = RTrim(( SELECT WXPMP400.PM4IM2 From
WXPMP400 Where WXPMP400.PM4SIT = WPPMP400.PM4SIT And WXPMP400.PM4CTL =
WPPMP400.PM4CTL And WXPMP400.PM4GRP = WPPMP400.PM4GRP)) ")
.Append(",")
.Append("PM4IM3 = RTrim(( SELECT WXPMP400.PM4IM3 From
WXPMP400 Where WXPMP400.PM4SIT = WPPMP400.PM4SIT And WXPMP400.PM4CTL =
WPPMP400.PM4CTL And WXPMP400.PM4GRP = WPPMP400.PM4GRP)) ")
.Append("Where Exists ")
.Append("(")
.Append("Select WXPMP400.PM4GRP ")
.Append("From WXPMP400 ")
.Append("Where WXPMP400.PM4SIT = WPPMP400.PM4SIT ")
.Append("And WXPMP400.PM4CTL = WPPMP400.PM4CTL ")
.Append("And WXPMP400.PM4GRP = WPPMP400.PM4GRP ")
.Append(")")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbUpdate.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 2/. INSERT new Records into the DESTINATION Table

'===========================================================================
==========
Dim sbInsert As New StringBuilder
With sbInsert
.Append("INSERT INTO WPPMP400 ")
.Append("(")
.Append("PM4SIT, ")
.Append("PM4CTL, ")
.Append("PM4GRP, ")
.Append("PM4ACT, ")
.Append("PM4ACS, ")
.Append("PM4SEQ, ")
.Append("PM4IM1, ")
.Append("PM4IM2, ")
.Append("PM4IM3 ")
.Append(")")
.Append("SELECT ")
.Append("RTrim(WXPMP400.PM4SIT) AS Expr1, ")
.Append("RTrim(WXPMP400.PM4CTL) AS Expr2, ")
.Append("RTrim(WXPMP400.PM4GRP) AS Expr3, ")
.Append("RTrim(WXPMP400.PM4ACT) AS Expr4, ")
.Append("RTrim(WXPMP400.PM4ACS) AS Expr5, ")
.Append("RTrim(WXPMP400.PM4SEQ) AS Expr6, ")
.Append("RTrim(WXPMP400.PM4IM1) AS Expr7, ")
.Append("RTrim(WXPMP400.PM4IM2) AS Expr8, ")
.Append("RTrim(WXPMP400.PM4IM3) AS Expr9 ")
.Append("FROM WXPMP400 ")
.Append("WHERE WXPMP400.PM4SIT Not In (SELECT PM4SIT from
WPPMP400) ")
.Append("Or WXPMP400.PM4CTL Not In (SELECT PM4CTL from
WPPMP400) ")
.Append("Or WXPMP400.PM4GRP Not In (SELECT PM4GRP from
WPPMP400) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbInsert.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 3/. DELETE Records from the DESTINATION Table that do NOT
Exist in the EXTRACT Table

'===========================================================================
==========
Dim sbDelete As New StringBuilder
With sbDelete
.Append("Delete from WPPMP400 ")
.Append("Where PM4SIT Not In (Select PM4SIT From WXPMP400)
")
.Append("Or PM4CTL Not In (Select PM4CTL From WXPMP400) ")
.Append("Or PM4GRP Not In (Select PM4GRP From WXPMP400) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbDelete.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using

End Sub

#End Region

#Region "44D/. DoUpdateTable_WPPMP400D()"


'===========================================================================
========================
'
' 44D/. DoUpdateTable_WPPMP400D()
'

'===========================================================================
========================
Private Shared Sub DoUpdateTable_WPPMP400D()

Dim connectionString As String = AppSettings.SQLConnectionString


'===========================================================================
==========
' 1/. UPDATE the DESTINATION Records from the SOURCE Records

'===========================================================================
==========

' NOTE - No Update Required as all maintainable fields (i.e.
Text Descriptions)
' are maintained on the server (via the Browser)


'===========================================================================
==========
' 2/. INSERT new Records into the DESTINATION Table

'===========================================================================
==========
Dim sbInsert As New StringBuilder
With sbInsert
.Append("INSERT INTO WPPMP400D ")
.Append("(")
.Append("PM4SITD, ")
.Append("PM4CTLD, ")
.Append("PM4LIDD, ")
.Append("PM4GRPD, ")
.Append("PM4DS1D, ")
.Append("PM4DS2D, ")
.Append("PM4DS3D ")
.Append(")")
.Append("SELECT ")
.Append("RTrim(WXPMP400D.PM4SITD) AS Expr1, ")
.Append("RTrim(WXPMP400D.PM4CTLD) AS Expr2, ")
.Append("RTrim(WXPMP400D.PM4LIDD) AS Expr3, ")
.Append("RTrim(WXPMP400D.PM4GRPD) AS Expr4, ")
.Append("RTrim(WXPMP400D.PM4DS1D) AS Expr5, ")
.Append("RTrim(WXPMP400D.PM4DS2D) AS Expr6, ")
.Append("RTrim(WXPMP400D.PM4DS3D) AS Expr7 ")
.Append("FROM WXPMP400D ")
.Append("Where WXPMP400D.PM4SITD Not In (SELECT PM4SITD from
WPPMP400D) ")
.Append("Or WXPMP400D.PM4CTLD Not In (SELECT PM4CTLD from
WPPMP400D) ")
.Append("Or WXPMP400D.PM4LIDD Not In (SELECT PM4LIDD from
WPPMP400D) ")
.Append("Or WXPMP400D.PM4GRPD Not In (SELECT PM4GRPD from
WPPMP400D) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbInsert.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 3/. DELETE Records from the DESTINATION Table that do NOT
Exist in the EXTRACT Table

'===========================================================================
==========
Dim sbDelete As New StringBuilder
With sbDelete
.Append("Delete from WPPMP400D ")
.Append("Where PM4SITD Not In (Select PM4SITD From
WXPMP400D) ")
.Append("Or PM4CTLD Not In (Select PM4CTLD From WXPMP400D)
")
.Append("Or PM4LIDD Not In (Select PM4LIDD From WXPMP400D)
")
.Append("Or PM4GRPD Not In (Select PM4GRPD From WXPMP400D)
")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbDelete.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using

End Sub

#End Region

#Region "49/. DoUpdateTable_WPPMP900()"


'===========================================================================
========================
'
' 40/. DoUpdateTable_WPPMP900()
'

'===========================================================================
========================
Private Shared Sub DoUpdateTable_WPPMP900()

Dim connectionString As String = AppSettings.SQLConnectionString


'===========================================================================
==========
' 1/. UPDATE the DESTINATION Records from the SOURCE Records

'===========================================================================
==========

Dim sbUpdate As New StringBuilder
With sbUpdate
.Append("Update WPPMP900 ")
.Append("Set ")
.Append("PM9PG1 = RTrim(( SELECT WXPMP900.PM9PG1 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9PG2 = RTrim(( SELECT WXPMP900.PM9PG2 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9PG3 = RTrim(( SELECT WXPMP900.PM9PG3 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9PG4 = RTrim(( SELECT WXPMP900.PM9PG4 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9PG5 = RTrim(( SELECT WXPMP900.PM9PG5 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9PG6 = RTrim(( SELECT WXPMP900.PM9PG6 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9PG7 = RTrim(( SELECT WXPMP900.PM9PG7 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9PG8 = RTrim(( SELECT WXPMP900.PM9PG8 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9ACT = RTrim(( SELECT WXPMP900.PM9ACT From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9ACS = RTrim(( SELECT WXPMP900.PM9ACS From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9SEQ = RTrim(( SELECT WXPMP900.PM9SEQ From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9IM1 = RTrim(( SELECT WXPMP900.PM9IM1 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9IM2 = RTrim(( SELECT WXPMP900.PM9IM2 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9IM3 = RTrim(( SELECT WXPMP900.PM9IM3 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9PUP = RTrim(( SELECT WXPMP900.PM9PUP From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9RRP = RTrim(( SELECT WXPMP900.PM9RRP From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9TRP = RTrim(( SELECT WXPMP900.PM9TRP From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9STK = RTrim(( SELECT WXPMP900.PM9STK From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9PCK = RTrim(( SELECT WXPMP900.PM9PCK From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9ASS = RTrim(( SELECT WXPMP900.PM9ASS From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9IMP = RTrim(( SELECT WXPMP900.PM9IMP From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9MET = RTrim(( SELECT WXPMP900.PM9MET From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9COM = RTrim(( SELECT WXPMP900.PM9COM From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9FET = RTrim(( SELECT WXPMP900.PM9FET From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9NEW = RTrim(( SELECT WXPMP900.PM9NEW From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9F01 = RTrim(( SELECT WXPMP900.PM9F01 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9F02 = RTrim(( SELECT WXPMP900.PM9F02 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9F03 = RTrim(( SELECT WXPMP900.PM9F03 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9F04 = RTrim(( SELECT WXPMP900.PM9F04 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)), ")
.Append("PM9F05 = RTrim(( SELECT WXPMP900.PM9F05 From
WXPMP900 Where WXPMP900.PM9SIT = WPPMP900.PM9SIT and WXPMP900.PM9CTL =
WPPMP900.PM9CTL and WXPMP900.PM9PRD = WPPMP900.PM9PRD)) ")
.Append("Where Exists ")
.Append("(")
.Append("Select WXPMP900.PM9PRD ")
.Append("From WXPMP900 ")
.Append("Where WXPMP900.PM9SIT = WPPMP900.PM9SIT ")
.Append("And WXPMP900.PM9CTL = WPPMP900.PM9CTL ")
.Append("And WXPMP900.PM9PRD = WPPMP900.PM9PRD ")
.Append(")")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbUpdate.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 2/. INSERT new Records into the DESTINATION Table

'===========================================================================
==========
Dim sbInsert As New StringBuilder
With sbInsert
.Append("INSERT INTO WPPMP900 ")
.Append("(")
.Append("PM9SIT, ")
.Append("PM9CTL, ")
.Append("PM9PRD, ")
.Append("PM9PG1, ")
.Append("PM9PG2, ")
.Append("PM9PG3, ")
.Append("PM9PG4, ")
.Append("PM9PG5, ")
.Append("PM9PG6, ")
.Append("PM9PG7, ")
.Append("PM9PG8, ")
.Append("PM9ACT, ")
.Append("PM9ACS, ")
.Append("PM9SEQ, ")
.Append("PM9IM1, ")
.Append("PM9IM2, ")
.Append("PM9IM3, ")
.Append("PM9PUP, ")
.Append("PM9RRP, ")
.Append("PM9TRP, ")
.Append("PM9STK, ")
.Append("PM9PCK, ")
.Append("PM9ASS, ")
.Append("PM9IMP, ")
.Append("PM9MET, ")
.Append("PM9COM, ")
.Append("PM9FET, ")
.Append("PM9NEW, ")
.Append("PM9F01, ")
.Append("PM9F02, ")
.Append("PM9F03, ")
.Append("PM9F04, ")
.Append("PM9F05 ")
.Append(")")
.Append("SELECT ")
.Append("RTrim(WXPMP900.PM9SIT) AS Expr1, ")
.Append("RTrim(WXPMP900.PM9CTL) AS Expr2, ")
.Append("RTrim(WXPMP900.PM9PRD) AS Expr3, ")
.Append("RTrim(WXPMP900.PM9PG1) AS Expr4, ")
.Append("RTrim(WXPMP900.PM9PG2) AS Expr5, ")
.Append("RTrim(WXPMP900.PM9PG3) AS Expr6, ")
.Append("RTrim(WXPMP900.PM9PG4) AS Expr7, ")
.Append("RTrim(WXPMP900.PM9PG5) AS Expr8, ")
.Append("RTrim(WXPMP900.PM9PG6) AS Expr9, ")
.Append("RTrim(WXPMP900.PM9PG7) AS Expr10, ")
.Append("RTrim(WXPMP900.PM9PG8) AS Expr11, ")
.Append("RTrim(WXPMP900.PM9ACT) AS Expr12, ")
.Append("RTrim(WXPMP900.PM9ACS) AS Expr13, ")
.Append("RTrim(WXPMP900.PM9SEQ) AS Expr14, ")
.Append("RTrim(WXPMP900.PM9IM1) AS Expr15, ")
.Append("RTrim(WXPMP900.PM9IM2) AS Expr16, ")
.Append("RTrim(WXPMP900.PM9IM3) AS Expr17, ")
.Append("RTrim(WXPMP900.PM9PUP) AS Expr17, ")
.Append("RTrim(WXPMP900.PM9RRP) AS Expr18, ")
.Append("RTrim(WXPMP900.PM9TRP) AS Expr19, ")
.Append("RTrim(WXPMP900.PM9STK) AS Expr20, ")
.Append("RTrim(WXPMP900.PM9PCK) AS Expr21, ")
.Append("RTrim(WXPMP900.PM9ASS) AS Expr22, ")
.Append("RTrim(WXPMP900.PM9IMP) AS Expr23, ")
.Append("RTrim(WXPMP900.PM9MET) AS Expr24, ")
.Append("RTrim(WXPMP900.PM9COM) AS Expr25, ")
.Append("RTrim(WXPMP900.PM9FET) AS Expr26, ")
.Append("RTrim(WXPMP900.PM9NEW) AS Expr27, ")
.Append("RTrim(WXPMP900.PM9F01) AS Expr28, ")
.Append("RTrim(WXPMP900.PM9F02) AS Expr29, ")
.Append("RTrim(WXPMP900.PM9F03) AS Expr30, ")
.Append("RTrim(WXPMP900.PM9F04) AS Expr31, ")
.Append("RTrim(WXPMP900.PM9F05) AS Expr32 ")
.Append("FROM WXPMP900 ")
.Append("WHERE WXPMP900.PM9SIT Not In (SELECT PM9SIT from
WPPMP900) ")
.Append("Or WXPMP900.PM9CTL Not In (SELECT PM9CTL from
WPPMP900) ")
.Append("Or WXPMP900.PM9PRD Not In (SELECT PM9PRD from
WPPMP900) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbInsert.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 3/. DELETE Records from the DESTINATION Table that do NOT
Exist in the EXTRACT Table

'===========================================================================
==========
Dim sbDelete As New StringBuilder
With sbDelete
.Append("Delete from WPPMP900 ")
.Append("Where PM9SIT Not In (Select PM9SIT From WXPMP900)
")
.Append("Or PM9CTL Not In (Select PM9CTL From WXPMP900) ")
.Append("Or PM9PRD Not In (Select PM9PRD From WXPMP900) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbDelete.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using

End Sub

#End Region

#Region "49D/. DoUpdateTable_WPPMP900D()"


'===========================================================================
========================
'
' 44D/. DoUpdateTable_WPPMP900D()
'

'===========================================================================
========================
Private Shared Sub DoUpdateTable_WPPMP900D()

Dim connectionString As String = AppSettings.SQLConnectionString


'===========================================================================
==========
' 1/. UPDATE the DESTINATION Records from the SOURCE Records

'===========================================================================
==========

' NOTE - ONLY the Product Description is Updated (Rest
Maintained on Server)

Dim sbUpdate As New StringBuilder
With sbUpdate
.Append("Update WPPMP900D ")
.Append("Set ")
.Append("PM9DS1D = RTrim(( SELECT WXPMP900D.PM9DS1D From
WXPMP900D Where WXPMP900D.PM9SITD = WPPMP900D.PM9SITD and WXPMP900D.PM9CTLD
= WPPMP900D.PM9CTLD and WXPMP900D.PM9LIDD = WPPMP900D.PM9LIDD and
WXPMP900D.PM9PRDD = WPPMP900D.PM9PRDD)) ")
.Append("Where Exists ")
.Append("(")
.Append("Select WXPMP900D.PM9PRDD ")
.Append("From WXPMP900D ")
.Append("Where WXPMP900D.PM9SITD = WPPMP900D.PM9SITD ")
.Append("And WXPMP900D.PM9CTLD = WPPMP900D.PM9CTLD ")
.Append("And WXPMP900D.PM9LIDD = WPPMP900D.PM9LIDD ")
.Append("And WXPMP900D.PM9PRDD = WPPMP900D.PM9PRDD ")
.Append(")")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbUpdate.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 2/. INSERT new Records into the DESTINATION Table

'===========================================================================
==========
Dim sbInsert As New StringBuilder
With sbInsert
.Append("INSERT INTO WPPMP900D ")
.Append("(")
.Append("PM9SITD, ")
.Append("PM9CTLD, ")
.Append("PM9LIDD, ")
.Append("PM9PRDD, ")
.Append("PM9DS1D, ")
.Append("PM9DS2D, ")
.Append("PM9DS3D ")
.Append(")")
.Append("SELECT ")
.Append("RTrim(WXPMP900D.PM9SITD) AS Expr1, ")
.Append("RTrim(WXPMP900D.PM9CTLD) AS Expr2, ")
.Append("RTrim(WXPMP900D.PM9LIDD) AS Expr3, ")
.Append("RTrim(WXPMP900D.PM9PRDD) AS Expr4, ")
.Append("RTrim(WXPMP900D.PM9DS1D) AS Expr5, ")
.Append("RTrim(WXPMP900D.PM9DS2D) AS Expr6, ")
.Append("RTrim(WXPMP900D.PM9DS3D) AS Expr7 ")
.Append("FROM WXPMP900D ")
.Append("Where WXPMP900D.PM9SITD Not In (SELECT PM9SITD from
WPPMP900D) ")
.Append("Or WXPMP900D.PM9CTLD Not In (SELECT PM9CTLD from
WPPMP900D) ")
.Append("Or WXPMP900D.PM9LIDD Not In (SELECT PM9LIDD from
WPPMP900D) ")
.Append("Or WXPMP900D.PM9PRDD Not In (SELECT PM9PRDD from
WPPMP900D) ")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbInsert.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using


'===========================================================================
==========
' 3/. DELETE Records from the DESTINATION Table that do NOT
Exist in the EXTRACT Table

'===========================================================================
==========
Dim sbDelete As New StringBuilder
With sbDelete
.Append("Delete from WPPMP900D ")
.Append("Where PM9SITD Not In (Select PM9SITD From
WXPMP900D) ")
.Append("Or PM9CTLD Not In (Select PM9CTLD From WXPMP900D)
")
.Append("Or PM9LIDD Not In (Select PM9LIDD From WXPMP900D)
")
.Append("Or PM9PRDD Not In (Select PM9PRDD From WXPMP900D)
")
End With

Using con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(sbDelete.ToString(), con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using

End Sub

#End Region
End Class

End Namespace


-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx] On
Behalf Of Peter Vidal
Sent: 16 May 2007 16:44
To: web400@xxxxxxxxxxxx
Subject: [WEB400] SQLing data from a different iSeries

Hi list!

Is there a way that, using NET.DATA, I can access another iSeries (a Test
Box) and SQL data from a file that is there?

Can code the solution?

TIA


Peter Vidal
PALL Corporation / SR Programmer Analyst, IT Development Group 10540 Ridge
Rd., Ste 203, New Port Richey, FL 34654-5111 http://www.pall.com

"Whether we like it or not, asking is the rule of the Kingdom."
Charles H. Spurgeon



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.