asked on Append records to a SQL Query
I am not sure if append is the correct word. What I am trying to do is pull information from another database and put it at the end of the exisitng information I am showing. The following is my SQL query what I want to do is query another database with this query but have that information show up after the 1st query of information.
sql = "select orgstrUPC, orgstrUPC + ' - (' + orgstrUname + ')' from cms.dbo.tblOrganization " _
& "where orgstrUPC not in (select strUIC from tblUIC where intTaskForceID = " & id & ") " _
& "AND (orgstrUName NOT LIKE '%FMS%') AND (orgstrUName NOT LIKE '%FWD%') AND (orgstrUName NOT LIKE '%RSP%') " _
& "order by orgstrUPC"
After this query is finished I then need to show information from another database here as well. the database is cmsAF and the tables are strUIC and strUnit.
So in the end this would all be on big query that show all this unit information but the 1st query would show up first and then the second one would follow.
sql = "select orgstrUPC, orgstrUPC + ' - (' + orgstrUname + ')' from cms.dbo.tblOrganization " _
& "where orgstrUPC not in (select strUIC from tblUIC where intTaskForceID = " & id & ") " _
& "AND (orgstrUName NOT LIKE '%FMS%') AND (orgstrUName NOT LIKE '%FWD%') AND (orgstrUName NOT LIKE '%RSP%') " _
& "order by orgstrUPC"
After this query is finished I then need to show information from another database here as well. the database is cmsAF and the tables are strUIC and strUnit.
So in the end this would all be on big query that show all this unit information but the 1st query would show up first and then the second one would follow.
ASP.NETSQL
Last Comment
ee_rlee
hi, is this what you want?
sql = "select orgstrUPC, orgstrUPC + ' - (' + orgstrUname + ')' from cms.dbo.tblOrganization " _
& "where orgstrUPC not in (select strUIC from cms.dbo.tblUIC where intTaskForceID = " & id & ") " _
& "AND (orgstrUName NOT LIKE '%FMS%') AND (orgstrUName NOT LIKE '%FWD%') AND (orgstrUName NOT LIKE '%RSP%') " _
& "order by orgstrUPC " _
& "UNION ALL " _
& "select orgstrUPC, orgstrUPC + ' - (' + orgstrUname + ')' from cmsAF.dbo.strUnit " _
& "where orgstrUPC not in (select strUIC from cmsAF.dbo.strUIC where intTaskForceID = " & id & ") " _
& "AND (orgstrUName NOT LIKE '%FMS%') AND (orgstrUName NOT LIKE '%FWD%') AND (orgstrUName NOT LIKE '%RSP%') " _
& "order by orgstrUPC"
ASKER
I receive an incorrect syntax error
Incorrect syntax near the keyword 'UNION'.
sql = "select o.orgstrUPC, o.orgstrUPC + ' - (' + o.orgstrUname + ') - ' + convert(varchar(10), u.intMaxPersonnel) from tblUIC as u " _
& "inner join cms.dbo.tblOrganization as o on u.strUIC = o.orgstrUPC where u.intTaskForceID = " & id & " order by o.orgstrUPC " _
& "UNION All " _
& "select strUIC + ' - (' + strUnit + ')' from tblAirForceUic " _
& "where strUIC not in (select strUIC from cmsAF.dbo.strUIC where intTaskForceID = " & id & ") " _
& "order by strUIC"
Thanks
Incorrect syntax near the keyword 'UNION'.
sql = "select o.orgstrUPC, o.orgstrUPC + ' - (' + o.orgstrUname + ') - ' + convert(varchar(10), u.intMaxPersonnel) from tblUIC as u " _
& "inner join cms.dbo.tblOrganization as o on u.strUIC = o.orgstrUPC where u.intTaskForceID = " & id & " order by o.orgstrUPC " _
& "UNION All " _
& "select strUIC + ' - (' + strUnit + ')' from tblAirForceUic " _
& "where strUIC not in (select strUIC from cmsAF.dbo.strUIC where intTaskForceID = " & id & ") " _
& "order by strUIC"
Thanks
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
union all
select 2 as dbSrc, * from db2.dbo.tbl2
order by dbSrc