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 With INTO

Author  Topic 

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-19 : 09:04:35
I am trying to do a delete query that will delete a record from a table and then insert it into another table. I did a copy of the first table to make the second table where I want to copy the records. So the tables should be exactly the same - except I took of the auto-increment on the 2nd table.
This is what I have so far.
DELETE FROM porders	
OUTPUT Deleted.PONum, Deleted.Date, Deleted.DateRequired, Deleted.ShipTo, Deleted.ShipAddress, Deleted.ShipCity, Deleted.ShipState, Deleted.ShipZip, Deleted.ShipPhone,
Deleted.Tax, Deleted.Freight, Deleted.PrintStatus, Deleted.Comments, Deleted."Description", Deleted.POTotal, Deleted.VendorID, Deleted.OriginatorID, Deleted.StatusID,
Deleted.ShippedID, Deleted.RefPONum

INTO pordershistory

WHERE PONum = 12304;

But I get an error Column name or number of supplied values does not match table definition. I don't understand how the table definitions do not match because I copied the one to make the other. Do you have to do all fields when you do an INTO because I didn't do the MPONum column because it was a computed. Not sure if that is what the problem might be?
Thanks,
Stacy

Porders Table Structure
COLUMN_NAME TYPE_NAME LENGTH
PONum int identity 4
VendorID int 4
OriginatorID int 4
StatusID int 4
ShippedID int 4
Date date 20
MPONum varchar 11
DateRequired date 20
RefPONum real 4
ShipTo varchar 30
ShipAddress varchar 25
ShipCity varchar 15
ShipPhone varchar 12
ShipState varchar 2
ShipZip varchar 10
Tax decimal 20
Freight decimal 20
PrintStatus varchar 3
POTotal money 21
Comments varchar 250
Description varchar 250
Modified timestamp 8

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 09:15:01
you've to all the fields if you're simply specifying table names. otherwise use column list

DELETE FROM porders
OUTPUT Deleted.PONum, Deleted.Date, Deleted.DateRequired, Deleted.ShipTo, Deleted.ShipAddress, Deleted.ShipCity, Deleted.ShipState, Deleted.ShipZip, Deleted.ShipPhone,
Deleted.Tax, Deleted.Freight, Deleted.PrintStatus, Deleted.Comments, Deleted."Description", Deleted.POTotal, Deleted.VendorID, Deleted.OriginatorID, Deleted.StatusID,
Deleted.ShippedID, Deleted.RefPONum

INTO pordershistory (Col1,Col2,...)

WHERE PONum = 12304;


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

Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-19 : 09:38:47
quote:
Originally posted by visakh16

you've to all the fields if you're simply specifying table names. otherwise use column list

DELETE FROM porders
OUTPUT Deleted.PONum, Deleted.Date, Deleted.DateRequired, Deleted.ShipTo, Deleted.ShipAddress, Deleted.ShipCity, Deleted.ShipState, Deleted.ShipZip, Deleted.ShipPhone,
Deleted.Tax, Deleted.Freight, Deleted.PrintStatus, Deleted.Comments, Deleted."Description", Deleted.POTotal, Deleted.VendorID, Deleted.OriginatorID, Deleted.StatusID,
Deleted.ShippedID, Deleted.RefPONum

INTO pordershistory (Col1,Col2,...)

WHERE PONum = 12304;


Thanks visakh16 - It's works now!


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



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 09:43:35
welcome

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

Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-19 : 10:35:53
quote:
Originally posted by visakh16

welcome

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




One more question I should be able to make that into a stored procedure to use in my VS2010 tableadapter Delete, right? Here is the stored procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[PordersDelete]
(
@Original_PONum int,
@Original_Modified Timestamp

)
AS
BEGIN
SET NOCOUNT ON

DELETE FROM porders
OUTPUT Deleted.PONum, Deleted.Date, Deleted.DateRequired, Deleted.ShipTo, Deleted.ShipAddress, Deleted.ShipCity, Deleted.ShipState, Deleted.ShipZip,
Deleted.ShipPhone, Deleted.Tax, Deleted.Freight, Deleted.PrintStatus, Deleted.Comments, Deleted.[Description], Deleted.POTotal, Deleted.VendorID, Deleted.OriginatorID,
Deleted.StatusID, Deleted.ShippedID, Deleted.RefPONum
INTO pordershistory (PONum, Date, DateRequired, ShipTo, ShipAddress, ShipCity, ShipState, ShipZip, ShipPhone, Tax,
Freight, PrintStatus, Comments, Description, POTotal, VendorID, OriginatorID, StatusID, ShippedID, RefPONum)
WHERE (PONum = @Original_PONum) AND (Modified = @Original_Modified)


I can't create the procedure it gives the error Msg 102, Level 15, State 1, Procedure PordersDelete, Line 18
Incorrect syntax near ')'. But I can't find anything wrong with it, can you?
Thanks,
Stacy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 10:39:25
try

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[PordersDelete]
@Original_PONum int,
@Original_Modified Timestamp
AS
BEGIN
SET NOCOUNT ON

