insert multiple tables into one table

Question

I am a new programmer analyst. I am learning SQL and I am trying to insert data from multiple tables (all the tables have the same fields) into one large history table. Is there an easy way to do this? I have this so far but I know this is wrong:

Create Procedure InsertTables  
@ExistingTableName nvarchar(50) 
AS

   insert into EMF_Archive    
select      
em_Employee_ID,
em_Payroll_Unit,
em_Name,
em_Payroll_Unit_2,
em_Last_Name,
em_First_Name,
em_Middle_Initial,
em_Social_Security_No,
em_Payroll_Group,
em_Employee_Status,
em_Hire_Date,
em_Rehire_Date,
em_Adjusted_Hire_Date,
em_Birth_Date,
em_Termination_Date,
em_Organization,
em_Title,
em_Work_Comp,
em_Annual_Pay,
em_Salary_Rate,
em_Tax_Group,
em_Seniority_Date,
em_service_Years,
em_Service_Months,
em_Grade,
em_Variable_3,
em_Variable_4,
em_Standard_Hours,
em_Variable_10,
em_Variable_11,
em_Shift
from @ExistingTableName  

GO

EXEC InsertTables 'emf_payPeriod1'

I want to execute this several times like this:

EXEC InsertTables 'emf_payPeriod1'
EXEC InsertTables 'emf_payPeriod2'
EXEC InsertTables 'emf_payPeriod3'
  etc

etc.....

Answer 1

You cannot pass the table name like you are trying to, you will need to use dynamic sql for this something like as follows ...

Create Procedure InsertTables  
@ExistingTableName nvarchar(128) 
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N' INSERT INTO EMF_Archive    
            select      
            em_Employee_ID,
            em_Payroll_Unit,
            em_Name,
            em_Payroll_Unit_2,
            em_Last_Name,
            em_First_Name,
            em_Middle_Initial,
            em_Social_Security_No,
            em_Payroll_Group,
            em_Employee_Status,
            em_Hire_Date,
            em_Rehire_Date,
            em_Adjusted_Hire_Date,
            em_Birth_Date,
            em_Termination_Date,
            em_Organization,
            em_Title,
            em_Work_Comp,
            em_Annual_Pay,
            em_Salary_Rate,
            em_Tax_Group,
            em_Seniority_Date,
            em_service_Years,
            em_Service_Months,
            em_Grade,
            em_Variable_3,
            em_Variable_4,
            em_Standard_Hours,
            em_Variable_10,
            em_Variable_11,
            em_Shift
            from ' + QUOTENAME(@ExistingTableName)

   EXECUTE sp_executesql  @Sql
END

GO

Execute Procedure

Further to execute this procedure for all the tables you can execute the following query

--Make sure the following query is pulling all the required tables
SELECT name INTO #Tables
from sys.tables
WHERE name like 'emf_payPeriod%'

-- Now use that temp table to pass table names to your procedure
DECLARE @TableName NVARCHAR(128);

WHILE EXISTS (SELECT 1 FROM #Tables)
BEGIN
  SELECT TOP 1 @TableName = name FROM #Tables

  EXECUTE InsertTables @TableName

  DELETE FROM #Tables WHERE name = @TableName
END