none
How to use multiple select statments RRS feed

  • Question

  • I have an excel document that I need to import into my sql 2008 server the only issue that I have ran into is when I specify the select statement I need to get two different sets from the sheet so the statements look like this.

     

     

    select * from [sheet1$]
    select top 1 * from [sheet2$]
    


    I get an error saying that my from clause is wrong but if I separate the select statements I don't get an error

    If I run them separately I get the right results but I can't run them at the same time

     



    • Edited by Powmax Tuesday, September 20, 2011 3:11 PM
    Tuesday, September 20, 2011 2:05 PM

Answers

All replies

  • Try

    select TOP 1 * from [Sheet2$]


    KSG
    Tuesday, September 20, 2011 3:04 PM
  • oops thats what it is actually select TOP 1 * from [sheet2$]
    Tuesday, September 20, 2011 3:08 PM
  • You cannot query two different sheets in one select statement, you would need to do the following where both Sheets have no header defined in this case. Use the same command object to execute both sheet1 and sheet2 queries. Also both sheets need the same amount of columns for the following two work and would also guess the same data types (have not checked be pretty sure this is true).

    So if Sheet1 had 20 rows after the import row is done there will be 21 rows in sheet1

    Private Sub TwoSheets()
        Dim cn As New System.Data.OleDb.OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
    
        Dim Builder As New OleDbConnectionStringBuilder With _
            {.DataSource = "test.xls", .Provider = "Microsoft.Jet.OLEDB.4.0"}
        Builder.Add("Extended Properties", "Excel 8.0; IMEX=1;HDR=No;")
        cn.ConnectionString = Builder.ConnectionString
    
        cmd.CommandText = "SELECT * FROM [Sheet1$]"
    
        cn.Open()
    
        Dim dr As System.Data.IDataReader = cmd.ExecuteReader
    
        Dim dtSheet1 As New DataTable
        dtSheet1.Load(dr)
    
        cmd.CommandText = "SELECT TOP 1 * FROM [Sheet2$]"
        Dim dtSheet2 As New DataTable
        dr = cmd.ExecuteReader
        dtSheet2.Load(dr)
    
        dtSheet1.ImportRow(dtSheet2.Rows(0))
    
        For Each row As DataRow In dtSheet1.Rows
            Console.WriteLine("{0} {1} {2}", row(0), row(1), row(2))
        Next
    
        cn.Close()
    
    End Sub
    
    

     


    KSG
    Tuesday, September 20, 2011 6:34 PM
  • select * from [sheet1$]

    union all


    select * from [sheet2$]


    Matt
    Wednesday, September 21, 2011 5:04 AM
    Moderator