Copy all values from source table to destination table dynamically in SQL Server. If there is some value in destination table then delete and insert

I have created 2 tables and populated the values in my source table.

Now, I want to populate these values into the destination table dynamically and also check if there is any value in the destination table then delete all those values and insert them from the source.

This is my approach which is not executing:

CREATE TABLE Customer
(
    CustomerID int NOT NULL,
    Customer_Name nvarchar(50) NULL,
    Customer_Address nvarchar(50) NULL,
    Contact int NULL,
    Created_Date int NULL
)

CREATE TABLE Customer_new
(
    CustomerID int NOT NULL,
    Customer_Name nvarchar(50) NULL,
    Customer_Address nvarchar(50) NULL,
    Contact int NULL,
    Created_Date int NULL
)

INSERT INTO Customer VALUES (1, 'Sarthak', 'cp192', 9560, 2022)
INSERT INTO Customer VALUES (2, 'Rashi', 'cp193', 9561, 2021)
INSERT INTO Customer VALUES (3, 'Rohan', 'cp194', 9562, 2020)
INSERT INTO Customer VALUES (4, 'Aman', 'cp195', 9564, 2019)

ALTER PROCEDURE spCustomera
    @Source_table nvarchar(100),
    @Dest_table nvarchar(100)
AS
BEGIN
    DECLARE @Target_Schema NVARCHAR(30)
    DECLARE @Source_Schema NVARCHAR(30)

    SELECT TABLE_NAME, TABLE_SCHEMA 
    INTO #data 
    FROM INFORMATION_SCHEMA.columns

    SELECT 
        @Dest_table = TABLE_NAME, 
        @Source_table = TABLE_NAME, 
        @Target_Schema =TABLE_SCHEMA, 
        @Source_Schema = TABLE_SCHEMA 
    FROM #data

    DECLARE @SQL AS nvarchar(1000)
    SET @SQL = (N'DELETE FROM ' + @Target_Schema + '.' + @Dest_table +
                 ' INSERT INTO ' + @Target_Schema + '.' + @Dest_table + 
                 '     SELECT * FROM ' + @Source_Schema + '.' + @Source_table)
        
    EXEC @SQL 
END

EXEC spCustomera @Source_table = 'customer', @Dest_table = 'customer_new'

Answer

In your select where you are pulling the destination and source table names, they are the same table. So, you’re deleting from the table and then trying to insert back into the same table.

I’m assuming your Customer_new table is the destination table and Customer is the source table.

You should just need to update the following line to append the _new to the end of the destination table name by adding + '_new' to the destinationation table part of the select statement:

select @Dest_table = TABLE_NAME + '_new', @Source_table = TABLE_NAME, @Target_Schema =TABLE_SCHEMA                 
        , @Source_Schema = TABLE_SCHEMA from #data