Can i shorten this multiple create/drop statement and run it once

[Siyanna]

Hi GUYS

Can I shorten this so it runs once and not having to run the command update several times?

Many Thanks

Sub RunBatch()
      
Dim cmd As ADODB.Command
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim strDB As String
Dim strlogin As String
Dim strpass As String
Dim connection_string As String
Dim ws As Worksheet
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
Set ws = Worksheets("Outstanding")
Application.ScreenUpdating = True
   
  'Loads database login details
    Database = "MYDB"     '(Schema Details)
    UserId = "TEST1' '(MyLogin UserId)"
    Password = "TEST2" '(MyPassword)
    
    '//Connection string  'Provider
    connection_string = "Provider=OraOLEDB.Oracle.1;Password=" & Password & ";Persist Security Info=False;User ID=" & UserId & ";Data Source=" & Database & ";Extended Properties=PLSQLRSet=1;DistribTX=0;OLE DB Services = -1"
                        
    'On Error GoTo ConnectionError:
    
        With cn
            .ConnectionString = connection_string
            .Open
        End With

Varname1 = ""
Varname1 = Varname1 & "call master.drop_table ('drop table MI166_A') "
'//Run SQL Command
        With cmd
            .ActiveConnection = cn
            .CommandType = adCmdText
            .CommandText = Varname1
            .Execute
        End With
Varname11 = ""
Varname11 = Varname11 & "create table MI166_A as " & vbCrLf
Varname11 = Varname11 & "( " & vbCrLf
Varname11 = Varname11 & "select  /*+ PARALLEL(a,32) */ " & vbCrLf
Varname11 = Varname11 & "        * " & vbCrLf
Varname11 = Varname11 & "from    CIS.TVP068ACTIVITY a " & vbCrLf
Varname11 = Varname11 & "where   TS_COMPLETED is null " & vbCrLf
Varname11 = Varname11 & ") "
'//Run SQL Command
        With cmd
            .ActiveConnection = cn
            .CommandType = adCmdText
            .CommandText = Varname11
            .Execute
        End With
Varname12 = ""
Varname12 = Varname12 & "create index IDX_MI166_A1 on MI166_A (NO_ACCOUNT, CD_COMPANY_SYSTEM) "
'//Run SQL Command
        With cmd
            .ActiveConnection = cn
            .CommandType = adCmdText
            .CommandText = Varname12
            .Execute
        End With
Varname13 = ""
Varname13 = Varname13 & "create index IDX_MI166_A2 on MI166_A (NO_EMPL_ASSGN, CD_COMPANY_SYSTEM)"
'//Run SQL Command
        With cmd
            .ActiveConnection = cn
            .CommandType = adCmdText
            .CommandText = Varname13
            .Execute
        End With
End Sub

 

[Joe4]

A simple one-line SQL code like:

Delete * From [TableName]

will delete all the records in a table, but not the table itself.

 

[Siyanna]

Hi Xenou - i use this temp table to join on to other tables - so how can i update this table using the select statement and then use this table to join on to other tables

I can delete the data using the delete statement but not sure how i can use this table to join on to other tables if the data is empty. If i use a select statement then how do i store the result of the statement in the table?

 

[xenou]

There's no difference between deleting all the records in a table (plan 1), or dropping the table and recreating it then using it (plan 2). Plan 2 is what you were doing before. Plan 1 is the same thing, but instead of "DROP Table X; CREATE Table X;" it is just "DELETE FROM Table X;".