Quickest way to copy records from one table to another sql server

sql sqlbulkcopy sql-server sql-server-2008

Question

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.

Accepted Answer

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

Popular Answer

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;


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why