copy table to table plus insert current date

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

SQL Server 2005 Forums

Transact-SQL (2005)

copy table to table plus insert current date

Author

Topic

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-07 : 12:51:43

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

38200 Posts

Posted - 2014-03-07 : 13:42:36

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/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-07 : 13:59:09

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-07 : 14:09:57

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/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-07 : 14:12:45

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 ','.

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-07 : 14:27:37

Got it thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources