INSERT USING A SELECT STATEMENT

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

INSERT USING A SELECT STATEMENT

Author

Topic

Amanda_Nicholls
Starting Member

4 Posts

Posted - 2012-04-06 : 10:16:43

insert into tbl_orders (CustomerID, Quantity, ProductID, OrderDate, OrderStatusID)
select customerID, Quantity, ProductID, OrderDate,OrderStatusID from tbl_Orders where OrderStatusID = 8

UPDATE tbl_Orders
SET OrderDate = '2012.06.04', OrderStatusID = 2
FROM tbl_Orders
WHERE OrderStatusID = 8

The order status 8 means recalled and I want to duplicate the orders with a new order status of 2 which means picking from warehouse.

I have no problem duplicating the orders and giving them a new order date of today but I don't want the old orders to change to order status 2 or both the old and new order will show as picking - I only want the duplicate to show as picking and the old one to stay as recalled?

some help would be very much appreciated.

Many thanks
amanda

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 10:20:34

why do you need separate update?

wont this suffice?


insert into tbl_orders (CustomerID, Quantity, ProductID, OrderDate, OrderStatusID)
select customerID, Quantity, ProductID, '20120604',2
from tbl_Orders where OrderStatusID = 8

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-06 : 10:22:34

May be you can change your insert statement to assign orderStatusId = 2, like this?

insert into tbl_orders (CustomerID, Quantity, ProductID, OrderDate, OrderStatusID)
select customerID, Quantity, ProductID, OrderDate, 2 AS OrderStatusID from tbl_Orders where OrderStatusID = 8

You may also want to add additional conditions in the where clause, for example, if you wanted to replicate only orders with a specific order date, as in:

insert into tbl_orders (CustomerID, Quantity, ProductID, OrderDate, OrderStatusID)
select customerID, Quantity, ProductID, OrderDate, 2 AS OrderStatusID from tbl_Orders where OrderStatusID = 8
AND OrderDate = '20120604'

As an aside, when you specify dates, try to use the YYYYMMDD format. SQL Server is able to interpret that format unambiguously.

Amanda_Nicholls
Starting Member

4 Posts

Posted - 2012-04-06 : 10:32:43

Yes I could do that I have changed it now.

The previous exercise I had been doing encouraged me to do the update statement also.

That has helped alot thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 10:39:16

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources