4

I'm trying to insert data from one table into another table and I also have 4 columns that are not in the source table but need to be populated in the target table. Here is the Insert statement

SET IDENTITY_INSERT dbo.district ON

INSERT INTO Table1 (dim_district_key, workday_id, 
district_code,district_abbr,district_name,state,region,dss_start_date,
dss_end_date,dss_current_flag,dss_version, dss_update_time, created_by, 
created_on, last_updated_by, last_updated_on)

SELECT dim_district_key, workday_id, district_code, district_abbr,
district_name, state,region, dss_start_date, dss_end_date, dss_current_flag,
dss_version, dss_update_time, SYSTEM_USER, GETDATE(), SYSTEM_USER, GETDATE()
FROM Table2

These columns are not in table2: created_by, created_on, last_updated_by, last_updated_on

When I run the above insert statement I get
Msg 207, Level 16, State 1, Line 4
Invalid column name 'created_by'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'created_on'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'last_updated_by'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'last_updated_on'.
4

The obvious answer is that those columns aren't in Table1 either. Otherwise I don't see an obvious reason why that would fail. It looks like you're adding data to the table that could easily be captured by a default value. See part B for an example of using SYSTEM_USER as a default value or just see the code shamelessly copied below:

CREATE TABLE Sales.Sales_Tracking
(
    Territory_id int IDENTITY(2000, 1) NOT NULL,
    Rep_id  int NOT NULL,
    Last_sale datetime NOT NULL DEFAULT GETDATE(), -- Your date info
    SRep_tracking_user varchar(30) NOT NULL DEFAULT SYSTEM_USER -- System user
);

By setting these as a default value on Table2 you might just be able to add those four columns to Table2 without having to bother with the insert. Alternatively you can set the date and user columns to default to GETDATE() and SYSTEM_USER respectively on Table1. That would eliminate the need to specify those values in your insert statement, and give you good defaults moving forward from the looks of things.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.