DELETE FROM porders
OUTPUT Deleted.PONum, Deleted.Date, Deleted.DateRequired, Deleted.ShipTo, Deleted.ShipAddress, Deleted.ShipCity, Deleted.ShipState, Deleted.ShipZip,
Deleted.ShipPhone, Deleted.Tax, Deleted.Freight, Deleted.PrintStatus, Deleted.Comments, Deleted.[Description], Deleted.POTotal, Deleted.VendorID, Deleted.OriginatorID,
Deleted.StatusID, Deleted.ShippedID, Deleted.RefPONum
INTO pordershistory (PONum, Date, DateRequired, ShipTo, ShipAddress, ShipCity, ShipState, ShipZip, ShipPhone, Tax,
Freight, PrintStatus, Comments, Description, POTotal, VendorID, OriginatorID, StatusID, ShippedID, RefPONum)
WHERE PONum = @Original_PONum
AND Modified = @Original_Modified
GO


also why are you using timestamp parameter for Modified field. Whats the datatype of table field?

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

Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-19 : 10:51:28
quote:
Originally posted by visakh16

try

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[PordersDelete]
@Original_PONum int,
@Original_Modified Timestamp
AS
BEGIN
SET NOCOUNT ON

DELETE FROM porders
OUTPUT Deleted.PONum, Deleted.Date, Deleted.DateRequired, Deleted.ShipTo, Deleted.ShipAddress, Deleted.ShipCity, Deleted.ShipState, Deleted.ShipZip,
Deleted.ShipPhone, Deleted.Tax, Deleted.Freight, Deleted.PrintStatus, Deleted.Comments, Deleted.[Description], Deleted.POTotal, Deleted.VendorID, Deleted.OriginatorID,
Deleted.StatusID, Deleted.ShippedID, Deleted.RefPONum
INTO pordershistory (PONum, Date, DateRequired, ShipTo, ShipAddress, ShipCity, ShipState, ShipZip, ShipPhone, Tax,
Freight, PrintStatus, Comments, Description, POTotal, VendorID, OriginatorID, StatusID, ShippedID, RefPONum)
WHERE PONum = @Original_PONum
AND Modified = @Original_Modified
GO


also why are you using timestamp parameter for Modified field. Whats the datatype of table field?

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




Nope, still doesn't like it. Basically I wanted to edit my visual studio tableadapter deletecommand to add the OUPUT and INTO but VS didn't like that so I thought I would make a stored procedure and use that for the tableadapter deletecommand, but now I can't get sql to make the stored procedure. The Modified was in the VS deletecommand so I just kept it. There is a Modified field in my table and it is a timestamp datatype.
Thanks,
Stacy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 11:03:30
use the code in ssms query window and call it from visual studio. dont try to edit procedure from visual studio.

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

Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-19 : 11:09:23
quote:
Originally posted by visakh16

use the code in ssms query window and call it from visual studio. dont try to edit procedure from visual studio.

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




I am trying to make the stored procedure in SSMS but that is where I am getting that error.
Thanks,
Stacy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 11:10:58
you had an unwanted BEGIN


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[PordersDelete]
@Original_PONum int,
@Original_Modified Timestamp
AS
BEGIN
SET NOCOUNT ON

DELETE FROM porders
OUTPUT Deleted.PONum, Deleted.Date, Deleted.DateRequired, Deleted.ShipTo, Deleted.ShipAddress, Deleted.ShipCity, Deleted.ShipState, Deleted.ShipZip,
Deleted.ShipPhone, Deleted.Tax, Deleted.Freight, Deleted.PrintStatus, Deleted.Comments, Deleted.[Description], Deleted.POTotal, Deleted.VendorID, Deleted.OriginatorID,
Deleted.StatusID, Deleted.ShippedID, Deleted.RefPONum
INTO pordershistory (PONum, Date, DateRequired, ShipTo, ShipAddress, ShipCity, ShipState, ShipZip, ShipPhone, Tax,
Freight, PrintStatus, Comments, Description, POTotal, VendorID, OriginatorID, StatusID, ShippedID, RefPONum)
WHERE PONum = @Original_PONum
AND Modified = @Original_Modified
GO


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

Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-12-19 : 11:18:09
Yep that was the problem! Got it to work now. Why is it I don't need the BEGIN in this stored procedure? All of my other stored procedures have it.

Stacy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 21:56:49
if you have a BEGIN then you should have a corresponding END. If using,use both else dont use either

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

Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2013-01-03 : 14:10:04
quote:
Originally posted by visakh16

if you have a BEGIN then you should have a corresponding END. If using,use both else dont use either

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




Thanks got it!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 02:33:48
welcome

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

Go to Top of Page
   

- Advertisement -