Combining datatables

[TMI]

Due to the length of my select statement and number of or statements in the WHERE clause. I have to run two seperate select statements and create two seperate datatables. Now I want to insert datatable2 into datatable1. I know that it is probably something simple but I cant seem to figure out how to do it. Help!

[Robby]

Why can't you make it a single statement?

[TMI]

As soon as I add one more OR statement to the select statement. It takes way to long for the query to run. I have let it run for 11 minutes and it still does not finish. If I seperate them it only takes 2 seconds for both queries to run. here is the select statement that I would like to use but can not due to the time-out. If I remove the last OR cluse. it runs in 1 second.

 

sqlArdisMat = "SELECT jSepp.DBO.bom_part.board_id AS Material," & _

" '" & PRI & "' AS P," & _

" '" & RICHTING & "' AS D," & _

" MI.Inventory_number AS Inv_Num," & _

" MI.thick_config AS Thick," & _

" MI.length_config AS Length," & _

" MI.width_config AS Width," & _

" '" & AANT & "' AS Qty," & _

" '" & NOLIMIT & "' AS Available," & _

" '" & MINBRDBOVEN & "' AS TUpper," & _

" '" & NOMBRDBOVEN & "' AS NUpper," & _

" '" & MINBRDRECHTS & "' As TRight," & _

" '" & NOMBRDRECHTS & "' AS NRight," & _

" '" & MINBRDONDER & "' As TBottom," & _

" '" & NOMBRDONDER & "' As NBottom," & _

" '" & MINBRDLINKS & "' AS TLeft," & _

" '" & NOMBRDLINKS & "' As NLeft," & _

" '" & PRIJS & "' AS Price," & _

" '" & IMPORTCOND & "' AS Importcondition," & _

" '" & PRODUNIT & "' AS ProdU," & _

" '" & REFER & "' AS Reference," & _

" '" & SCHROOTW & "' AS ScrapeV," & _

" '" & PREF & "' AS U," & _

" dsc.type_description" & _

" FROM (jSepp.DBO.bom_part" & _

" left OUTER JOIN pdm.dbo.def_surface_codes dsc" & _

" ON (dsc.surface_id = SUBSTRING(jSepp.DBO.bom_part.board_id,8,5)" & _

" OR dsc.surface_id = SUBSTRING(jSepp.DBO.bom_part.board_id,13,5)" & _

" )" & _

" )," & _

" PDM.DBO.Master_Inventory MI, " & _

" jSepp.DBO.elev_def, " & _

" jSepp.DBO.elev_item" & _

" where jSepp.DBO.bom_part.job_id = '" & curProject & "'" & _

" and jSepp.DBO.elev_def.job_id = '" & curProject & "'" & _

" and jSepp.DBO.elev_def.phase_id = '" & Phase & "'" & _

" and jSepp.DBO.elev_def.elevation_id = jSepp.DBO.elev_item.elevation_id" & _

" and jSepp.DBO.bom_part.group_id = jSepp.DBO.elev_item.group_id" & _

" and jSepp.DBO.elev_item.product_type = 'C'" & _

" and jSepp.DBO.bom_part.part_family_pattern <> ''" & _

" and jSepp.DBO.elev_item.job_id = jSepp.DBO.elev_def.job_id" & _

" and MI.width_config > 775" & _

" AND (dsc.type_description in ('TFM', 'Veneer')" & _

" and (MI.generic_id = SUBSTRING(jSepp.DBO.bom_part.board_id,1,7)" & _

" AND ((MI.surface_id1 = SUBSTRING(jSepp.DBO.bom_part.board_id,8,5)" & _

" AND MI.surface_id2 = SUBSTRING(jSepp.DBO.bom_part.board_id,13,5))" & _

" OR (MI.surface_id1 = SUBSTRING(jSepp.DBO.bom_part.board_id,13,5)" & _

" AND MI.surface_id2 = SUBSTRING(jSepp.DBO.bom_part.board_id,8,5))))" & _

" OR" & _

" (dsc.type_description NOT in ('TFM', 'Veneer')" & _

" and MI.generic_id = SUBSTRING(jSepp.DBO.bom_part.board_id,1,7)" & _

" AND ((MI.surface_id1 = ''" & _

" OR MI.surface_id2 = '')))" & _

" OR" & _

" (LEN(jSepp.DBO.bom_part.board_id) = 7" & _

" and MI.generic_id = SUBSTRING(jSepp.DBO.bom_part.board_id,1,7))" & _

'" and MI.generic_id = SUBSTRING(jSepp.DBO.bom_part.board_id,1,7)" & _

" OR" & _

" (LEN(jSepp.DBO.bom_part.board_id) = 7" & _

" and MI.Product_group = SUBSTRING(jSepp.DBO.bom_part.board_id,1,3)" & _

" and MI.Identifier = SUBSTRING(jSepp.DBO.bom_part.board_id,4,4))" & _

" )" & _

" GROUP BY jSepp.DBO.bom_part.board_id," & _

" MI.thick_config, " & _

" MI.length_config," & _

" MI.width_config," & _

" MI.Inventory_number," & _

" dsc.type_description" & _

" ORDER BY jSepp.DBO.bom_part.board_id, MI.length_config"

 

 

As you can see, this is a pretty long select statement. That is why I am using two queries and need to insert one table into the other.

[TheGuru]

A Union would work

 

Use a Union, just as fast.

 

Select fieldone, fieldtwo from table1

UNION

Select Fieldone, Fieldtwo from table2