I'm trying to copy rows from one table to another table (one table is almost a mirror copy of the other). The code I'm using isn't working. Instead of copying data from one table to another, it appears to just make a copy of the records that match the destination table instead of the source table.

Here's the code I'm using:
insert into weekhistory (Header_ID,DayOfWeek,Hrs,Rate_ID,AcctCode,Remarks, ProfitCenter,Shift_ID,Reason_ID,StandBy,EditDate,E ditBy,AlreadyProcessed)
(select Header_ID,DayOfWeek,Hrs,Rate_IDAcctCod,Remarks,Pro fitCenter,Shift_ID,Reason_ID ,StandBy ,getdate(),'197748','Y'
from week where header_id=156719)

What am I doing wrong ?

To mirror copy a table you can just use this, but in your case, there is nothing got to be used with WHERE Clause.

Select * into NEW_TABLE from OLD_TABLE

NEW_TABLE - will now have a mirro copy of the OLD_TABLE

Cheers!


-Vijay G

Does this new table already exist? If not then use this;
CREATE TABLE weekhistory AS
SELECT Header_ID, DayOfWeek, Hrs, Rate_IDAcctCod, Remarks, ProfitCenter, Shift_ID, Reason_ID, StandBy, getdate(), '197748', 'Y'
FROM week
WHERE header_id=156719;

If the table already exist and you are just adding new data to it:

INSERT INTO weekhistory (Header_ID,DayOfWeek,Hrs,Rate_ID,AcctCode,Remarks, ProfitCenter,Shift_ID,Reason_ID,StandBy,EditDate,E ditBy,AlreadyProcessed)
VALUES (SELECT der_ID, DayOfWeek, Hrs, Rate_ID, AcctCod, Remarks, ProfitCenter, Shift_ID, Reason_ID, StandBy, getdate(), '197748', 'Y'
from week where header_id=156719);

Try that...





Skipmeok

I'm getting a syntax error using the code you suggested.

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ')'.

which one did you use to get that error? there were many posted here.

-Vijay G