|
Hi,
-- DELETE, TRUNCATE and DROP Statements
DELETE
/*
The DELETE command is used to remove rows from a table.
A WHERE clause can be used to only remove some rows.
If no WHERE condition is specified, all rows will be removed.
After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
Note that this operation will cause all DELETE triggers on the table to fire.
*/
TRUNCATE
/*
TRUNCATE removes all rows from a table.
The operation cannot be rolled back and no triggers will be fired.
As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
*/
DROP
/*
The DROP command removes a table from the database.
All the tables' rows, indexes and privileges will also be removed.
No DML triggers will be fired. The operation cannot be rolled back.
*/
--Difference between TRUNCATE and DELETE commands
/*
1) TRUNCATE is a DDL command whereas DELETE is a DML command.
2) TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.
Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
3) You cann't rollback in TRUNCATE but in DELETE you can rollback.
TRUNCATE removes the record permanently.
4) In case of TRUNCATE ,Trigger doesn't get fired.
But in DML commands like DELETE .Trigger get fired.
5) You cann't use conditions(WHERE clause) in TRUNCATE.
But in DELETE you can write conditions using WHERE clause
*/
Regards,
GVPrabu
|
|
|
|
|
Hi Guys,
I want to know the table creation for the problem described below.
I have a 26 parameters for a customer feed back,
and i have a set of sites , some sites don't have some parameters in my 26 points, and some site want more then 26 points,
so i thought to create table like
id(int),parametename(Nvarchar(max)),site1(bit),site2(bit),site3(bit),site4(bit)
and i will allow in UI to check which point is applicable for which site,
this solution is for time being,
my concern is id the site name may add in future that time we have to change the parameter table and modify the insert and update methods as well as Store procedure,
How can avoid the re-coding by creating customized table.
Please suggest me the solution.
Thanks and regards
Vishwa
|
|
|
|
|
You should have a table that stores the list of sites identified by a SiteID column. And then you can store the values in your table with these columns
id(int),parametename(Nvarchar(max)),SiteID(int),value(bit)
Whenever there is a new site is to be added, just add it to the site table and insert a new row with that id in this table.
|
|
|
|
|
Table: site
- id
- Site_name
- Site_url
Table: site_parameter
- id
- Site_id (foreign key to above table)
- name
- value
For "26 parameters" you would have 26 rows in the site_parameter table.
|
|
|
|
|
Hi
I have an employee table with column gender F and M. Now i want to update the gender values of all the employee as F with M and M with F. But this should happen in a single query. How can i do this.
Thanks for your help.
Regards
Naina
Naina
|
|
|
|
|
You cannot do it in a single query, you need 3 updates, move F to X, move M to F, move X to M you can wrap the 3 queries in a transaction to insure integrity is maintained.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You cannot do it
You can actually do it. SQL is a set based language, changes made in a query are either fully committed or fully rolled back, there is no partial update. So, Eddy's solution will actually work.
|
|
|
|
|
Eddie got my 5, I didn't think of using case statement in the update clause.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
BEGIN TRANSACTION
CREATE TABLE Person
(
Name VARCHAR(MAX)
,Gender CHAR(1)
)
INSERT INTO Person VALUES
('Pete', 'M')
,('John', 'M')
,('Mary', 'F')
,('Dude', 'M')
,('Mary', 'F')
SELECT *
FROM Person
UPDATE Person
SET Gender = CASE Gender WHEN 'M' THEN 'F'
ELSE 'M'
END
SELECT *
FROM Person
ROLLBACK
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
CREATE TABLE EmpDtls(Code VARCHAR(10), Name VARCHAR(100), DeptCode TINYINT)
CREATE TABLE DeptDtls(DeptCode TINYINT, DeptName VARCHAR(50), EmpCount INT)
INSERT INTO EmpDtls(Code, Name, DeptCode)
VALUES('Emp1','Venkat', 1), ('Emp2','Prabu', 2), ('Emp3','Kumar', 1), ('Emp4','Karthick', 3),
('Emp5','Amith', 2),('Emp6','HariKrishna', 2)
INSERT INTO DeptDtls(DeptCode, DeptName)
VALUES(1, 'IT'),(2, 'Sales'), (3,'HR'), (4, 'Accounts')
SELECT Code, Name, DeptCode FROM EmpDtls
SELECT DeptCode, DeptName,EmpCount FROM DeptDtls
UPDATE D SET D.EmpCount= E.EmpCount
FROM DeptDtls D
INNER JOIN (SELECT DeptCode, COUNT(Code) 'EmpCount' FROM EmpDtls GROUP BY DeptCode) E ON E.DeptCode=D.DeptCode
SELECT DeptCode, DeptName,EmpCount FROM DeptDtls
IF OBJECT_ID('EmpDtls') IS NOT NULL DROP TABLE EmpDtls
IF OBJECT_ID('DeptDtls') IS NOT NULL DROP TABLE DeptDtls
|
|
|
|
|
Hi Naina,
Check the Script, U can use CASE Statement in UPDATE.
CREATE TABLE #EmpDtls(ID INT, EmpGender CHAR(1))
INSERT INTO #EmpDtls (ID, EmpGender) VALUES (1,'M'),(2,'F'),(3,'M')
SELECT ID, EmpGender FROM #EmpDtls
UPDATE #EmpDtls SET EmpGender = (CASE WHEN EmpGender='M' THEN 'F' WHEN EmpGender='F' THEN 'M' END)
SELECT ID, EmpGender FROM #EmpDtls
Regards,
GVPrabu
|
|
|
|
|
I am adding my code below
create procedure dist_profit_pairs1(IN user_id
varchar(20),IN plan varchar(20))
begin
DECLARE left_bvs,right_bvs Float DEFAULT 0;
SELECT sum(businessvalues)as left_bvs from bv_history where userid=user_id and type='Distribution Profit' and position='Left';
SELECT sum(businessvalues)as right_bvs from bv_history where userid=user_id and type='Distribution Profit' and position='Right';
IF left_bvs < right_bvs THEN
SELECT left_bvs;
ELSE
SELECT right_bvs;
|
|
|
|
|
Hi
I assume you want to print the variable value when executing in sqlserver studio management. If so, you can print the variable value by
print variablename
regards
Naina
Naina
|
|
|
|
|
The printed value has to be a varchar or char. So, please cast the value to a varchar before printing.
|
|
|
|
|
Can Visual Studio 2005 connect with an Access 2010 Database?
|
|
|
|
|
Yes
G to connectionstrings.com as a research beginning.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
We're currently implementing a warehouse management system with some customizations. One task is to plan the work in the warehouse by releasing waves of picking activities. The release is handled by a stored procedure. This stored procedure generates data on the amount of work that needs to be done, number of containers that are required, etc.. The planner needs to know this information in order to release the appropriate waves.
So, in order to capture the required data, we have another stored procedure which releases all waves that are not yet released and rolls back the transaction once it's done. This roll-back is a guarantee. The problem is that during this 'simulation' other queries are blocked, which creates a performance problem.
Is there a way to run the 'simulation' without blocking all other queries?
Thanks in advance!
Kind regards,
Bob Stanneveld
|
|
|
|
|
Sounds to me like you are doing something dramatically wrong, running a simulation on production data, therefore requiring the roll back, or building a business process on cancelling a transaction.
A more normal method of simulating would be to restore production to another instance, run the process to completion and then restore again to repeat.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Earlier this week I queried the possibility of Propagating a MySQL transaction with MSMQ on a concept level.
Thanks to Pete I crossed that bridge. But there is a stumbling block.
MySQL transactions themselves do not appear compatible with TransactionScope!
Short of migration to SQLServer what are my choices?
Ger
|
|
|
|
|
Good Day All
I imported data into a Ms Access 2013 from SQL and all the data is there. I created a Query and also created a report that will use the Query. So the Query returns all the data. There are around 590 000 records that needs to be displayed by the record and records that are returned by the Query. when i do a Print Preview it gives me
Page 32767 of 12224
So i am afraid to start printing because from this look it seems like it does not show all data, but my assumption is that Print Preview cant show all the data , even if i try to go to the last page, can anyone clear my assumption.
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
Vuyiswa Maseko wrote: There are around 590 000 records...So i am afraid to start printing
You should be.
Why exactly do you think that you should print something that is probably going to be about 6000 pages (at 100 records per page).
Who has told you that they are going to read this? Have you suggested to them what 6000 pages represents? Perhaps if you put a box (10 x 500 packages) on their desk and ask them what they planned to do with that then you might start a conversation that would lead to reports that are actually useful.
Other than that I certainly wouldn't attempt to print that at one go. If it messes up in the middle you have to start the entire job over. So select 400 pages at a time and print.
Might tell them you need your own printer too as it is going to tie it up for quite a while.
|
|
|
|
|
Good Day Jschell
Thank you for your reply. i will like to reserve my harsh comment for the other reply that i received after you , but i will politely reply to your question.
Who has told you that they are going to read this?
This is a book called the valuation roll , everyone comes and read this book because it tells a person how much they are going to pay in taxes ,for sure more than a million people are going to read the book.
Have you suggested to them what 6000 pages represents? Perhaps if you put a box (10 x 500 packages) on their desk and ask them what they planned to do with that then you might start a conversation that would lead to reports that are actually useful.
I have now decided to split it to 30000 to be on the safe side and created temp tables with the data sequential and its fine now thanks , what i learned is that Access Report will never output more than "32767", so basically in a Page you would have 10 records and if you take 590 000 and Divide by 10 it gives more 30000 Pages, so that is when i realized where and how to fix the problem.
Your reply was still going to point me to the direction of Splitting the data.
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
Vuyiswa Maseko wrote: for sure more than a million people are going to read the book.
Nope.
There is no way that you are going to get one million people to read 6000 pages (plural) much les 30,000.
What is going to happen instead is that they are going to search for what they already know they are looking for. And then read 1 or perhaps as many as 10 pages to find what they are looking for.
And that is where the solution lies. Provide a way for them to search for it.
Vuyiswa Maseko wrote: so that is when i realized where and how to fix the problem.
Presumably your design will have 30,000 static pages and then provide a way to figure out where something is in those pages and then go to the specific page.
|
|
|
|
|
I agree with jschell, you are asking access to do something inherently dumb, print 1000s of pages and you wonder why it is giving you wrong results!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Requirements in Software Development or in any industry might sometimes overcome the basic understanding of what makes sense to you.
Sometimes before reply you must try to understand what is the poster's point of view and after that you can reply or decide to be rude.
For your information, you can read my reply on jschell's reply.
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
