SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Delete With INTO
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 12/19/2012 :  09:04:35  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/19/2012 :  09:15:01  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 12/19/2012 :  09:38:47  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/19/2012 :  09:43:35  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 12/19/2012 :  10:35:53  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/19/2012 :  10:39:25  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 12/19/2012 :  10:51:28  Show Profile  Reply with Quote
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

Edited by - StacyOW on 12/19/2012 10:55:00
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/19/2012 :  11:03:30  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 12/19/2012 :  11:09:23  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/19/2012 :  11:10:58  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 12/19/2012 :  11:18:09  Show Profile  Reply with Quote
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

Edited by - StacyOW on 12/19/2012 11:32:14
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/19/2012 :  21:56:49  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 01/03/2013 :  14:10:04  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/04/2013 :  02:33:48  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000