1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussão em 'Outras Linguagens' iniciado por Stack, Setembro 24, 2021.

  1. Stack

    Stack Membro Participativo


    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...

Compartilhe esta Página