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 pordershistoryWHERE 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,StacyPorders Table StructureCOLUMN_NAME TYPE_NAME LENGTHPONum int identity 4VendorID int 4OriginatorID int 4StatusID int 4ShippedID int 4Date date 20MPONum varchar 11DateRequired date 20RefPONum real 4ShipTo varchar 30ShipAddress varchar 25ShipCity varchar 15ShipPhone varchar 12ShipState varchar 2ShipZip varchar 10Tax decimal 20Freight decimal 20PrintStatus varchar 3POTotal money 21Comments varchar 250Description varchar 250Modified 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 listDELETE 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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 listDELETE 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 MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-19 : 09:43:35
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-12-19 : 10:35:53
|
quote: Originally posted by visakh16 welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 ONGOSET QUOTED_IDENTIFIER OFFGOCREATE PROC [dbo].[PordersDelete](@Original_PONum int,@Original_Modified Timestamp)ASBEGINSET NOCOUNT ONDELETE FROM pordersOUTPUT 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.RefPONumINTO 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 18Incorrect syntax near ')'. But I can't find anything wrong with it, can you?Thanks,Stacy |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-19 : 10:39:25
|
trySET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOCREATE PROC [dbo].[PordersDelete]@Original_PONum int,@Original_Modified TimestampASBEGINSET NOCOUNT ONDELETE FROM pordersOUTPUT 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.RefPONumINTO 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_ModifiedGO also why are you using timestamp parameter for Modified field. Whats the datatype of table field?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
StacyOW
Yak Posting Veteran
93 Posts |
Posted - 2012-12-19 : 10:51:28
|
quote: Originally posted by visakh16 trySET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOCREATE PROC [dbo].[PordersDelete]@Original_PONum int,@Original_Modified TimestampASBEGINSET NOCOUNT ONDELETE FROM pordersOUTPUT 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.RefPONumINTO 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_ModifiedGO also why are you using timestamp parameter for Modified field. Whats the datatype of table field?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 MVPhttp://visakhm.blogspot.com/
I am trying to make the stored procedure in SSMS but that is where I am getting that error.Thanks,Stacy |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-19 : 11:10:58
|
you had an unwanted BEGINSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOCREATE PROC [dbo].[PordersDelete]@Original_PONum int,@Original_Modified TimestampASBEGINSET NOCOUNT ONDELETE FROM pordersOUTPUT 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.RefPONumINTO 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_ModifiedGO ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 MVPhttp://visakhm.blogspot.com/
Thanks got it! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-04 : 02:33:48
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|