I have an employee database table with the attributes EmpID and EmpName. The columns in my second table, called EmployeeVersion, are PKID(auto), EmpID, EmpName, Month, and Year.
How quickly might data be sent from the Employee table to the EmployeeVersion table? The Month, Year would either include the month and year that the data was copied in or values that were provided from c# code to the stored function (@Month, @Year). Please provide an answer to both problems.
As far as I'm aware, I can't use the following statement since both tables have different numbers of columns:
Insert Into EmployeeVersion (EmpID, EmpName, Month, Year) select * from Employee
advise me, please. Thanks.
Column names should be specified in the select statement, and reserved terms should be escaped.
Insert Into EmployeeVersion (EmpID, EmpName, [Month], [Year]) select EmpId, EmpName, MONTH(GETDATE(), YEAR(GETDATE()) from Employee
Moreover, 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 month and year separated into distinct columns? Why not just include the date in the data when it is inserted?
Insert Into EmployeeVersion (EmpID, EmpName, Month, Year) select EmpID, EmpName, month(getdate()), year(getdate()) from Employee;
Note that you may use a column with the name
it is automatically allocated. You could specify it as:
create table . . . CreatedAt datetime not null default getdate()
Insert Into EmployeeVersion (EmpID, EmpName) select EmpID, EmpName from Employee;