delete records from data base - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

delete records from data base

Author

Topic

zeljo_bl
Starting Member

5 Posts

Posted - 2009-11-11 : 15:37:35

...I need to erase records from data base

first filter is all records newer than specific date

second filter is all entries from specific user entered at specific time need to be erased...from whole database

ZR

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-11 : 15:51:30

Could you at least show us what you have tried?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

zeljo_bl
Starting Member

5 Posts

Posted - 2009-11-11 : 16:09:46

I didn't tried anything...I'm new to these things and have no idea how to do it...

ZR

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-11 : 16:56:17

we'd need to see the table definitions to help here.

one piece of advice can give right now though: make certain you have a good backup before you start deleting

zeljo_bl
Starting Member

5 Posts

Posted - 2009-11-11 : 17:41:16

backup is there...allready loaded it 16 times :)....

I need to start system all over again but after removig all data(TURNCATE TABLE?) program which using same data base giving me error...it askig for some data which initialy comes with database so all data which was there before first user entry need to remain in database...

there is around 100 tables and structure inside each is something like

FIELD1 FIELD2 FIELD3 FIELD4 FIELD5 FIELD6 FIELD7 CREATED MODIFIED FIELD8....

each table have different nuber of fields(colomns) but all of them contains CREATED...

ZR

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-11 : 22:20:23

if there is a field called creaqted in every table, in your query editor, set the output to text (not grid) and execute this:


select	'delete from [' + name + '] where created > ''20091101'';
GO'
FROM	sys.tables

Then copy/paste the results back in and execute that

Change the date to the date you want. I used Nov 1, 2009 for this sample

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-12 : 01:57:24

quote:
Originally posted by russell

if there is a field called creaqted in every table, in your query editor, set the output to text (not grid) and execute this:

select 'delete from [' + name + '] where created > ''20091101'';
GO'
FROM sys.tables

Then copy/paste the results back in and execute that

Change the date to the date you want. I used Nov 1, 2009 for this sample

You need to include the second filter too


select	'delete from [' + name + '] where created > ''20091101'' and user_col=''some user'';
GO'
FROM	sys.tables

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-11-12 : 12:23:01

And if there are Foreign keys or other constraints?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 12:34:05

DROP DATABASE <dbname>

Kidding! I was kidding.

so you have n number of tables. And in each table you have a column of (add?) dates?

Is the column name the same in each table?

Can we focus on 1 table to start?

Post the DDL of that table and what you want deleted.

Then we will go from there

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-12 : 13:19:57

quote:
Originally posted by DonAtWork

And if there are Foreign keys or other constraints?

Then you can disable all constraints like this:


--Disable all Constraints 
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

-- execute deletes here... 
-- delete from....

-- Enable all Constraints 
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 13:49:47

Doesn't seem to work too well



CREATE TABLE myTable99a (Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 char(1))
CREATE TABLE myTable99b (Cola int IDENTITY(1,1) PRIMARY KEY
							, Col1 int, Col2 char(1)
							, FOREIGN KEY (Col1) 
								REFERENCES myTable99a(Col1))
GO


INSERT INTO myTable99a(Col2) SELECT 'x'
GO

SELECT * FROM myTable99a
GO

INSERT INTO myTable99b(Col1, Col2) SELECT 1, 'x'
GO

SELECT * FROM myTable99b
GO

TRUNCATE TABLE myTable99a
GO

ALTER TABLE myTable99a NOCHECK CONSTRAINT ALL 
ALTER TABLE myTable99b NOCHECK CONSTRAINT ALL 
GO


TRUNCATE TABLE myTable99a
GO

DROP TABLE myTable99b, myTable99a
GO

Would've been sweet though

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-12 : 15:26:53

Can't truncate...constraint is still respected when you try to truncate. Try it with DELETE. that works

By the way, Paul Randal told me in Orlando last year that it sounds like a bug when I mentioned to him that truncate doesn't work in that script.

Also, obviously...the usual disclaimer about undocumented SPs

cheers

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 15:30:57

Name dropper

Where is Paul these days anyway

Haven't seen his post in a while

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-12 : 15:34:42

lol. didn't mean to...just that i thought it was strange too, so i asked.

anyway, for the sake of completeness:


create table t1 (a int primary key not null, b int);

create table t2 (a int, b int foreign key references t1(a));

insert t1 values (1, 1);
insert t1 values (2, 2);
insert t2 values (100, 1);
GO

--Disable all Constraints 
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

-- execute deletes here... 
delete t1;

-- Enable all Constraints 
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 

select * from t1;
select * from t2;
GO

drop table t2;
go
drop table t1;
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-12 : 19:21:44

Paul's very active in the SQL Server community still, just maybe not so much on SQLTeam since he no longer works for Microsoft. He's very active on Facebook and Twitter for example, SQL Server specifically.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-13 : 01:30:44

quote:
Originally posted by DonAtWork

And if there are Foreign keys or other constraints?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Madhivanan

Failing to plan is Planning to fail

JhonABR
Starting Member

1 Post

Posted - 2009-11-14 : 15:50:18

sp_msforeachtable 'delete from ?'

www.dealpocket.com/

zeljo_bl
Starting Member

5 Posts

Posted - 2009-11-14 : 17:37:53

thanks for help

one more thing

how to erase all records from table except last 10 rows?

ZR

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-14 : 18:01:01

You can use DELETE TOP.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-14 : 18:01:22

The question is: How to decide which records are the last 10?

No, you're never too old to Yak'n'Roll if you're too young to die.

zeljo_bl
Starting Member

5 Posts

Posted - 2009-11-14 : 20:03:35

I have 260563 entries in one table...table contains 23 colomns...1t colomn is unique... at the moment first row starts with 1600 and last is 551672(between these is lots of gaps in numbers because some entries are erased bu external program which using database)...so i want to keep numbers from 551662 till 551672...

hope explanation is good enough

ZR

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources