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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 copy table to table plus insert current date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Patyk
Starting Member

44 Posts

Posted - 03/07/2014 :  12:51:43  Show Profile  Reply with Quote
I need to create a store procedure to copy contents of one table to another. This will be done on the daily basis. I would need to take all data from one table where the warehouse is 01,02 or 03 and insert to the destination one. On the destination table I would have to insert a date field equal to the current date. How should I modify the procedure it achieve this.

Create proc CopyTableContents

AS
BEGIN
INSERT INTO Destination Table
(Warehouse,C1, C2, C3) Current Date also needs to be inserted
Values (SELECT C1, C2, C3
FROM MasterTable)
WHERE Warehouse = 01 or Warehouse = 02 or Warehouse = 03
END
Thanks

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 03/07/2014 :  13:42:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
INSERT INTO DestinationTable (Warehouse, C1, C2, C3, DateColumn)
SELECT Warehouse, C1, C2, C3, GETDATE()
FROM MasterTable
WHERE Warehouse in ('01', '02', '03)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 03/07/2014 13:43:14
Go to Top of Page

Patyk
Starting Member

44 Posts

Posted - 03/07/2014 :  13:59:09  Show Profile  Reply with Quote
Still getting error I was able to rewrite this procedure to the following:

BEGIN
INSERT INTO dbo.DailyWarehouseValues
(dbo.DailyWarehosueWalues.EntryDate, dbo.DailyWarehouseValues.StockCode , dbo.DailyWarehouseValues.Warehouse, dbo.DailyWarehouseValues.QtyONHand,dbo.DailyWarehouseValues.dbo.DailyWarehouseValues.QtyOnOrder,dbo.DailyWarehouseValues.QtyOnBackOrder,dbo.DailyWarehouseValues.QtyInTransit,dbo.DailyWarehouseValues.QtyAllocatedWip)
Values (GetDate(),StockCode , Warehouse, QtyONHand, QtyOnOrder,QtyOnBackOrder,QtyInTransit,QtyAllocatedWip
FROM companyB.dbo.InvWarehouse)
WHERE Warehouse in ('01', '02', '03')

My error is :Msg 128, Level 15, State 1, Procedure CopyTableContents, Line 10
The name "StockCode" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 03/07/2014 :  14:09:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
You aren't using the right format. You don't use VALUES when you are providing a SELECT statement. Notice the format of my post. Remove VALUES and () and use SELECT intead.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Patyk
Starting Member

44 Posts

Posted - 03/07/2014 :  14:12:45  Show Profile  Reply with Quote
Ok I have modified this
INSERT INTO dbo.DailyWarehouseValues
(EntryDate, StockCode ,Warehouse,QtyONHand,QtyOnOrder,QtyOnBackOrder,QtyInTransit,QtyAllocatedWip)
Select (GetDate(),StockCode , Warehouse, QtyONHand, QtyOnOrder,QtyOnBackOrder,QtyInTransit,QtyAllocatedWip
FROM companyB.dbo.InvWarehouse)
WHERE Warehouse in ('01', '02', '03')

but still some comma problem
Msg 102, Level 15, State 1, Procedure CopyInvWarehouseTable, Line 6
Incorrect syntax near ','.
Go to Top of Page

Patyk
Starting Member

44 Posts

Posted - 03/07/2014 :  14:27:37  Show Profile  Reply with Quote
Got it thanks.
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.44 seconds. Powered By: Snitz Forums 2000