SQL Insert Into New Table Or Else Insert Overwrite Into Existing Table

Question

What is the SQL to do the following in SQL Server:

I want to take rows from tbl_A and insert the distinct grouping of them into tbl_B.

If tbl_B doesn't yet exist, I want tbl_B to be created exactly like tbl_A, but receive only the distinct grouping of tbl_A's rows.

If tbl_B already exists, I still want the same data inserted but I want all existing rows in tbl_B to be deleted beforehand.

Of course, I need to do this across multiple tables (about 100).

Also, at the expense of stating the obvious, I do not want to have to specify any column names anywhere. The only parts of the script that should need to change as I do this for each pair of tables is the table names. Thanks!

Answer 1

Since you are not going to specify the column names you are going to have to go with SELECT * it is not ideal, but works if the tables are going to be new.

You could do it with the query below, but you will have to change your grouping columns each time. Unless you can simply use DISTINCT.

If your tables are big, You should look into splitting it in batches.

More info on batches

Test TableA

CREATE TABLE  dbo.tbl_A(ID INT, Val varchar(255));

INSERT INTO  dbo.tbl_A(id,val) 
VALUES(1,'bla'),(1,'bla'),(2,'bla'),(2,'ugh'),(3,'bla');

Query with group by

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'tbl_B'))
BEGIN
    TRUNCATE TABLE dbo.tbl_B
    INSERT INTO  dbo.tbl_B
    SELECT * 
    FROM dbo.tbl_A
    GROUP BY ID,VAL;
END
ELSE 
BEGIN
    SELECT * 
    INTO dbo.tbl_B
    FROM dbo.tbl_A
    GROUP BY ID,VAL;
END

Query with distinct

  IF (EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_SCHEMA = 'dbo' 
                     AND  TABLE_NAME = 'tbl_B'))
    BEGIN
        TRUNCATE TABLE dbo.tbl_B
        INSERT INTO  dbo.tbl_B
        SELECT DISTINCT * 
        FROM dbo.tbl_A;
    END
    ELSE 
    BEGIN
        SELECT DISTINCT * 
        INTO dbo.tbl_B
        FROM dbo.tbl_A;
    END

DB<>Fiddle