Is there a way to attach a single record to a Select expression without another table?
Here's my case: I am developing a Select query for a report. The report has to show a row for each month, but not all months are necessarily represented in the data, so the result is a report with some of the months missing. Can't have that. To "force" the appearance of all months, I'd like to UNION my Select query to "dummy" records for each month with zeros in the data fields, or something like that. But I'd like to avoid creating a table just to hold the months for the Union. Is there a way to attach records to the result of a Select without those records coming from a table? I'd like to be able to say something like this:
"Do this SELECT. Now, append a row with the following values..."
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
I have three tables "UNIONED" and I want the this inserted into a table.
INSERT INTO mytable (A, B, C, D, E) SELECT A, B, C, D, E FROM (SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2) UNION (SELECT A, B, C, D, E FROM a_lot_of_parts) GROUP BY A,B,C,D,E
This part alone works just like I want it:
(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2) UNION (SELECT A, B, C, D, E FROM a_lot_of_parts)
I just want it inserted inte stated columns in my table.
I've stared so much at this I'm "homeblind", ie I can't see the forest because of all the trees...
This may be a dumb question, but I can't seem to get the syntax right. I have two temp tables that have the same columns, I want to do a union on them and store the results in a temp table. Any ideas?
Ie.
select * from #tmpTable1 union select * from #tmpTable2 into #tmpTable3
Right now, a client of mine has a T-SQL statement that does thefollowing:1) Create a temp table.2) Populate temp table with data from one table using an INSERTstatement.3) Populate temp table with data from another table using an INSERTstatement.4) SELECT from temp table.Would it be more efficient to simply SELECT from table1 then UNIONtable 2? The simply wants to see the result set and does not need tore-SELECT from the temp table.
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
i am do the following: insert into table_a ([field1a], [field2a], [field3a]) select field1b, field2b, field3b from tableb where datefield1b >= {variable datefield1} and datefield1b <= {variable datefield2} which is working. HOWEVER, i need to add/include 1 more record from tableb. My first inclination is to do a UPDATE to add that first record BUT i am wondering if there is a way to do a UNION to get that record? i need the next 1 RECORD that has a datefield1b value that is < {variable datefield1} so if my date range is 01/01/2007 thru 12/31/2007 i need to also include the previous 1 record from tableb that is < 01/01/2007. that date could be from 1 month to 24 months old. I don't want all those records because there could be a whole lot of them in there.
I'm having trouble creating a temp table out of a select statement that uses multipe union alls.
Here's what I have, I'm trying to get the results of this query into a temp table...
select parent, (select cst_id from co_customer (nolock) where cst_key = Parent) as cst_id, (select cst_name_cp from co_customer (nolock) where cst_key = Parent) as cst_name_cp, (select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent) as Parent_Total_assets, sum(own_assets) as Total_child_own_assets
from ( Select parent, Child, (select org_own_assets_ext from dbo.co_organization_ext where org_cst_key_ext = child) as Own_assets
from (Select Cst_key as Child, dbo.return_org_parent(cst_key,0,1) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,1) is not null union all
Select Cst_key as Child, dbo.return_org_parent(cst_key,0,2) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,2) is not null union all
Select Cst_key as Child, dbo.return_org_parent(cst_key,0,3) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,3) is not null union all
Select Cst_key as Child, dbo.return_org_parent(cst_key,0,4) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,4) is not null union all
Select Cst_key as Child, dbo.return_org_parent(cst_key,0,5) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,5) is not null union all
Select Cst_key as Child, dbo.return_org_parent(cst_key,0,6) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,6) is not null union all Select Cst_key as Child, dbo.return_org_parent(cst_key,0,7) as Parent from co_customer (nolock) where cst_type = 'Organization' and cst_delete_flag = 0 and dbo.return_org_parent(cst_key,0,7) is not null )as c ) as d
group by parent
having sum(own_assets) <> (select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent)
SELECT plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr, FROM plan2008 GROUP BY plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr
UNION
SELECT fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr FROM fsp_auftrag GROUP BY fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
My problem is that each table contains additional values like art_amount, art_turnover etc... whereby the first table contains plan values while the second table contains actual values.
My goal is to get plan as well as the actual values in one row, how is that possible? If I put the values into each of the selects I get two rows, which is not the wished output.
Is it possible to join the tables after the union took place?
-- populate declare @i int set @i = 1000 while @i > 0 begin insert into dbo.test1 select @i, '1.' + cast(@i as varchar(5)) set @i = @i - 1 end
insert into dbo.test2 select 1, '2.1' union all select 2, '2.2' go
-- create view create view dbo.vw_Test as select1 as QueryID, TestName fromdbo.Test1 union all select2 as QueryID, TestName fromdbo.Test2; go
-- this works as i want, only scans table dbo.Test2 select * fromdbo.vw_Test whereQueryId = 2
-- joining to a table triggers scan of both tables in view: declare @table table (QueryID int) insert into @table select 2;
selectvt.TestName fromdbo.vw_Test vt join@table t on vt.QueryID = t.QueryID
Using the showplan I can see why the optimizer ends up scanning all tables, but maybe there is a way to force it to use the QueryID param evaluation earlier in the filtering.
I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.
What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).
I have tried 2 statements:
#1: SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#)) GROUP BY tblTable1.Name, tblTable1.INDate ORDER BY tblTable1.Name;
#2: SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#)) UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));
Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.
Example:
NameINOUT John Smith1/1/20141/2/2014 John Smith1/1/2014(blank)
I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?
We have a table with 500+ columns. The data is non-normalized, i.e. there are four groups of fields for for "people", followed by data that applies to all people in the row (a "household").For ad-hoc queries, and because I wanted to index columns within each person (person 1's age, person 2's age, etc.), I used UNION:
SELECT P1Firstname AS FirstName, P1LastName as LastName, P1birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns> UNION SELECT P2Firstname AS FirstName, P2LastName as LastName, P2birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>
I could get at least the P1... P2... P3... columns with PIVOT, but then I believe I'd have to JOIN back to the row anyway for the "household" columns. Performance of UNION good, but another person here chose to use PIVOT instead.I can' find any articles on PIVOT vs. UNION for "de-flattening".
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET ( 'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES', 'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR, ''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item' COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns) ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order) DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
I just found out that I can do an ORDER BY clause on entire records set retrieve from a query that combines several sub queries with UNION from different tables with the same structure... so this is great to know, BTW, is this a new feature of MSSQL 2K ? I don't recall being able to do this in MSSQL 7 or 6.5.
Anyway, the main question is, can I use the TOP command in a query that has UNION in it?? Meaning, there are two queries (or more) from two tables (or more) and I need to fetch the top 10 records by an ORDER BY clause from the combined results, when I try to add each sub query TOP 10 the results are not correct at all, when I try to add TOP 10 only to the first query hoping that the analyzer will refer to the whole query, it's selecting TOP 10 from the first query and combines it with all the records from the others...
So, can anyone help? I hope the problem is understood.
I am trying to join to different queries into one table ( I accomplished this)
Next I need to ADD or SUM the results of 2 rows to form a single row.
As you can see in the query below, I run 2 separate queries and use ' ' as a place holder for the UNION to work. I get duplicate rows, one with a value and the other with a '0'. I want to have a single row.
Any help is greatly appreciated!
(SELECT v_gs_supportedpackages.ProdID0 as 'Product Name', v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site', Count(ProdID0) as '# copies installed', '' as '# legitimate copies installed'
FROM v_R_System SYS, v_GS_Workstation_Status HWSCAN, v_gs_SupportedPackages inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE SYS.ResourceId = HWSCAN.ResourceId AND SYS.ResourceId = v_gs_SupportedPackages.ResourceId AND v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
(SELECT v_gs_supportedpackages.ProdID0 as 'Product Name', v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site', '' as '# copies installed', Count(ProdID0) as '# legitimate copies installed'
FROM v_R_System SYS, v_GS_Workstation_Status HWSCAN, v_gs_SupportedPackages inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE SYS.ResourceId = HWSCAN.ResourceId AND SYS.ResourceId = v_gs_SupportedPackages.ResourceId AND v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60) AND DateDiff(Day,HWSCAN.LastHWScan,GetDate()) <= '20'
By default, the UNION operator removes duplicate rows from the result set. If you use ALL, all rows are included in the results and duplicates are not removed.
Why is it assumed that one would want the duplicates removed by default? Isn't that what SELECT DISINCT is for?
I was wondering if there is a way I could write this query as one query using union instead of 3 different queries?
SELECT columna as column,count(ID) as Applications FROM tablea GROUP columna
SELECT columna as column2, count(ID) as Approved FROM tablesa where substring(APP_DATE,1,6) >'200304' and in 'Approved') GROUP BY columna
SELECT columna as column3, count(ID) as Booked, sum(AMT) as amt, sum(AMT)/count(ID) as lavg from tablea where substring(APP_DATE,1,6) >'200304' and STATUS in('book') group by columna
I am doing a UNION of 8 views(with 3 million rows in each view approx). I am sure that there is NO DUPLICATE data between the views. SO specifying a UNION ALL would be better than specifying a UNION in this case?
Thanks!
Prakash.P The secret to creativity is knowing how to hide your sources!
Hi All I want to do the following report and I want the 2nd select statement to appear at the bottom to display the totals
SELECT Item,B.Mar, B.Apr, B.May, B.Jun,B.Jul, B.Aug, B.Sep, B.Oct, B.Nov, B.Dec, B.Jan FROM fncWineSales(2007) AS A UNION SELECT 'Total',SUM(B.Mar), SUM(B.Apr), SUM(B.May), SUM(B.Jun),SUM(B.Jul), SUM(B.Aug), SUM(B.Sep), SUM(B.Oct), SUM(B.Nov), SUM(B.Dec), SUM(B.Jan) FROM dbo.fncWineSales (2007) AS B
Hello,Bear with me (not had much sleep last night), pls see following ddl,dml and comments for what is desired, I don't have a problem gettingdesired result(s), however, I'm wondering if there's another (better)solution than UNION operator in this case. TIA.-- DDLCREATE TABLE #TMP (col varchar(10));-- DMLinsert into #TMPvalues('A124');insert into #TMPvalues('A127');insert into #TMPvalues('A12728');insert into #TMPvalues('A17282');insert into #TMPvalues('BCD');insert into #TMPvalues('BCD');insert into #TMPvalues('CDSS');insert into #TMPvalues('DS');insert into #TMPvalues('YUUEI');-- goal: get one row with col data starting with 'A' and distict rowsfor the restselect top 1 colfrom #TMPwhere col LIKE 'A%'UNIONselect distinct colfrom #TMPwhere col NOT LIKE 'A%'
