Insert With Multiple Sub Queries
May 27, 2008
In Sql 2005, I can't get this to work. I am getting an error that says SQL does not support subquery. SQL has a problem with my subquery. It works fine with one subquery, but adding multiple gives me an an error.
INSERT INTO STU_SUMMARY
(SUMMARY_PERIOD,
CHILD_COUNT, NO_DATA_ERRORS, PROG_CD_DESCREP, TOT_CURR_SPEDSTU)
VALUES (GETDATE(),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
)
View 1 Replies
ADVERTISEMENT
Nov 15, 2007
Hi
I have two queries, one which takes 1 second to execute and the other which takes less that a second to execute.
I need to join them up similar to the following but when I do it take forever for them to execute.
select *
from table
where column not in (select column from table2)
It is quite complex and I have a couple of views included in the queries but it executes quickly on my dev database and when I run it on the live one it takes forever. Granted there is alot more data on the live database but the individual queries only take a second to run.
Does anyone know why there would be such a time increase whenever I join the queries and run them on my live database?
Any help would be appreciated.
Thanks,
Frankie
View 4 Replies
View Related
Aug 11, 2004
*************************************************************
<code> tags added by moderator. Please use <code> and
</code> tags to bracket comments to make code readable.
*************************************************************
I am a beginner to this whole .net thing so my code is probablly scary, but anyway, I am looking for a way to insert records into a database and then get the last ID. I am sure there is a much better way to do this, but here is the code that I have that ins't quite working:
'SQL Insert data'
Dim Conn As SqlConnection
Dim Rdr As SqlDataReader
Dim strConn As String = AppSettings("doConnect")
Dim strSQL As String = "INSERT INTO USERS (username, password, accountNum) VALUES ('" + Email.Text.Trim() + "', '" + password.Text.Trim() + "', '" + Session("accountNum").Trim() + "')"
Conn = New SqlConnection(strConn)
Dim Cmd As New SqlCommand(strSQL, Conn)
Conn.Open()
Cmd.ExecuteNonQuery()
'SQL Get Last ID
Dim ConnGet As SqlConnection
Dim RdrGet As SqlDataReader
Dim strConnGet As String = AppSettings("doConnect")
Dim strSQLGet As String = "select MAX(id) as maxid from USERS"
ConnGet = New SqlConnection(strConn)
Dim CmdGet As New SqlCommand(strSQL, Conn)
ConnGet.Open()
If CmdGet.ExecuteScalar() <> 0 Then
RdrGet = CmdGet.ExecuteReader()
While RdrGet.Read()
Session("theID") = RdrGet("name")
End While
Else
Session("theID") = 0
End If
View 1 Replies
View Related
Jul 23, 2001
Does anyone know how to count multiple columns in a SQL Query from One table and return numbers I can use the following but don't know how to join the two statements to one.
SELECT COUNT(*) AS [NUMBER OF SERVERS]
FROM DBO.OS
WHERE (OSTYPE LIKE '%SER%')
The other statement is
SELECT COUNT(*) AS [NUMBER OF WORKSTATIONS]
FROM DBO.OS
WHERE (CAPTION LIKE '%9%') OR (CAPTION LIKE '%PRO%') OR (CAPTION LIKE '%ME%')
If any one has any idea's please reply or email me directly. I would greatly appreciate it.
View 1 Replies
View Related
Jul 23, 2005
I'm writing an ASP page for a project and it requires multiple queries.However, I'm trying to combine multiple SELECT statements, but can'tfigure out a way that actually works.Basically, here are the SELECT statements I want to combine:SELECT * FROM schoolrequest WHERE SLid=10SELECT SLlastName FROM academicofficeWHERE schoolrequest.SLsendToId=academicoffice.AOidSELECT SLlastName FROM academicofficeWHERE schoolrequest.SLbillToId=academicoffice.AOidSELECT SLlastName FROM academicofficeWHERE schoolrequest.SLrequestorId=academicoffice.AOidSELECT * FROM diaryentry WHERE diaryentry.DEkeyValue=10AND diaryentry.DEdeletedDate IS NULLThe academicoffice table just contains basic contact info, but needs tobe used 3 times in the query to get specific contact info for 3different contacts(SLsendToId, SLbillToId, SLrequestorId)The diaryentry table contains info entered in by students. TheDEkeyValue is actually the primary id of the schoolrequest table(SLid).Any ideas?Thanks,M
View 4 Replies
View Related
Jul 18, 2007
Hello everyone...
I have created a report that pulls data from 5 different tables. I have created a tableadapter for this. I have a sql stored procedure with left joins that I am trying to use but keep getting key violations and not null value violations when I try to use it. I have tried setting the NULLValue to NOT throw exception but that has not helped. I have also tried to writing a query for each table. I can add each query to my tableadapter but I can use them all at the same time for some reason. Can this be done? When I go to my report and try to add fields to it from the tableadapter, I only see the results of one query. I am only trying to SELECT. I am not doing any updates or deletes to the tables on the SQL Server.
Any advice would be greatly appreciated.
View 4 Replies
View Related
Mar 3, 2008
I get a wo_ID and want to query company data. I have the following tables, work_orders (has wo_ID, wo_name and install_id), install_ids (has install_id, comp_id) and customers (comp_id, company_name). The query process goes like this: I get a wo_ID and I need to find the install_id that corresponds with that wo_ID in that same work_orders table. Then take that install_id and find out the comp_id that corresponds with that install_id from the install_ids table. Finally take that customer_id and link it to comp_id in the customers table to find out company data. I need to join it multiple times to get my final data. I have a simple one that works right now with one join and using the install_id, but I don't know how to expand it with multiple joins. Thanks.
View 4 Replies
View Related
Apr 14, 2008
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below./* will remove for aspx page use */USE Database/* these params are on the page in drop down boxes*/DECLARE @ProductID int;DECLARE @BuildID int;DECLARE @StatusID int;/* static params for this sample */SET @ProductID = -1;SET @BuildID = -2SET @StatusID = -3/*the query that will build the datagrid. currently this runs and produces three different result sets.How do I combine these statements so they produce a single set of results? */IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
View 12 Replies
View Related
Dec 25, 2014
If you have a dozen or so queries related to the same thing such as an exception report do you wrangle with the query to make it a one stop shop or do you do something different? Is it possible to drop them into a SP and have them all execute at the same time?
I am running queries for against business rules and wanted to do it the right way. I don't have a solid mentor at work, two guys who are experienced and off a lot but not in the world of reports.
View 8 Replies
View Related
Apr 14, 2008
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below.
/* will remove for aspx page use */
USE Database
/* these params are on the page in drop down boxes*/
DECLARE @ProductID int;
DECLARE @BuildID int;
DECLARE @StatusID int;
/* static params for this sample */
SET @ProductID = -1;
SET @BuildID = -2
SET @StatusID = -3
/*
the query that will build the datagrid. currently this runs and produces three different result sets.
How do I combine these statements so they produce a single set of results?
*/
IF (@ProductID = -1) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))
IF (@BuildID = -2) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))
IF (@StatusID = -3) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
View 15 Replies
View Related
Mar 30, 2008
Hi,
I have the below query that has multiple select statements from the same query. Is it possible to run this more efficiently with a SUM/ CASE statement so that its just one SELECT ?
I just used a SUM(CASE WHEN ....... statement on another query, but I am having trouble figuring out how to integrate it on this one, and if its even possible or worth it.
Thanks very much for any help!!
mike123
CREATE PROCEDURE [dbo].[select_mailbox_Count]
(
@userID int
)
AS SET NOCOUNT ON
SELECT count(*) as totalInbox,
(SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 0 and deletedbyRecipient =0 ) as totalInbox_UnRead,
(SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 2 and deletedbyRecipient =0 ) as totalInbox_UnReplied,
(SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID AND deletedByRecipient = 0 AND deletedBySender = 0 ) as totalOutbox,
(SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID or messageTo =@userID AND deletedByRecipient = 0 and deletedBySender = 0 ) as totalTrash
FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND deletedByRecipient = 0
View 13 Replies
View Related
Dec 2, 2005
I'm cleaning data which involves updating ~12 million rows with threedifferent models, progressively. First clean values using the modelwith finest granularity, then the remainder with the next model,finally what's left using the last model. The first model sets ~1/2 ofthe rows, the second ~1/4, the third ~1/5, and the remaining 5% don'tget updated.It's something like this:UPDATE t SET value=value*m.AdjustmentFactor, updateFlag='updated'FROM Table t JOIN Models m ON ....WHERE m.ModelID='first model' AND t.updateFlag IS NULLStart with 'first model' then 'second model' etc.I'm wondering what happens if I submit all three queries together, oras three separate submissions, waiting for the one before to complete.If I do them all as one group, the query planner might plan for thesecond and third updates based on the initial distribution of values.However, the first update removes half of the rows from consideration,so it seems to me a new plan should be prepared for the second query,based on the distribution at that time. If I highlight the queries inQuery Analyzer and execute, are all three plans created at thebeginning? Does putting GO between them (which I do) make anydifference?This is SQL Server 2000.Thanks,Jim
View 2 Replies
View Related
Dec 31, 2007
I've written a SQL script to go in and pull student grades for honor roll. The first subquery in the script eliminates any students that have any D's, and the second sub query is supposed to give me a only students that have no more than 1 C of any type as the Honor Roll criteria is that a student has to have a GPA between 3.0 and 3.49 and no more than 1 C.
If I run each individual sub query by itself they work and pull the students that they are supposed to. If I run the main query along with the first sub query to eliminate students with D's everything works. If I run the 2nd sub query by itself it will pull the students that meet the criteria. If I run the main query along with both sub queries or the main along with the 2nd sub query it doesn't work.
Here is what the query looks like that I'm trying to run.
Thanks in advance for any help.
select distinct i.personid, i.lastname + ',' + ' ' + i.firstname as student, p.studentnumber, e.grade,
t.name as Term, gs.score, c.name as course, sec.teacherdisplay
from gradingscore gs
JOIN [identity] i on i.personid = gs.personid
JOIN person p ON p.personid = i.personid
JOIN enrollment e on e.personid = p.personid
JOIN term t on t.termid = gs.termid
JOIN v_TermGPA tg on tg.personid = gs.personid and tg.calendarid = gs.calendarid
JOIN v_GpaTermCalc tc ON tc.personid = gs.personid and tc.calendarid = gs.calendarid
JOIN section sec on sec.sectionid = gs.sectionid
JOIN course c on c.courseid = sec.courseid
where gs.calendarid = 20 and t.name = '2nd 6wk' and tc.gpa between 3.0 and 3.49 and
e.enddate is null
--Sub Query to eleminate students with any D's in scores
and p.personid not in
(select distinct gs1.personid from gradingscore gs1
join Term t1 ON t1.termid = gs1.termid
join v_TermGPA tg1 ON tg1.personid = gs1.personid
where gs1.calendarid = 20 AND t1.name ='2nd 6wk'and
tc.gpa >=3.0 AND tc.gpa <= 3.49
and gs1.score IN('D+','D', 'D-')
--Sub Query to eliminate students with more than 1 C of any type in scores.
and gs.personid not in
(select distinct gs2.personid from gradingscore gs2
join Term t2 ON t2.termid = gs2.termid
join v_TermGPA tg2 ON tg2.personid = gs2.personid
where gs2.calendarid = 20 AND t2.name ='2nd 6wk'and tc.gpa >=3.0 AND tc.gpa <= 3.49
and gs2.score IN('C+','C', 'C-')
group by gs2.personid
HAVING count(gs2.score)<=1))
order by i.[student]
View 6 Replies
View Related
Feb 26, 2008
Hi,
I have some questions about making muliple T-SQL queries againt an SQL-server. I retrieve all rows from an SQL-server table called ActivityGroup and add the result to a Radio Button List. Suppose, I also wish to add the total sum of every "ActivityGroup" value to the Radio Button List. I guess this ought to be done using a SELECT COUNT statement and then retrieved using the Executescalar method?
The question is do I need to copy the code for the SELECT ('*) statement and make the neccessary changes for the SELECT COUNT statement and Executescalar method or is there a more simple way? Is it possible to use two command statements in the same code block?
The total sum is going to be calculated using the values from both an SQL-server and an Access database. The OleDb sample below is what I refer to as a code block. I appreciate any help!
string connectionString = ConfigurationManager.ConnectionStrings["ServetteConnectionString"].ConnectionString;
string sQuery = "SELECT * FROM Aktivitetsgrupper where aktivitetsid = " + Request["AktivitetsId"];
OleDbConnection oOleDbConnection = new OleDbConnection(connectionString);
oOleDbConnection.Open();
OleDbCommand command = new OleDbCommand(sQuery, oOleDbConnection);
OleDbDataReader reader = command.ExecuteReader();
int test = 12;
while (reader.Read())
{
rblAktivitetsgrupper.Items.Add(new ListItem(String.Format("{0}, platser", reader["aktivitetsGruppNamn"]), reader["aktivitetsGruppID"].ToString()));
}
View 4 Replies
View Related
Dec 15, 2006
Hi,
I'm having problems manipulating the results from multiple count(*) queries.
I have a table 'RECOMMENDATIONS' holding a field called 'SCORE'. This field holds a number from 1 to 10 and has no null or zero figures.
I currently have the following three queries:
1) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)
2) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
3) SELECT COUNT(*) FROM RECOMMENDATIONS
I now need to combine these three queries so that i can divide and multiply the resulst: (query 2/query 1)*query 3
My initial idea was:
SELECT (COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)))* (SELECT COUNT(*) FROM RECOMMENDATIONS) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
... but this gives a result of "0". I then stripped out the multiplication section to see if the divide was working:
SELECT COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
... and again the result was "0"
Please could someone help me!!
Ian
View 2 Replies
View Related
Jan 4, 2015
I was messing around with stored procedures and I was wondering if creating a SP that populates a single table for reporting is a good idea?
I have ~10 queries that I have to currently run manually and was hoping to drop them into a physical table and then leverage that single table to pull into excel.
Some of my queries use virtual tables or CTE's, this is to get the aggregate set correctly.
Essentially I am working out of a data warehouse and would like to eventually get all my queries in one SP or something similar and then call that query for a insert.
Speaking of which could you create a SP that has several selects than with that output drops the records into a single table by using an insert into query so the data from the all the queries would line up into the right columns?
View 1 Replies
View Related
Oct 17, 2013
I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.
I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?
select
clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type = 'CC'
group by clientnumber;
OUTPUT currently looks like this:
1234 2 0 0
1234 0 1 0
1234 0 0 4
Is it possible to result in this, along with a flag with the criteria above?:
1234 2 1 4 Y
View 3 Replies
View Related
Jul 10, 2007
Hi everyone,
I am reading about the Rollback transaction but I'm not sure if it's the feature I need.
My application is going to update a few tables, but my programmer said that it will be done using more than 1 transaction.
For example, if I want to create a new employer with all the detailed informations, tables will be filled when the user complete the insertion of a part of data. If he decides to abort the operation, i'd like to delete all the values inserted before with the other queries. Is it possible to create a "savepoint" and roll back all transactions processed from this savepoint?
thank you!
View 6 Replies
View Related
Jul 23, 2005
HiI am trying to write two Select * statements to the same text fileusing bcp (from a stored procedure).But cannot find a way of appending to a file using bcp.Does anyone know if this is possible or is there another way of writingmultiple queries to a file from a stored procedure?ThanksCaro
View 2 Replies
View Related
Sep 15, 2005
Greetings,I have what seems a simple problem....I want to save the output from the following queries in 1 file in'SQL Query Analyser', but I can only save each 'grid' separately.I have about 30 queries in total. 'Messages' tells me the number ofrows affected but I need the data.So can I save all the grids together at the same time in 1 file ?ThanksMikePRINT 'HEAT 3510'SELECT f.persfirstname AS First_Name,f.perslastname AS Last_Name,a.rsrchqnumber AS Personal_nr,b.asgtassignmentid AS Contract_nr,ISNULL(c.tishcode,'') AS TS_Id,ISNULL(d.tpitpayrollcode,'6900') AS Pay_Code,d.tpitdaydate AS Payroll_Date,c.tishactualenddate AS TS_end_dateFROM resources aINNER join assignments b ON b.asgtrsrcguid = a.rsrcguidINNER join timesheets c ON c.tishasgtguid = b.asgtguidINNER join users e ON e.userguid = a.rsrcuserguidINNER join persons f ON e.userpersguid = f.persguidLEFT OUTER join timesheetpayrollitems d ON d.tpittishguid =c.tishguidWHERE a.rsrchqnumber = 80000147and b.asgtassignmentid = 0000001234and c.tishactualenddate > '20050614'and (d.tpitdaydate > '20050614'or d.tpitdaydate IS null)order by d.tpitdaydateSELECT f.persfirstname AS First_Name,f.perslastname AS Last_Name,a.rsrchqnumber AS Personal_nr,b.asgtassignmentid AS Contract_nr,ISNULL(c.tishcode,'') AS TS_Id,c.tishactualenddate AS TS_end_date,g.thisdaydate AS TSH_dateFROM resources aINNER join assignments b ON b.asgtrsrcguid = a.rsrcguidINNER join timesheets c ON c.tishasgtguid = b.asgtguidINNER join users e ON e.userguid = a.rsrcuserguidINNER join persons f ON e.userpersguid = f.persguidINNER join timesheethistory g ON g.thistishguid = c.tishguidWHERE a.rsrchqnumber = 80000147and b.asgtassignmentid = 0000001234and c.tishactualenddate > '20050619'order by g.thisdaydatePRINT 'HEAT 3213'
View 3 Replies
View Related
Dec 7, 2007
I am currently using enterprise manager to run multiple queries on asingle table in a DB. I refresh these queries every few minutes. Dueto the huge number of them I was looking for a better way (or should Ijust say "a way") to manage/save these queries so I can recall themeasier/faster for monitoring purposes. Suggestions?TIA.
View 4 Replies
View Related
Jul 20, 2005
I have the following fieldname in an Access_Table:Field 1 = Cust_ID (Primary key)Field 2 = DateField 3 = DescriptionField 4 = Inv_NoField 5 = AmountMy SQL Input syntax are:"Select distinct Cust_ID,Date,Description,Inv_No,Amount fromAccess_Table WHERE Date <Now()-30 And Date >Now()-60 AND Date =Main.Date"It failed to generate the result I want,anywayMy "Required" Output is:Cust_ID Date Description Inv_No >30DYS >60DYS8000 21/05/2004 PC RAM 2008000 26/06/2004 Modem 180---------------------------------------------------------------Total: 180 200---------------------------------------------------------------8001 22/04/2004 Cable 508001 23/05/2004 HD 210---------------------------------------------------------------Total: 260---------------------------------------------------------------Grand Total: 180 460can anyone help? ThanksFrom:Cady Steldyn*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jan 26, 2007
Hi there,
I am new to ssis.I need to execute multiple queries to update number of tables in sqlserver.Pleasesuggest me the right way to do this.
Thanks in Advance.
Regards
Vinod
View 4 Replies
View Related
Oct 4, 2007
Hey guys,
In my DB i have hundreds on queries setup for all different reporting purposes..
We have just changed they way our system handles costing and are moving from a average cost function to a standard cost function.. This in turns, means that i need to replace any reports where i use the average cost field and replace it with the standard cost field.
Is it possible, that i can do a full search and replace over all my queries, looking for a specific field name and replacing it with something else.
I am dreading the idea of opening each individual queries and checking if it exists..
If anyone knows of software or someway to write a SP to accomadate this, please let me know. I would be most grateful.
Thanks
Scotty
View 3 Replies
View Related
Feb 13, 2008
I have a situation where I need to do some reporting for simular asset records but the data is listed on different database from the same server.
I was curious as to the way to write the queries that I will need.
Lets say I have 3 databases and I am looking for a asset of some type and the ID is a serial number.
The serial will be on the main table from each database. Is there a way I can set a relation from a view between these records?
I.E.:
1: Database A -DSNA_tblMaiin - Serialnumber
2: Database B -DSNB_tblMaiin - Serialnumber
3: Database C -DSNC_tblMaiin - Serialnumber
I believe that this is a dot notation question.
Thanks in advance,
Gene
View 8 Replies
View Related
Feb 11, 2004
Is there any way to use a graphical designer to build your insert & update SQL statements in Enterprise manager? I mean Access has an EASY way to build them, surely SQL does too?
I would just build them in Access and copy the SQL, but then I'm stuck replacing all the "dbo_" with "dbo." and other little nuances.
View 3 Replies
View Related
Oct 8, 2014
I have 4 tables which I am extracting 2 distinct values;
Select Distinct UniId, PID from dbo.Event1
Select Distinct UniId, PID from dbo.Event2
Select Distinct UniId, PID from dbo.Event3
Select Distinct UniId, PID from dbo.Event4
Then, I want to select Distinct between these 4 tables (Event1, Event2, Event3 and Event4)
Then insert the distinct records of the 4 tables to the final table - dbo.EventLookup .
View 2 Replies
View Related
Aug 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
Jul 22, 2004
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)
SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
View 5 Replies
View Related
Apr 4, 2006
Hello. I'm having some difficulty trying to output the results of two seperate queries into the same temporary table.
Does anyone know if it is possible to use the UNION operator to output the results into a temporary table?
Select * From TableA
UNION
Select * From TableB
<---output result to temporary table here--->
Alternatively, is it possible to output the results of two queries in to the same temporary table without the UNION clause?
The following statement fails on the second SELECT INTO due to the fact that #MyTempTable already exists.
Select * INTO #MyTempTable FROM TableA
Select * INTO #MyTempTable FROM TableB
Thanks in advance.
View 2 Replies
View Related
Jul 14, 2014
I have to create a SQL job which will run around 50 queries and email results when the query gets some results.These are like quality checks which run to check errors in the system so if any query(out of 50 queries) returns some results an email with the details will be sent .So if 5 queries return results 5 emails with the details will be sent .
I think of something like A table which has one column as the query .What will be the best way of handling such a scenario , may be need an SSIS package with steps ?
View 3 Replies
View Related
Sep 19, 2013
I have to do following scenario,
if 1st query Then 2nd Query
Else 'Msg'
How Can i do this using Case Statement??how can do this by Other way??
View 5 Replies
View Related
Jan 8, 2008
I am trying to select the max for each Schedule_Number when ProcessDescription = 'Exit Cold Rinse'. In the following table, 2:00 and4:00 should be returned for 12345_001 and 12345_002 respectively. Ihave tried to join the two queries and would like to use the currentSchedule_Number as one of the criteria when determining the max.Below is some code that I've used thus far? Does anyone havesuggestions?*Schedule_Number * Process_Description * TMDT*12345_001 * Exit Cold Rinse * 1/07/08 01:00:00 PM*12345_001 * Enter Cold Rinse * 1/07/08 01:30:00 PM*12345_001 * Exit Cold Rinse * 1/07/08 02:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 02:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 03:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 03:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 04:00:00 PMSelect *From(Select distinct Schedule_NumberFrom dbo.Process_DataWHERE left(Schedule_Number,5) = '12345') as Query1left join(Select *From dbo.Process_DataWhere TMDT =(SELECT Max(TMDT)FROM dbo.Process_DataWHERE Process_Description = 'Exit Cold Rinse' andQuery1.Schedule_Number = Query2.Schedule_Number)) as Query2on Query1.Schedule_Number=Query2.Schedule_Number
View 1 Replies
View Related
