|
|
Respected,
How can we truncate the whole tables value from my DataBase by single query.
Thanks
|
|
|
|
|
truncate table tblName
Niladri Biswas
|
|
|
|
|
Drop database: The DROP DATABASE statement is used to delete a database. Everything is deleted ... tables, views, indexes, stored procedures, etc
Drop Table: The DROP TABLE statement is used to delete a table.
Truncate Table: Delete the data inside the table, and not the table itself
Use this as reference:
http://www.w3schools.com/SQl/sql_drop.asp[^]
|
|
|
|
|
Respected,
But I have given the Relations to Tables.
Thanks.
|
|
|
|
|
Then do it one table at at time, starting from the leaf tables...
eg:
Persons have one or more Addresses. Delete Addresses first, then Persons.
|
|
|
|
|
You could have specified that in the post.. How can one come to know that you want to perform a CASCADE DELETION ?
e.g.
CREATE TABLE tblPK
( id int primary key )
CREATE TABLE tblFK
(id int,
name varchar(50),
age int
FOREIGN KEY (id) REFERENCES tblPK ON DELETE CASCADE)
Niladri Biswas
modified on Wednesday, November 4, 2009 12:51 AM
|
|
|
|
|
In 20+ years of database design and development I've never actually used cascade delete, I wonder why!
|
|
|
|
|
Mycroft Holmes wrote: I wonder why!
Because you are scared of it? 
|
|
|
|
|
I always envisage someone going to the settings table and using cascade delete on the Standard Customer record or something. Scared does not even cover it, terrified is closer.
|
|
|
|
|
Meh... don't let users have access to the tables... have a good backup strategy!! 
|
|
|
|
|
Thats even the worst side effect.
Imagine a header table with, lets say, 1000 records in it. Each header has 100,000 (badly indexed) child records. Cascade delete is turned on.
Someone comes along and says "Ah header table only has 1000 rows, it will be easy to delete a record from there".
Database reduced to treacle.
|
|
|
|
|
Probably for the same reason as I haven't - we are control freaks who like to explicitly delete things when we want to, not sudenly finding it happening 'on its own' 
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
hello
SSRS - web service interface generated pdf's with slightly different font/color compared to those pdf when access via "Direct URL access" (point browser to http://localhost/reports/SomeReport/SomeReport - then export by pdf).
Any idea why? Perhaps it has to do with DeviceInfo in Render method? What do I need to do so two are same?
Thanks
dev
|
|
|
|
|
i have a crystal report which is bind with oledb database. tommorrow its working but now it show error that "Table could not found". i googled my problem and can't find any satisfactory solution if anyone knows it solution plz share with me.
advance thnx 4 help
|
|
|
|
|
|
Hi i have 2 tables..,
one is master another one is child...,
ex:
Master=>Pid is primary key
===========
Pid Name
1 m1
2 m2
3 m3
4 m4
5 m5
6 m6
Child=>Cid is primary key
==========
Cid Pid Name
1 2 nm1
2 2 nm2
3 1 nm3
4 5 nm4
5 6 nm5
Now i need the lastest records...,
O/p:
======
Pid Cid Name
6 5 nm5
5 4 nm4
4 null m4
3 null m3
2 2 nm2 //in child table have 2 records for pid...,in tht latest is Cid=2
1 3 nm3
Thanks & Regards,
Member 3879881,
please don't forget to vote on the post
|
|
|
|
|
Try this
declare @tblMaster table(pid int primary key,name varchar(20))
insert into @tblMaster
select 1,'m1' union all select 2,'m2' union all
select 3,'m3' union all select 4,'m4' union all
select 5,'m5' union all select 6,'m6'
declare @tblChild table(cid int primary key,pid int,name varchar(20))
insert into @tblChild
select 1,2,'nm1' union all select 2,2,'nm2' union all
select 3,1,'nm3' union all select 4,5,'nm4' union all
select 5,6,'nm5'
select pid,cid,name from
(
select
m.pid
,c.cid
,case when c.name IS null then m.name
else c.name
end name
,row_number() over(partition by m.pid order by c.name desc) as rn
from @tblChild c
full join @tblMaster m
on c.pid = m.pid
)x(pid,cid,name,rn)
where rn = 1
order by pid desc
Niladri Biswas
|
|
|
|
|
whts tht row_number function?
Thanks & Regards,
Member 3879881,
please don't forget to vote on the post
|
|
|
|
|
|
This should do it
select m.pid,c.cid,c.name
from Master m
join Child c on c.pid = m.pid
and c.cid = (select max(cid) from Child c1 where c1.pid = m.pid)
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Okay, someone straighten me out. I have two tables: [Budget] is the monthly budget for a bunch of accounts and [Account List] is (duh) the master list of accounts. I need to find the account names, [Twin Pack], that do not have any budget, ie no record exists, for a specified date, [Date].
Here's what I wrote:
SELECT [Account List].[Twin Pack]
FROM [Account List]
WHERE NOT EXISTS (SELECT Budget.[Twin Pack]
FROM Budget
WHERE (((Budget.Month)=DateValue([Date]))));
When I run it as is, it returns nothing. To troubleshoot, I put the subquery in a query by itself and got the desired results: the list of accounts that DO have budget in the specified month. Also, when I reversed the EXISTS statement by taking out the "Not", I also got the list of accounts that DO have budget in the specified month.
What am I missing?
|
|
|
|
|
You seem to have 2 disassociated queries here, one on
Account List and another on Budget with no logic to join them.
[rant] Who in his right mind allows spaces in table/field names and what idiot allows reserved words to be used as field names (Date) [/rant]
SELECT [Account List].[Twin Pack]
FROM [Account List]
WHERE something NOT EXISTS (SELECT Budget.[Twin Pack]
FROM Budget
WHERE (((Budget.Month)=DateValue([Date]))));
also make sure your internal select returns the expected records (what is DateValue)
|
|
|
|
|
Mycroft Holmes wrote: Who in his right mind allows spaces in table/field names and what idiot allows reserved words to be used as field names (Date)
Yeah, I'm right up there on that soap box with ya. It's a linked table that someone else created. I could write some code to go change the field names, but this was supposed to be a quick and easy DB.
I found out what I was missing. I scrapped the first approach and did a Left Join on the two tables.
|
|
|
|
|
Try this
declare @tblAccountList table(TwinPack int)
insert into @tblAccountList
select 111 union all select 222 union all
select 333 union all select 444 union all
select 555 union all select 666 union all
select 777 union all select 888 union all select 1100
declare @tblBudget table(TwinPack int,Mnth varchar(20))
insert into @tblBudget
select 111,'Jan' union all select 222,'Feb' union all
select 333,'Mar' union all select 444,'Apr' union all
select 555,'May' union all select 666, 'Jun' union all
select 777,'Jul' union all select 888 ,'Aug' union all
select 999,'Sep' union all select 1000 ,'Oct' union all
select 1100,'Nov' union all select 1200 ,'Dec'
SELECT a.TwinPack
FROM @tblAccountList a
except
SELECT b.TwinPack
FROM @tblBudget b
WHERE b.Mnth = CONVERT(varchar(3),DATENAME(mm,GETDATE()))
Basically I am finding out the TwinPack from @tblAccountList where the budget is not in November. I am using Except here
Note- I have done the schema by myself which I hope is as per ur requirement.Else you please correct and I will again give a shot.
Hope this helps
Niladri Biswas
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
