0

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'
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sarthak Gupta
  • 7
  • 1
  • 4

1 Answers1

1

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
Marcus Parsons
  • 1,714
  • 13
  • 19
  • It makes no difference still it's not executable – Sarthak Gupta Sep 24 '21 at 16:16
  • My other suggestion would be to terminate each sequence of commands inside the dynamic SQL with the `;` in between the delete and insert. i.e. ` SET @SQL = (N'DELETE FROM ' + @Target_Schema + '.' + @Dest_table + '; INSERT INTO ' + @Target_Schema + '.' + @Dest_table + ' SELECT * FROM ' + @Source_Schema + '.' + @Source_table)` – Marcus Parsons Sep 25 '21 at 00:24
  • Why isn't it taking `Customer_new` as the destination table and `Customer` as the source table as I am supplying the parameters while executing the stored procedure? Also, while executing the above block it is taking `Customer_new_new` at the destination and `Customer_new` at the source automatically. – Sarthak Gupta Sep 25 '21 at 02:50
  • Because that is not what you're doing. You are overwriting what you passed in within your procedure. Don't overwrite the source and destination table parameters. – Marcus Parsons Sep 25 '21 at 03:05
  • And how am I overwriting it, if you could explain it in brief? – Sarthak Gupta Sep 25 '21 at 03:53
  • Inside your procedure, right after you select `into #data` you select `@dest_table` and `@source_table` with information from your `#data` table, which is the first table_name in your `#data` table, effectively overwriting the parameters you passed in. – Marcus Parsons Sep 25 '21 at 04:06