MAJOR PROBLEM!!!


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: MAJOR PROBLEM!!!

  1. #1
    Hamish Guest

    MAJOR PROBLEM!!!


    I have a major problem. I have done a simple query:

    SELECT tblDispense.DispenseID, tblDispense.DispenseDate, tblDispenseItem.DispensedAs,tblDepartments.DeptName
    FROM tblDispense INNER JOIN
    tblDispenseItem ON tblDispense.DepartmentID =
    tblDispenseItem.DepartmentID INNER JOIN tblDepartments ON tblDispenseItem.DepartmentID
    = tblDepartments.DepartmentID

    I then put this into a recordset. I need to be able to send all of this data
    to another server with the same database, by building INSERT statments.
    Does anyone know how i can do it?
    I am thinking of looping through the recordset and marking the start and
    end of each table in query. Then building INSERT Statements based on what
    is within the loop. Then i would move the next table in the query and build
    INSERT statments out of that.
    Is this the way to do it?

    Please Help

    Thanks

  2. #2
    Arthur Wood Guest

    Re: MAJOR PROBLEM!!!


    Do ALL of the related tables that you use in this query ALREADY exist on the
    other Database?

    Do those tables contain the necessary Data so that the JOINS will in fact
    be valid?

    (by the way, this is NOT a simple query - you are carrying out TWO INNER
    JOINS - a SIMPLE query would be "Select * from tblDispense")

    If the Tables DO NOT already exist in the traget database, then you will
    need to CREATE them, FIRST.

    If the tables DO exist, but are not populated with the necessary data (for
    tblDispenseItems and tblDepartments), then those tables MUST be populated
    FIRST, before you can begin to think about moving the data from your query.

    Finally, since you refer to fields from all three tables in your query, this
    query WILL NOT be able to be used to INSERT records into another Table, whether
    that table is IN the current database, or another database. This kind of
    a query will ALWAYS create a recordset that is NON_UPDATEABLE, and also cannot
    be used to create records in another table.

    Arthur Wood



    "Hamish" <hamishlucas@blueyonder.co.uk> wrote:
    >
    >I have a major problem. I have done a simple query:
    >
    >SELECT tblDispense.DispenseID, tblDispense.DispenseDate, tblDispenseItem.DispensedAs,tblDepartments.DeptName
    >FROM tblDispense INNER JOIN
    >tblDispenseItem ON tblDispense.DepartmentID =
    >tblDispenseItem.DepartmentID INNER JOIN tblDepartments ON tblDispenseItem.DepartmentID
    >= tblDepartments.DepartmentID
    >
    >I then put this into a recordset. I need to be able to send all of this

    data
    >to another server with the same database, by building INSERT statments.


    >Does anyone know how i can do it?
    >I am thinking of looping through the recordset and marking the start and
    >end of each table in query. Then building INSERT Statements based on what
    >is within the loop. Then i would move the next table in the query and build
    >INSERT statments out of that.
    >Is this the way to do it?
    >
    >Please Help
    >
    >Thanks



  3. #3
    challis Guest

    Re: MAJOR PROBLEM!!!


    This code is frm a similar utility I wrote for a single table. you can easily
    modify it to create it from views and joined recordsets....
    the principal remains the same : (use ADOX if you need to determine the original
    view structure and the like) !! the objects need to exist as expected in
    the destination database, else need to be created.



    Private Sub ScriptDataItems( _
    ByRef strTableName As String, _
    ByRef cnn As ADODB.Connection, _
    ByRef lnghFile As Long)

    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim strValues As String
    Dim strFields As String
    Dim strInsert As String

    Set rst = New ADODB.Recordset

    rst.Open _
    "Select * from " & strTableName, _
    cnn, _
    adOpenStatic

    If Not (rst.BOF And rst.EOF) Then
    rst.MoveFirst

    Print #lnghFile, "/*"
    Print #lnghFile, "Statements to insert data into " & strTableName
    Print #lnghFile, "*/"
    Print #lnghFile, "DELETE FROM " & strTableName
    Print #lnghFile, "GO"
    ' determine if there is an identity column
    Print #lnghFile, "IF EXISTS("
    Print #lnghFile, " SELECT * FROM INFORMATION_SCHEMA.Columns"
    Print #lnghFile, " WHERE "
    Print #lnghFile, " COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)
    + '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1 AND"
    Print #lnghFile, " OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)
    + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 AND"
    Print #lnghFile, " TABLE_SCHEMA = 'dbo' AND"
    Print #lnghFile, " TABLE_NAME = '" & strTableName & "')"
    Print #lnghFile, "SET IDENTITY_INSERT " & strTableName & " ON"
    Print #lnghFile, "GO"

    Do Until rst.EOF

    strFields = "("
    strValues = "("

    For Each fld In rst.Fields
    If Not IsNull(fld.Value) Then
    strFields = strFields & fld.Name & ", "
    strValues = strValues & GetDelimitedFieldVal(fld) & ", "
    End If
    Next fld

    strFields = Left$(strFields, Len(strFields) - 2) & ")"
    strValues = Left$(strValues, Len(strValues) - 2) & ")"

    strInsert = _
    "INSERT INTO [dbo].[" & strTableName & "] " & strFields & " VALUES
    " & strValues

    Print #lnghFile, strInsert

    rst.MoveNext
    Loop

    Print #lnghFile, "GO"
    ' determine if there is an identity column
    Print #lnghFile, "IF EXISTS("
    Print #lnghFile, " SELECT * FROM INFORMATION_SCHEMA.Columns"
    Print #lnghFile, " WHERE "
    Print #lnghFile, " COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)
    + '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1 AND"
    Print #lnghFile, " OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)
    + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 AND"
    Print #lnghFile, " TABLE_SCHEMA = 'dbo' AND"
    Print #lnghFile, " TABLE_NAME = '" & strTableName & "')"
    Print #lnghFile, "SET IDENTITY_INSERT " & strTableName & " OFF"
    Print #lnghFile, "GO"
    Print #lnghFile, ""

    End If

    Set fld = Nothing
    Set rst = Nothing

    End Sub ' ScriptDataItems


    Private Function GetDelimitedFieldVal( _
    ByRef fld As ADODB.Field) _
    As String

    Dim str As String

    Select Case fld.Type

    Case _
    ADODB.DataTypeEnum.adInteger, _
    ADODB.DataTypeEnum.adBigInt, _
    ADODB.DataTypeEnum.adDecimal, _
    ADODB.DataTypeEnum.adCurrency, _
    ADODB.DataTypeEnum.adDouble, _
    ADODB.DataTypeEnum.adNumeric, _
    ADODB.DataTypeEnum.adSingle, _
    ADODB.DataTypeEnum.adTinyInt, _
    ADODB.DataTypeEnum.adSmallInt

    str = CStr(fld.Value)

    Case _
    ADODB.DataTypeEnum.adDate, _
    ADODB.DataTypeEnum.adDBDate, _
    ADODB.adDBTimeStamp

    'If fld.Value > Now Then
    str = Format(Now, "'dd-mmm-yyyy'")
    ' Else
    ' str = Format(fld.Value, "'dd-mmm-yyyy'")
    ' End If

    Case _
    ADODB.DataTypeEnum.adBoolean

    If fld.Value = True Then
    str = "1"
    Else
    str = "0"
    End If

    Case Else

    str = "'" & Replace(fld.Value, "'", "''") & "'"

    End Select

    GetDelimitedFieldVal = str

    End Function ' GetDelimitedFieldVal






    "Arthur Wood" <wooda@nospam.com> wrote:
    >
    >Do ALL of the related tables that you use in this query ALREADY exist on

    the
    >other Database?
    >
    >Do those tables contain the necessary Data so that the JOINS will in fact
    >be valid?
    >
    >(by the way, this is NOT a simple query - you are carrying out TWO INNER
    >JOINS - a SIMPLE query would be "Select * from tblDispense")
    >
    >If the Tables DO NOT already exist in the traget database, then you will
    >need to CREATE them, FIRST.
    >
    >If the tables DO exist, but are not populated with the necessary data (for
    >tblDispenseItems and tblDepartments), then those tables MUST be populated
    >FIRST, before you can begin to think about moving the data from your query.
    >
    >Finally, since you refer to fields from all three tables in your query,

    this
    >query WILL NOT be able to be used to INSERT records into another Table,

    whether
    >that table is IN the current database, or another database. This kind of
    >a query will ALWAYS create a recordset that is NON_UPDATEABLE, and also

    cannot
    >be used to create records in another table.
    >
    >Arthur Wood
    >
    >
    >
    >"Hamish" <hamishlucas@blueyonder.co.uk> wrote:
    >>
    >>I have a major problem. I have done a simple query:
    >>
    >>SELECT tblDispense.DispenseID, tblDispense.DispenseDate, tblDispenseItem.DispensedAs,tblDepartments.DeptName
    >>FROM tblDispense INNER JOIN
    >>tblDispenseItem ON tblDispense.DepartmentID =
    >>tblDispenseItem.DepartmentID INNER JOIN tblDepartments ON tblDispenseItem.DepartmentID
    >>= tblDepartments.DepartmentID
    >>
    >>I then put this into a recordset. I need to be able to send all of this

    >data
    >>to another server with the same database, by building INSERT statments.

    >
    >>Does anyone know how i can do it?
    >>I am thinking of looping through the recordset and marking the start and
    >>end of each table in query. Then building INSERT Statements based on what
    >>is within the loop. Then i would move the next table in the query and build
    >>INSERT statments out of that.
    >>Is this the way to do it?
    >>
    >>Please Help
    >>
    >>Thanks

    >



  4. #4
    Sue Harsevoort Guest

    Re: MAJOR PROBLEM!!!

    I don't think the statement in your last paragraph is correct, but maybe I
    am just reading it wrong. You can insert records from a query with multiple
    tables into another table, I think you can even update records in the
    underlining tables through a SQL statment at least, maybe not using
    rs.Update.

    Sue

    "Arthur Wood" <wooda@nospam.com> wrote in message
    news:3dc02c6a$1@tnews.web.devx.com...
    >
    > Finally, since you refer to fields from all three tables in your query,

    this
    > query WILL NOT be able to be used to INSERT records into another Table,

    whether
    > that table is IN the current database, or another database. This kind of
    > a query will ALWAYS create a recordset that is NON_UPDATEABLE, and also

    cannot
    > be used to create records in another table.
    >
    > Arthur Wood




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center