| Author |
 Topic  |
|
|
StacyOW
Yak Posting Veteran
USA
74 Posts |
 Posted - 12/19/2012 : 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
India
47173 Posts |
Posted - 12/19/2012 : 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/
|
 |
|
|
StacyOW
Yak Posting Veteran
USA
74 Posts |
Posted - 12/19/2012 : 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/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 12/19/2012 : 09:43:35
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
StacyOW
Yak Posting Veteran
USA
74 Posts |
Posted - 12/19/2012 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 12/19/2012 : 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/
|
|
|
|
StacyOW
Yak Posting Veteran
USA
74 Posts |
Posted - 12/19/2012 : 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 |
Edited by - StacyOW on 12/19/2012 10:55:00 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 12/19/2012 : 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/
|
|
|
|
StacyOW
Yak Posting Veteran
USA
74 Posts |
Posted - 12/19/2012 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 12/19/2012 : 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/
|
|
|
|
StacyOW
Yak Posting Veteran
USA
74 Posts |
Posted - 12/19/2012 : 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 |
Edited by - StacyOW on 12/19/2012 11:32:14 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 12/19/2012 : 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/
|
|
|
|
StacyOW
Yak Posting Veteran
USA
74 Posts |
Posted - 01/03/2013 : 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!
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/04/2013 : 02:33:48
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Delete With INTO
