[SQL] SQL: Copy all values from source table to destination table dynamically in mssql, if...

1. 
   I have created 2 tables and populated the values into 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.
   
   
2. 
   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
Go

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

Continue reading...