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.
When I am using a SQL Query I have an ability to control whether or not I am able to Include the Column Headers when copying or saving results.
The control exists in the Options > Query Results > Results to Grid > Include column headings etc.
My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query. The idea is that when I setup a view it€™s a drag/drop type of query building (query building for dummies if you will). Once I have a view and click the Execute icon it will return all the records selected by the View. However, when I click the upper left/top box to select all rows and column and then try to copy/paste the records into Excel all the data copies just fine but the field name/column headers are not there. How can I get the header fieldname date to copy/paste from View result set that I'm able to copy from a Query result set? Thank you, Mike
I'm using MS SQL Server Management Studio. How do copy my store procedures that are on the SQL Server 2000 to SQL Server 2005? What are my options? I don't have admin rights...only rights to my database.
I have the following code to create a view. Instead of having a view I would like to convert this code to create a store procedure so the data is saved in a table.
SELECT TOP (100) PERCENT StockCode, MAX(categ) AS categ, MAX(flavor) AS flavor, MAX(Type) AS Type FROM (SELECT RTRIM(KeyField) AS StockCode, RTRIM(AlphaValue) AS categ, RTRIM(AlphaValue) AS flavor, RTRIM(AlphaValue) AS Type, DateValue FROM companyB.dbo.AdmFormData WHERE (0 = 1) UNION ALL
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.
Hi 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?
I'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 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.
Hi, I wanted to create an exact database in another server, so I generated script of all tables,views, store procedures and ran the script on the new server. I was able to have all objects in the new server. but when I run the following sql from sql query analyser, I get nothing in return. What do I need in order to get a valid response.
Ali
/*Truncate from all tables*/ select 'Truncate table ' + name from sysobjects where type ='u' order by name
/*Count all table rows from all tables*/ select 'select count(*) as ' +''+ name + ' from ' + name from sysobjects where type ='u' order by name
/*View all sp*/
select * from sysobjects where type ='p' and name not like 'dt%' order by name
/*View all triggers*/
select * from sysobjects where type ='tr' order by name
/*View all Views*/ use master select * from sysobjects where type ='v' order by name
Hi I am new to SQL programming. This is what I am trying to do in store procedure. Alter a table - adding two columns. Then update those columns Both the above action in same procedure . When I execute them - it complains that the columns I am adding in the first part of store procedure , does not exists. So the SP looks like this create store procedure <name> as alter table <name> ADD column_1 nvarchar (256), column_2 nvarchar (256);
update table set column_1 = <condition> column_2 = <condition>
The SQL complains - invalid column name column_1 invalid column name column_2
HI I have a problem related Store Procedure, that i am trying to extact a value from Database (Like FirstName,LastName,Email Address) through Store Procedure and Display it in the DropDownList(Like: FirstName LastName ,(xyz@xyz.com)) , and this is working correctly. Now i try to check the value at the same time if it is NULL value in the Database then pass EmptyString to the DropDownList Like ("" "" ,(xyz@xyz.com)) how i can do that in the store procedure. Comments will be appreciated.
Does anyone know how to save the output of a stored procedure into a table? I would like to be able to use some procedures such as the sp_helpfile and dbcc sqlperf(logspace) and save to output into a table. Does anyone have any ideas?
While writing store procedure in db most of Time i will Use common Table to write select queries for selecting more than seven table whether it reduce speed performance or it won't
I have a INSERT INTO where i retunr the result from a store procedure. But I want to only insert the data if the row not already exist. How can i do that? (See Where xxxxxxxxxxxx).
I can't use a function as i store data in a temporary table in the store procedure.
--Get Generated Times INSERT INTO @GeneratedTimes( ResourceId , DateFrom , DateTo ) EXEC dbo.P_GenerateTimes @ApplicationId , @EventId , @FromDate , @ToDate , @WeekScheduleId , @FromTimeToBook , @ToTimeToBook WHERE xxxxxxxxxxxxxxxxxx
now i want to learn how to make a stored procedure to insert a record to `purchase` table, and many records to `purchase_detail` table with transaction where the some value are passed from vb6 through the parameters. i've made a SP to insert 1 record to `purchase` table n 1 record to `purchase_detail` just for testing, so i set the disc value to 10. it works fine... --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10), xitem_id VARCHAR(10), xqty TINYINT(3), xprice DOUBLE(15,2)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, 10); COMMIT; END --------------------------------------------------------------------------------- what i need is something like that but i only pass 3 variables (purch_id, purch_date, and supp_id) to SP, and then the SP will insert 1 record of purchase to `purchase` table, and add the purchase items to `purchase_detail` automatically from `purch_temp` table, and use the disc rate based on `supplier_id` and `item_id` from supplier_disc table, which will be looked something like this: --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); /*start looping here get the disc rate for each items where supp_id = xsupplier_id and item_id = the item_id from purch_temp table, and save it in a local variable (let's say local_disc) INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, local_disc); */ COMMIT; END --------------------------------------------------------------------------------- can anyone help me please? thank you in advance...
I've a requirement to store the output of the stored procedure into temp. tables/ table varibles. I've 4 select statements as my output of the stored procedure. How do I store the results of all the 4 select stmnts into 4 different temp tables.
Simplified SP is as...
Create procedure usp_test as begin
select c1,c2 from table1 select c3,4 from table2 select c9,c8 from table3 select c5,c7 from Table4 end
I'm expecting something like this...
declare @table1 table (c1, c2) insert into @table1 Exec <Sp_Name>
select * from @table1
I know the above stmnt works, if my SP has only 1 select stmnt as output. Please help me to acheive this for multiple select statements.
Hy , How is the stored procedure, to copy from a table to others tables? I have a stored procedure which is doing that, but for 1 registration : " insert into....". So i want for each registration in the source table to execute this procedure and put the data in my format table.
Hi All,I am looking for a store procedure or any alternate method which save my html file(s) text (with or without tags) in my table column automatically when I upload my html file to my file system (local hard drive).any help will be appreciate.Thanks in advance.
I wanted to know if it is possible to run a stored proc (report) when a new record is inserted / updated into a certain table. Also if it could do so for records with a column that meet a certain criteria ie. sell description like '%test%'
Can u please tell me how to copy data from table A(database A) to table B(databaseB) which table A contain 10 fields but table B consist of 11 fields. I have to insert current date and time into another field in Table B (which has extra field compare to tableA) automatically every hour or so. Please help. Thanx
Hi guys I have a stored procedure that a make crosstab table , In this table the main column is "job titles" these jobs must be ordered in certain way , for example "1st managers then engineers … workers … " so In the table that job titles are defined there is also a column named "Ranking" so the" job titles" could be sorted appropriately by ranking order . The problem is I cannot have the "Ranking" column with my crosstab table so I need to load it in a view or something like that. Any Idea?
