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()
Then the insert
would look like:
Insert Into EmployeeVersion (EmpID, EmpName)
select EmpID, EmpName
from Employee;