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;