I have to pull values from a mysql table, then loop through the result set using the value in an mssql query as shown below. I also have an array ($all_lobs[]) of about 100 values that must be looped through for each value pulled from the mysql table:
$tod=date("n/j/Y",time());
//this is pulling the data from the mysql table
$query2="select CustId from prospect_CustId ";
$result2=mysql_query($query2,$link_id_mysql);
while($custs=mysql_fetch_row($result2))
[Code] .....
If I pull only 100 records from the mysql table, this takes about 1 second to run. However, if I pull 200 records, it takes about 60 secs to run. And, if I pull 300 records, it takes about 200 secs to run. After about 500 records, it takes almost a second per record to run!
Since I have 20,000+ records to pull, this takes hours...
Unfortunately, we are not allowed to modify the mssql tables at all, only query them.
Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc tblGroups - with GroupID, GroupName tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups. What I want is a list of all contacts, with their IDs, names and what groups they are related to:
We can save query output save as CSV file directly from the Query Analyzer window. I have done it at last few year before. Now I need it.Can anyone please give the one example for the same.
Out of one messed up table I need to create two related tables. I am stuck in trying to get 'some' of the columns from the messed up table to the new table. How can I select only the columns I need and insert them in the new table? Thanks in advance!
hi all i have the following stored procedure. declare @AreaID int select @AreaID = 1
select DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT from table1 INNER JOIN table2 ON table1.JobId = table2.JobEvent_JobId where (table1.Job_AreaId = @AREAID) the there are about 300 AREAID's so my question is do i do a "LOOP" or is that no efficient and chews up resources on the server ?what would be a nice clean way to do this ? thanksrobby
I am using Report Writer for Ingres II.Is it possible to write a query in a loop?e.g. My table is like thistime position09:01 pos0109:02 pos0309:02 pos0109:04 pos05Can I loop a query to count the number of times each position occurs in each30 minute period in a day?I wish to generate a report that goes something like09:00 09:30 10:00----------------------------------------pos01 3 5 3pos02 0 6 4pos03 4 3 1
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.
Hi All. How do i loop over to extract data out of a xml parameter in order to insert that into a table along with other input. For example if the xml is <Id>1</Id> <Id>2</Id> <Id>3</Id> I want to input values into table (1,data2,data3) and (2,data2,data3) and (3,data2,data3). How do i do that?
Hi,I'm probably missing something obvious (either that or doing this totally wrong).I'm trying to use a nested loop to generate the following results:Unit Day1 Day2 Day3 Day4 Day5Name1 25 45 89 54 76Name2 48 54 81 74 98What I have so far is this:WHILE @FCount < @TotalFoodUnitsBEGINSELECT (SELECT Unit FROM tbl_acc_FoodVenues WHERE UnitID = (@FCount + 1)) AS Unit WHILE @FDCount < @Days BEGIN SELECT (SELECT FdRevenue_a FROM tbl_acc_aud_SportsAudits WHERE AudDate = DATEADD(day, @FDCount, @pdStartDate)) AS Rev SET @FDCount = @FDCount + 1 END SET @FCount = @FCount + 1ENDAny suggestions please
I am quite new in query building so I would like to know where can I find some useful information about if, for,.. clauses to use in query analizer. I would like to set a variable for date(datetime) and use it in if, for,... clause to get results for each day in selected month.For example:
SELECT Uporabnik AS Expr1, MIN(Ura) AS Expr3, MAX(Ura) AS Expr5, COUNT(*) AS Expr4, Datum AS Expr2 FROM BatchIndeksNEW GROUP BY Uporabnik, Datum HAVING (Datum = CONVERT(DATETIME, '2001-11-30 00:00:00', 102)) ORDER BY COUNT(*) DESC
I'm a relative novice at using sql and I'm struggling with a particular problem. I'm not sure if this is the appropriate place to post this sort of thing but after much fruitless research I need to try and enlist someone's help.
I have a table of pay dates with a paid amount. I want to write a statement that creates a table that breaks those out into the daily amount paid. So it would create a table with a row for each date in the pay period and the amount paid each day.
For example: My existing table has an employee Bob who was paid $1000.00 on January 14. I want then create a table that has records for January 1 - 14 and $71.43 for each date. I think it I need some kind of loop for the whole thing to run through but I can't get the syntax right. I've thought about creating a temp table with all of the dates in that pay period but again, in order to create that temp table I feel like I need some sort of while loop that creates the rows for each date.
Any help or suggestions would be greatly appreciated. I'm at wits end .
@strSql is a dynamic query in while loop which can return single record, single row of records, multiple rows of records.
So only if it returns single record then I have to store it otherwise convert to xml before storing.
1) If it returns 1 record(1row and 1 column) then save as it is. 2) if it returns a row with more than 1 columns then convert to xml before saving. 2) if it returns data rows then convert to xml before saving.
I currently have an asp script that is generating a 12 month rolling report. From asp I'm running a for loop with 12 iterations, each one sending the following query:
select count(a.aReportDate) as ttl from findings f left outer join audits a on a.aID = f.auditID where f.findingInvalid <> 1 and month(aReportDate) = " & Mo & " and year(aReportDate) = " & Yr
where the Mo and Yr variables are incremented accordingly.
I actually have 4 sets of data being pulled back to populate a graph, so this results in 48 queries with each page load! Obviously not ideal. So I'm hoping to reduce this to 4 queries. I was playing with the following in enterprise manager:
DECLARE @DT DATETIME DECLARE @CNT INT SET @DT = '10/31/07' SET @CNT = 1 WHILE(@CNT < 12) BEGIN select count(a.aReportDate) as ttl from findings f left outer join audits a on a.aID = f.auditID where f.findingInvalid <> 1 and month(aReportDate) = month(@DT) and year(aReportDate) = year(@DT)
SET @CNT = @CNT + 1 END
I haven't yet added any logic to increment the date, but my concern is that it looks like it is returning 12 separate results. Is there any way to combine this all into one resultset that will be passed back to my asp script? Hopefully this makes sense?
Suggestions on a completely different approach would also be welcome.
I want to make a SP to update table Product with information I get from table Orderdetail.
Create Procedure UpdateVoorraad §OrderId (int) As Select ProductId, Tal From Orderdetail where OrderId = @OrderId
-- this query get info from table orderdetail : ProductId (integer) and Tal (smallint)
-- Tal = Number of Products
-- Here I want to loop through the query above
-- and for each record in the query I want to update
-- table Product.
Update Product Set Product.Voorraad = Product.Voorraad - Tal where ProductId = ProductId
To do this must I make a create a tempory table, store the query result in the table loop through the table and update table product, or can I try to create a function without a temporary table.
I need to create a query to list all the subfolders within a folder.
I have a database table that lists the usual properties of each of the folder.
I have another database table that has two columns
1. Parent folder 2. Child folder
But this table maintains the parent child relationship only to one level.
For example if i have a folder X that has a subfolder Y and Z. And Y has subfolders A and B. and B has subfolder C and D and C has subfolder E and F
The database table will look like
parentfolder child folder X Y X Z Y A Y B B C B D C E C F
I want to write a query which will take a folder name as the input and will provide me a list of all the folders and subfolders under it. The query should be based on the table (parent - child) and there should not be any restriction on the subfolder levels to search and report for.
I have been banging my head to do this but i have failed so far. Any help on this will be highly appreciated.
I'm have created a data flow that uses an OLEDB source with a SQL Query. In the WHERE statement of this query is a condition for the storecode. I want to figure out how to create a loop that will cycle through a list of storecodes using a variable which is passed to the dataflow in turn to the OLEDB source's query and runs through the process for each store.
The reason i'm using a loop is because there are about 15 million records that are merge joined with 15 million others which is causing a huge performance problem. I'm hoping that by looping the process with one store at a time it should be faster. Any ideas would be greatly appreciated.
Hi,I've written a job to export user and database permissions for alld/b's on a server. As you can see below, the T-SQL commands are thesame for each d/b. Can anyone assist with regard to re-writing this sothat any new d/b's added do not require ammending the job (loop)?Thx,GC.use mastergoSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'use msdbgoSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'use test1goSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'use test2goSELECT db_name()EXEC sp_helpuserEXEC sp_helprotect NULL, NULL, NULL, 'o s'
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate ORDER BY t.topicid ASC,th.lastpostdate DESC Whose result set is as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 On modifying the query to:- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid) ORDER BY t.topicid ASC,th.lastpostdate DESC I get the result set as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
Hi to all,I just need to get two fields from a table and manipulate the resultsin next query of a procedure.I planned to code like what you seebelow,create procedure marks1as@ sql1 as varchar(50)@ sql1=select registerno ,subjectcode from mark;beginselect * from marksetting where registerno='@sql1.registerno' andsubjectcode='@sql1.subjectcode';endcan it be possible to get the results as shown in the code? elsepropose an alternative for this scenario.Thanks in Advance.
Hi,all, I ran the two queries and I thought it would be the same, but it's different. Can you explain to me.
Query 1: result---52 rows select s.InsuredSurname, s.email from studyUSA s join interMedical I on s.email=I.email where convert(char(10), s.enrolldate, 126)>= '2004-01-01' and convert(char(10), s.enrolldate, 126) <='2005-05-20' and (s.agentcode not like '162%') and (s.agentcode not like '17%') and s.agentcode <> '130844'
Query 2: result--14 rows
select s.InsuredSurname, s.email from tis_studyUSA s where convert(char(10), s.enrolldate, 126)>= '2004-01-01' and convert(char(10), s.enrolldate, 126) <='2005-05-20' and s.email IN (Select I.Email from tis_InterMedical I) and (agentcode not like '162%') and (agentcode not like '17%') and agentcode <> '130844'
I have 1 table "Progress"P_no b_no status build_date----------------------------------------------------------------25 1 First_slab 2006/4/525 1 second slab 2006/5/625 2 first slab 2006/1/225 2 third slab 2006/2/3o/p should be asPno,bno, status, max(build_date)sample o/p can be as below25 1 second slab 2006/5/625 2 third slab 2006/2/3Thanks in Advance.
