Delete & Output Clause - 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 & Output Clause

Author

Topic

SQLFOX
Starting Member

45 Posts

Posted - 2010-08-09 : 18:17:20

Hi,

I am trying to put together a syntax that will Delete all rows from the Customer Table where the Firstname = John. I would like to keep a record of the deleted rows and have them automatically inserted into a new table called Deleted Data. I'm told this can be done using the Delete and Output Clause but I don't know what the syntax is or how is works exactly. Any advice would be really appreciated.

Thanks

Laith Scofield
Starting Member

10 Posts

Posted - 2010-08-09 : 18:44:38

I don't think you can do that...
but you can insert into the table "Deleted data" the records that first name='Jhon'
and then deleted the records with first name='jhon'...

"It's fine to celebrate success but it is more important to heed the lessons of failure."
Bill Gates

SQLFOX
Starting Member

45 Posts

Posted - 2010-08-09 : 19:40:11

Thanks for your reply.

I found some information on the following link - http://msdn.microsoft.com/en-us/library/ms177564.aspx
From looking at the information I'm hoping that I can have the deleted records automatically populate to the Deleted Data table.

I have included a different example of what I am trying to do below, whereby I create an ArchiveEmployees table, delete all records from Employees table with firstname = 'Paul' and then Output into the ArchiveEmployees table(I think this may be a 2008/2005 feature in SQL Server???). It's not working for me but I'll keep trying to find a solution.

Thanks

Create Table ArchiveEmployees
(Firstname nchar(10) Null,
Lastname nchar(10) Null,
Department nchar(10) Null,
Country nchar(10) Null);
Delete dbo.Employees
Output Deleted.Firstname,
Deleted.Lastname,
Deleted.Department,
deleted.Country
Into ArchiveEmployees
Where Firstname='Paul'

Kristen
Test

22859 Posts

Posted - 2010-08-10 : 02:36:27

Your syntax looks OK. What error are you getting?

"I think this may be a 2008/2005 feature in SQL Server???"

Introduced in SQL 2005

SQLFOX
Starting Member

45 Posts

Posted - 2010-08-10 : 13:25:59

This is the error message that I received:

'Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition'.

Just to note the Employees table has the following columns and I have not included the EmployeeID in my Delete and Output statement so I'm wondering if this is the reason for the error message. However, if I were to include the EmployeeID in the Delete and Output Clause then I get a different error message and I'm wondering if it is because the EmployeeID foreign key in the Orders table has records so it won't let me archive the Employee if there are Orders connected to them???:

--Employee Table--
EmployeeID (PK, int, not null)
Firstname (nchar(10), null)
Lastname (nchar(10), null)
Department (nchar(10), null)
Country (nchar(10), null)

However, when I include EmployeeID in the Delete and Output Clause I get the error message below:

Create Table ArchiveEmployees
(EmployeeID Primary Key Int Not Null,
Firstname nchar(10) Null,
Lastname nchar(10) Null,
Department nchar(10) Null,
Country nchar(10) Null);
Delete dbo.Employees
Output
deleted.EmployeeID,
Deleted.Firstname,
Deleted.Lastname,
Deleted.Department,
deleted.Country
Into ArchiveEmployees
Where Firstname='Paul'

'Msg 173, Level 15, State 1, Line 2
The definition for column 'EmployeeID' must include a data type'.

SQLFOX
Starting Member

45 Posts

Posted - 2010-08-10 : 13:30:35

If I leave out the EmployeeID from the Delete and Output Clause I also get the following message - I assume it's because there are related records in the Orders table where the EmployeeID foreign key is???:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_Employees". The conflict occurred in database "Products", table "dbo.Orders", column 'EmployeeID'.
The statement has been terminated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-10 : 13:36:06

thats because you've Employee which is to be deleted being refered by one or more Order records. You need to first remove the reference records before you remove it from employee table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-10 : 13:44:57

use a trigger


CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 varchar(20), Col3 datetime DEFAULT(GetDate()))
GO

CREATE TABLE myTable99_H(Col1 int, Col2 varchar(20), Col3 datetime)
GO


INSERT INTO myTable99(Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
GO

CREATE TRIGGER myTrigger99 ON myTable99
	AFTER DELETE
AS
	
		INSERT INTO myTable99_H(Col1, Col2, Col3)
		SELECT Col1, Col2, Col3 FROM deleted
GO

DELETE FROM myTable99 WHERE Col2 = 'B'

SELECT * FROM myTable99
GO

SELECT * FROM myTable99_H
GO

DROP TRIGGER myTrigger99
DROP TABLE myTable99, myTable99_H
GO

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

SQLFOX
Starting Member

45 Posts

Posted - 2010-08-11 : 15:51:21

Hi Brett,

Thanks for your reply. I'm fairly new to SQL and am currently trying to learn about Triggers and using them with the Delete and Output Clause. Just want to ask you a why you have '1.1' beside the IDENTITY column - does this mean that the number in the identity column begins with 1 and increments by 1?? It may sound like a pathetic question but as I said I'm fairly new to SQL and am learning the basics!

Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-11 : 16:04:44

quote:
Originally posted by SQLFOX

Hi Brett,

Thanks for your reply. I'm fairly new to SQL and am currently trying to learn about Triggers and using them with the Delete and Output Clause. Just want to ask you a why you have '1.1' beside the IDENTITY column - does this mean that the number in the identity column begins with 1 and increments by 1?? It may sound like a pathetic question but as I said I'm fairly new to SQL and am learning the basics!

Thanks.

Yes. Thats right.
Its (1,1) btw.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources