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.

1
1
2/6/2015 11:51:28 AM

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
2
2/6/2015 12:17:54 PM

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
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow