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!

• McNets: Have a look at MERGE
• Ilan: Thanks, McNets. While that looks generally useful, it doesn't seem to answer my question. And if it does, I have yet to reach the level of expertise at which I can tell as much!

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