I have a database table (Employee) with following fields: EmpID, EmpName. I have a second table (EmployeeVersion) with following fields: PKID(auto), EmpID, EmpName, Month, Year.
What would be the quickest way to copy data from Employee table to EmployeeVersion table. The Month, Year would either contain the month and the year when the data was copied or values sent to Stored procedure (@Month, @Year) from c# code. Please provide solution for both scenarios.
As far as I know, I can't used following statement because the number of columns don't match in both tables:
Insert Into EmployeeVersion (EmpID, EmpName, Month, Year) select * from Employee
Please advice. Thanks.
You should specify columns in the select statement and escape reserved words used as column names
Month, Year with
Insert Into EmployeeVersion (EmpID, EmpName, [Month], [Year]) select EmpId, EmpName, MONTH(GETDATE(), YEAR(GETDATE()) from Employee
And stored procedure:
CREATE PROCEDURE [dbo].[CopyEmployee] @Month INT, @Year INT AS BEGIN SET NOCOUNT ON; Insert Into EmployeeVersion (EmpID, EmpName, [Month], [Year]) select EmpId, EmpName, @Month AS [Month], @Year AS [Year] from Employee; END GO
Why are you storing month and year in separate columns? Why not just put the date in when the data is inserted?
Insert Into EmployeeVersion (EmpID, EmpName, Month, Year) select EmpID, EmpName, month(getdate()), year(getdate()) from Employee;
Note that you can have a column called
CreatedAt that is assigned automatically. You would define it as:
create table . . . CreatedAt datetime not null default getdate()
insert would look like:
Insert Into EmployeeVersion (EmpID, EmpName) select EmpID, EmpName from Employee;