February 10, 2011 at 6:24 am
I want to know that whether temporary tables and databases exist in sql server 2005 ?
actually , there is a big application which have tables with lacs of data rows in it.
so i need users to work on temporary tables instead of the original tables because of accuracy and integrity.
users should first insert their original data into the temporary tables (they would not know that they are inserting into the temporary tables instead of original tables) and then it would commit the data into the original table. this would be done through transactions.
so is there any inbuilt feature of temporary tables and databases or i would have to create it?
February 10, 2011 at 6:35 am
yes, you can create temporary tables in SQL...but there is a time and a place for it. temporary databases? for get it, you'l have way too many permissions problems to even bother trying. I think you need to get a better understanding on updates and deletes instead.
you would not want to copy lacs/millions of rows to a temporary table, just to protect the original data. you simply let them SELECT data, and only UPDATE what they changed(and are ALLOWED to change)
for example, an application will select some data....that means it requests a copy of the data so it can be presented...so the presentation layer(web page, vb.net, whatever) ALREADY has a copy of the data. does it make a difference if it is a copy of a copy of the data? No.
The programmer can decide whether the data gets updated/posted back to the server or not, based on whatever rules you impose...so the question for you really is what, EXACTLY, do you want to do?
Lowell
February 10, 2011 at 7:27 am
Dear Lowell Sir,
Actually, i have a .net application through which lacs of data would be inserted by the users.
but as usual, users keep on making mistakes. so i want that at the back end of the application,when the users enter the data, then the data is entered into a temporary table, and after the user presses the last save button, then the whole data is committed to the main original table.
also, the users delete the data by mistake, which would be very dangerous.
so i want that if a user deletes the data by mistake, then it is deleted from only the temporary table and not from the original table. that is why i need the users to alter, delete and insert in the temporary table, and when the final button is pressed then the data is committed to the original table.
February 10, 2011 at 7:34 am
SQLCHILD (2/10/2011)
Dear Lowell Sir,Actually, i have a .net application through which lacs of data would be inserted by the users.
but as usual, users keep on making mistakes. so i want that at the back end of the application,when the users enter the data, then the data is entered into a temporary table, and after the user presses the last save button, then the whole data is committed to the main original table.
also, the users delete the data by mistake, which would be very dangerous.
so i want that if a user deletes the data by mistake, then it is deleted from only the temporary table and not from the original table. that is why i need the users to alter, delete and insert in the temporary table, and when the final button is pressed then the data is committed to the original table.
there is nothing that you mentioned that needs a temporary table so far.....
It sounds like you have two issues...not enough validation in your .net application to filter/remove the end user mistakes...and posting those changes to the server too early in the work path.
I cannot help with the additional validation...that's business rules unique to your application..it's simply a If MyValidationRoutine() then msgbox(errors) in your app.
as for the second part, it sounds like you have most of everything in place: so you have a Datatable in .Net typed dataset that is loaded via MyAdapter.LoadDatatable. so the end user already has a "local" copy of the data.
you have "two" levels of save: one that posts to the server before it should...change that so it does not call UpdateDataTable. make sure the UpdateDataTable is called only at the "second" save you mentioned...when all the data is validated and the "final" button is clicked instead.
what value, at all, would there be to put bad data into a temporary table, and then post bad data to the permanent table? none that i can think of. your core issue is you KNOW end users are putting in bad data...concentrate coding efforts on preventing THAT; the temp table does not fix that issue...it just makes it more complex...shuffling the erroneous data to more than one spot.
as for deleting data...you can do the following: instead of REALLY deleting, you can add a column "isDeleted" to your data...then you can make the .net application select values WHERE isDeleted = 0 , and have the .net UPDATE that field to mark the row as deleted, instead of really deleting it...that makes it very easy to add an inteface to "undelete" records that should not be deleted/need to be recovered.
Lowell
February 10, 2011 at 7:40 am
thanks a lot sir. your reply helped me a lot.
but for the delete case, what would you refer?
i want that when the user deletes the data from the table, then it is deleted from the temporary table,and the users would work with the old data itself,
and after half an hour the application would ask that all the deleted data is now to be finally deleted or not? this time if the user presses yes then it would delete the data from the original tables.
February 10, 2011 at 7:42 am
while you were posting, i edited and added this:
as for deleting data...you can do the following: instead of REALLY deleting, you can add a column "isDeleted" to your data...then you can make the .net application select values WHERE isDeleted = 0 , and have the .net UPDATE that field to mark the row as isDeleted=1, instead of really deleting it...that makes it very easy to add an interface to "undelete" records that should not be deleted/need to be recovered.
Lowell
February 10, 2011 at 7:50 am
I CREATED A DATABASE--------
create database temporarytables
use temporarytables
--I CREATED TEMPORARY TABLE INSIDE A STORED PROCEDURE
create proc sp_testtemptable
as
begin
create table #temptable(tempno int)
end
THIS TIME I RUN THE SELECT * FROM #TEMPTABLE, OUTSIDE THE PROCEDURE, AND THE ERROR OCCURS THAT THE TABLE DOES NOT EXIST, which is fine as i read that temporary tables created in procedures are deleted when the procedure completes.
---------------------------------------------------------------------------------------------------
--NOW, I CREATED TEMPORARY TABLE1 INSIDE A STORED PROCEDURE & USED A SELECT QUERY ALSO
create proc sp_testtemptable1
as
begin
create table #temptable1(tempno1 int)
select * from #temptable1
end
WHEN I EXECUTE THE PROCEDURE, IT DISPLAYS THE TEMPTABLE1 TABLE.
BUT WHEN I RUN THE SELECT * FROM #TEMTABLE1, OUTSIDE THE PROCEDURE, THEN ALSO IT DISPLAYED THE TABLE. THE ERROR DID NOT OCCUR? MEANS THE TABLE WAS SUCCESSFULLY DISPLAYED BY THE SELECT QUERY. WHY SO?
ACCORDING TO THE CONCEPT THAT WHEN PROCEDURE COMPLETES, TEMPORARY TABLES ARE DESTROYED. SO THIS TIME ALSO ,THE TABLE SHOULD HAVE BEEN DESTROYED AND IT SHOULD SHOW ERROR INSTEAD OF DISPLAYING THE TABLE.
---------------------------------------------------------------------------------------------------
February 10, 2011 at 7:56 am
thanks a lot sir, this solution is very very intellectual . thanks a lot. your are awesome
February 10, 2011 at 8:07 am
Dear sir, then what are the main purposes for which temporary tables are used? Please tell, would be a great help. I want to know the practical and commercial uses of temporary tables
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply