Quickest way to copy records from one table to another sql server
sql sqlbulkcopy sql-server sql-server-2008
Question
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.
Accepted Answer
Column names should be specified in the select statement, and reserved terms should be escaped.Month, Year
with []
:
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
Popular Answer
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 nameCreatedAt
it is automatically allocated. You could specify it as:
create table . . .
CreatedAt datetime not null default getdate()
Next, theinsert
would resemble:
Insert Into EmployeeVersion (EmpID, EmpName)
select EmpID, EmpName
from Employee;
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow