I would like to perform a large query operation (which takes many seconds), and store that into a designated table which will act as basically a cache for that query. Is there a way to achieve this just using SQL, as opposed to using a data reader, then inserting that back into the designated table with a sqlceresultset.
View 3 RepliesHi, all experts here,
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
With best regards,
Yours sincerely,
Can anyone tell me or point me in the direction of how I can store select query results to a variable in VB.NET? Im using the SqlDataSource control with dropdowns and textboxes for searching. I want to store the search results in a variable on the button click event.
View 2 Replies View RelatedHi guys, now i was able to get the results of the query i made. and the remaining problem now is how can i store the results to my database? here's the my code: protected void btnGenerate_Click(object sender, EventArgs e) { _con = new SqlConnection(database.evgConnection()); _comm = new SqlCommand("EV_usp_SearchNoticeLocation", _con); _comm.CommandType = CommandType.StoredProcedure; _comm.Parameters.AddWithValue("@Locationid", ddlLocation.SelectedValue); _adapt = new SqlDataAdapter(_comm); DataSet objDataset = new DataSet(); _adapt.Fill(objDataset); GridView1.DataSource = objDataset; GridView1.DataBind(); foreach(DataRow dr in objDataset.Tables[0].Rows) { Response.Write(dr["Docownerid"].ToString()); } } how can i store all the Docownerid to another database table?
View 1 Replies View RelatedI'm writing a search engine and I want to make a search and then, after
I've recieved the results, put them into some sort of temporary(in
mememory) table so that I can do another query on that temporary table.
I saw something about temp tables (in T-SQL) in the
help guide, but I still want to know how to do a query on a regular
table and then store those results in a temporary table to perform a
query on that. Could anyone show me some example or something?
I am looking for an example of a store procedure that will run a existing view and copy the results to a table. Every time it runs the table needs to be truncated. Will run once a day.
My view is a follows
SELECT PClass
FROM mydatabase.dbo.ProductClassDes
WHERE (ProductClass <> '_RBS') AND (ProductClass <> '_EDT') AND (ProductClass <> '_BMS') AND (ProductClass <> '_PAZ') AND (ProductClass <> '_PBC')
View results need to be copied to a table tblCurrentProductClasses that will only contain one field PClass.
One column in my table stores SQL queries(QueryCoulmn). Another coulmn supposed to store the result of those queries(ResultColumn). Can I run an update query or how can I do that? I could not figure out the syntax.
update tablename
set ResultColumn=exec(QueryCoulmn)
thanks
SELECT PATNT_REFNO, PATNT_REFNO_NHS_IDENTIFIER, ARCHV_FLAG
FROM dbo.S_PATIENTS
WHERE (PATNT_REFNO_NHS_IDENTIFIER IS NOT NULL)
SELECT dbo.S_PATIENT_IDS.END_DTTM, dbo.S_PATIENT_IDS.PITYP_REFNO, dbo.S_REFERENCE_VALUES.DESCRIPTION
FROM dbo.S_PATIENT_IDS LEFT OUTER JOIN
dbo.S_REFERENCE_VALUES ON dbo.S_PATIENT_IDS.PITYP_REFNO = dbo.S_REFERENCE_VALUES.RFVAL_REFNO
WHERE (dbo.S_PATIENT_IDS.ARCHV_FLAG = 'N')
i want to select the results from the querys into one table how would i do this ?
I have written several scripts to pull in nested info to the analyzer window. How do I get this data to write to the new table I have created in the database? here is the current script:
select Hierarchy_List.Hierarchy_Label as Hierarchy_Name,
Hierarchy_List.hierarchy_ID as Hierarchy_ID,
Hierarchy_List.Parent_ID as Parent_ID,
frequency_item.manufacturer as Motor_Make,
frequency_item.model as Frame
from hierarchy_list full outer join Frequency_item
ON HIERARCHY_LIST.HIERARCHY_ID = frequency_item.HIERARCHY_ID
where parent_id in (select hierarchy_id from hierarchy_list where parent_id in
(select hierarchy_id from hierarchy_list where parent_id in
(select hierarchy_id from hierarchy_list where parent_id in
(select hierarchy_id from hierarchy_list where parent_id=0)
and parent_id<>0) and parent_id<>0) and parent_id<>0)
and parent_id<>0 and frequency_item.description = 'motor'
I need to move this data to the VAER.Al_Machines and the column names are the same. I can move data via DTS, but it won't work on this because the nested info. Is there a script addendum I can add to this to execute both the search and the transfer in one job so I can automate it? Thanks for any help.
Mick Flanigan
Is there any way i could take the first 50 results of a sql query and store them into 1 table in access and take the next 50 and store them into a second table in access? Is there any SQL statement that will direct where the output gets directed
to?
OR is there a way to have the sql reults paird up with a autonumbered ID?
Doc
Hi everybody need help on the possibility creating a new table from the results of a view or query? below is my table named table1
ID col1 col2
1 a a
2 b d
3 c f
this would be my new table named table2
ID col1 col2 col3
1 a a aa
2 b d bd
3 c f cf
this new table has an additional column by concatenating col1+col2
tried this procedure but is not working
CREATE TABLE AS (SELECT ID, COL1, COL2, COL1+COL2) TABLE2
thanks
How do I save my query results into new table.... The ORIGINAL COLUMN Of course before parsing--- But the only data I want is in the three no name columns---(NO Column Name),(NO Column Name),(NO Column Name)I don’t want the original column saved back but I think it existing in the final query is blocking my Insert Into---
View 2 Replies View RelatedI have this query;
select c.name
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
where t.name= 'Hosea_tblDATA_NOTES'and c.is_identity = 0
order by column_id
and I get these results;
NOTE_ID
NOTE_DESCRIPTION
NOTE_TEXT
NOTE_STATUS
NOTE_STARTDATE
NOTE_ENDDATE
NOTE_AUTHOR
NOTE_LASTUPDATE
I was thinking if it is possible to create a table from the results of this query, my table name(in this case 'Hosea_tblDATA_NOTES') will be a parameter, that whatever table name I pass, with the results I get I will be able to create another table, assuming all data types are varchars, but it should be generic.
I want to create a new table with the about of that query, that output has to be my columns.
Hye Friends,
I'm using a DMX query to get some predictions out of my MiningModel
my DMX query is as follows :
SELECT
predict([x SalaryPredictor].[Emp Gross],20),predict([x SalaryPredictor].[Emp Basic],20)
From
[x SalaryPredictor]
This query is returning me 2 objects of type expressions in my Dataset.
The problem is when I try to drag these 2 fields in my Table i get a "#Error" value
I executed the query in 'Data' tab and found that data is getting returned is a tree like format where "Expression" is at top & values expected ar its child nodes.
I also found out that the returned objects are actually AdomdDataReader , but i'm not able to write a expression to get its values in my tables....
I want to display the values in the following format....
_____________________________
| Emp gross | Emp basic |
---------------------------------------------------
| predicted val 1 | predicted val 2 |
---------------------------------------------------
| .... | ..... |
-- -------------------------------------------------
can anybody help me out in this ????...
Thanks in advance !!!
I am try to insert the results from the query into new temp table but keep geeting an error for Incorrect syntax near ')'.
select * into tempCosting
from
(
select top 10* from itemCode itm
where itm.type= 1
)
I am trying to do a select statement and input the result to a different table how can this be done in one step? Now I am just coping to excel and importing back in this is a real pain.
Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips
Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate
Order by LeaveDate, LeaveTime, Trip
Can anyone show me how to run a prediction query and save the results to a sql table without using the T-SQL OPENQUERY tip here http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx? I am looking for an example in vb.net that I can use in a SSIS script task.
Thanks
I have cubes that hold quite a few calculations and so creating Excel pivot table views from it take a long time. This is even true for Excel 2007.
Now I wonder if it would be possible to write back all the calculation results to a relational table - maybe one that exactly matches the report format - so creating another report would be much faster?
SSRS seems to be a way to go but it does not speed up my Excel case.
I read about write-back in ROLAP and MOLAP but I don't think any of these concepts help me to really speed up my reports.
The closest thing I was able to find so far, which besides seems to do exactly what I want is Microsoft's new PerformancePoint 2007. It's just it seems overkill for my projects and the price is at $20K.
Any suggestions are appreciated.
Dirk
I have a table that was created as follows
create table call_summary (
row_date smalldatetime,
[700] int,
[730] int,
[800] int,
[900] int)
I have a query that returns the following
date interval (int) calls (int)
2008-01-07 00:00:00 700 0
2008-01-07 00:00:00 730 0
2008-01-07 00:00:00 800 0
2008-01-07 00:00:00 830 9
2008-01-07 00:00:00 900 8
I am looking for a way to get my table mentioned above to look like this
row_date [700] [730] [800] [830] [900]
2008-1-7 0 0 0 9 8
does anyone have any slick ideas on how I can accomplish this task?
I have a label on my page that shows a persons name, but in the database it is identified by a ID for indexing purposes. When I do a UPDATE (users can change other info on the page...not the name though), I want to update by the ID, not the actual name. Problem is, I don't have the ID (just the name) on the page, so I would have to do a lookup for the ID in the database. Is there a another way to do this? When I select the persons name for display using the SqlDataSource, it's easy to select the ID too. But how can I use this ID on the UPDATE? I don't want to reinvent the wheel.
View 1 Replies View RelatedI have created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
ALTER PROCEDURE [dbo].[RPT_Cost_copy]
SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM
INTO T1
FROM
(SELECT a.meu, a.mep2, SUM(a.mest) as excst
FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> ''
[code]....
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". I will post the definitions from another effort below.
CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,
[code]....
SQL Server 2008 R2.
I am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set.
DECLARE @tblCustomRange AS TABLE
(
RecordID INTEGER IDENTITY(1,1),
RangeMonth INTEGER,
RangeDay INTEGER
)
DECLARE @Month INTEGER
DECLARE @Day INTEGER
-- Initialize month and day variables.
SET @Month = 8
SET @Day = 11
-- Insert records into the table variable.
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,2)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,27)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (6,10)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (9,22)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (12,16)
-- Select everything from the table variable ordering the results by month, day in
-- descending order
SELECT * FROM @tblCustomRange
WHERE (RangeMonth < @Month) OR
(RangeMonth = @Month AND RangeDay <= @Day)
ORDER BY RangeMonth, RangeDay DESC
I am getting the following resultset:
RecordID RangeMonth RangeDay
----------- ----------- -----------
2 1 27
1 1 2
3 6 10
I am expecting the following resultset:
RecordID RangeMonth RangeDay
----------- ----------- -----------
3 6 10
2 1 27
1 1 2
I believe saving prediction query results to relational tables is possible (the BI studio does it!). I am not clear on how to do this w/o the BI studio, which means if I write a DMX query and want to store its output to a relational table, how do I do it?
Tips, anyone?
Thanks!
Hi,
I am unable to execute the following query against SqlCE 3.1.
Could someone guide me what is wrong.
SELECT CASE WHEN ISNULL(MAX(CONTENT_NUMBER)) THEN 0 ELSE MAX(CONTENT_NUMBER) END + 1
FROM PRELIMINARY_CODES WHERE EXAMID = '38D990D322C94B189FF12AF158AD7B06';
Error Message:
Major Error 0x80040E14, Minor Error 25501
> SELECT CASE WHEN ISNULL(MAX(CONTENT_NUMBER)) THEN 0 ELSE MAX(CONTENT_NUMBER) END + 1
FROM PRELIMINARY_CODES WHERE EXAMID = '38D990D322C94B189FF12AF158AD7B06'
There was an error parsing the query. [ Token line number = 1,Token line offset = 46,Token in error = THEN ]
But when I execute foloowing queries:
1) select MAX(CONTENT_NUMBER)from PRELIMINARY_CODES;
Result: NULL
2) select ISNULL(MAX(CONTENT_NUMBER))from PRELIMINARY_CODES;
Result: 1
Thanks
Sreenaiah
Do data mining results store at some files or in the database? I need the mining results at later time for reporting purpose.
View 1 Replies View Related
I am currently working on an application that requires, insertion of the results of a SQL Query in to the field of another table, in the form of a comma separated values.
For example, to explain things in detail:
create table dbo.phone_details
(country varchar(20),
state varchar(30),
list_of_toll_free_numbers text)
insert into dbo,phone_details
values
( 'USA', 'CA', 'select Phone from phone_table where substring(phone, 1, 3) in ('800', '866', '877', '888')' )
The final output I desire is:
country state list_of_toll_free_numbers
---------- ------- -----------------------------------------
USA CA 8009877654, 8665764398, 8776543219
Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:
Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3
Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road
I'd like the results to be like this:
Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30
Anyone? Thanks in advance!
I am currently upgrading our ppc app (written in .net 2003) to .net 2005 and from sqlce 2.0 to 3.0. The new application runs out memory(storage) when handling lots of data transactions (both in case of using sqlcedatareader, and dataset ). There is no memory leak issue here, sqlce 3.0 simply uses a lot more (3 times more) memory than sqlce 2.0.
Our applicaton runs fine using sqlce 2.0 and .net 2003, but fail due to memory shortage
with the upgrades(which has the same code). Anyone can shed some lights on this issue?
How to use the SQLCE Query Analyser in Windows Mobile 5.0?
I have MSPDB.SDF database in WM 5.0. I can't open this sdf file.
Please update me,
what are the softwares i have to install in my WM 5.0 device?
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies View RelatedI have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
Query
DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);
SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');
SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result
[Code] ....
I've got a db table, that I created in Sever Studio Management and I can se all the data and everything the
problem is that when I load it to the movile device (windows mobile 5.0) it says that the table is NotAble
but in the management works fine, I can't even do a "select * from table", and if you check the query analyzer
you se the table but it has no columns.