How to Insert Distinct Records from Table a to Table B (Both Tables Have Same Structure)

Insert distinct values SQL Server

You can get a list of all passports with multiple entries by running this query:

Select Passport, Count (*) NumEntries
From  fromTable
Group by Passport
Having Count (*) > 1

Then you have to decide what to do with these duplicate rows. Run the following query to see the full row for these duplicates:

Select *
From  fromTable
Where Passport In
(
    Select Passport, Count (*) NumEntries
    From  fromTable
    Group by Passport
    Having Count (*) > 1
)
Order by Passport

Let's say you decide to go with the newest row inserted for each passport (meaning the Id would be the highest), this query will give you the data you need.

Select T1.*
From  fromTable T1
Where Id In
(
    Select Max (Id) Id
    From  fromTable
    Group by Passport
)

You can insert using

INSERT into toTable (id, passport, name, surname, address) 
Select T1.*
From  fromTable T1
Where Id In
(
    Select Max (Id) Id
    From  fromTable
    Group by Passport
)

How to insert one row from Table A to Table B But not Primay key

There is no select * except some columns. Your sql must contain the full list of columns except the id for both tables.

However, this does not mean you have to manually type them. You can generate the columns list by selecting them from sys.columns or from information_schema.columns.

If you are using SSMS, you can expand the columns list of one of the tables and simply drag the columns to your query window. If you want to drag them all, simply drag the columns "folder" icon to your query window.

How to get the records back from two tables with same primary key if any of the other column fields are changed

Since you want to check for all columns writing a where clause for it might be tedious so you can use information_schema.columns to get the column names for that table and then using a dynamic query you can check for column differences.

The following might be the solution to your problem.

--Simulate your table structure
CREATE TABLE TableA
(
    NAME VARCHAR(100),
    AGE INT,
    ID INT,
    DATE_COL DATETIME
)

CREATE TABLE TableB
(
    NAME VARCHAR(100),
    AGE INT,
    ID INT,
    DATE_COL DATETIME
)

--Data for testing
INSERT INTO TABLEA(NAME, AGE, ID, DATE_COL) VALUES('David',11,1,'01/11/2014')
INSERT INTO TABLEA(NAME, AGE, ID, DATE_COL) VALUES('Claire',16,2,'03/13/2014')
INSERT INTO TABLEA(NAME, AGE, ID, DATE_COL) VALUES('Max',15,3,'02/20/2014')
INSERT INTO TABLEA(NAME, AGE, ID, DATE_COL) VALUES('John',14,4,'09/19/2014')
INSERT INTO TABLEA(NAME, AGE, ID, DATE_COL) VALUES('James',12,5,'06/16/2014')

INSERT INTO TABLEB(NAME, AGE, ID, DATE_COL) VALUES('Max',15,3,'05/15/2014')
INSERT INTO TABLEB(NAME, AGE, ID, DATE_COL) VALUES('Will',14,4,'04/12/2014')
INSERT INTO TABLEB(NAME, AGE, ID, DATE_COL) VALUES('Bill',12,7,'04/11/2014')
INSERT INTO TABLEB(NAME, AGE, ID, DATE_COL) VALUES('Paul',11,8,'12/24/2013')
INSERT INTO TABLEB(NAME, AGE, ID, DATE_COL) VALUES('Kevin',13,9,'04/03/2014')

--Solution Starts from here 
CREATE TABLE #TableCols
(
    ID INT IDENTITY(1,1),
    COLUMN_NAME VARCHAR(1000)
)

--since both tables have same columns you can take columns of any 1 table
INSERT INTO #TableCols
(COLUMN_NAME)
SELECT COLUMN_NAME
FROM information_schema.columns
WHERE table_name = 'TableA';

DECLARE @STARTCOUNT INT, @MAXCOUNT INT, @COL_NAME VARCHAR(1000), @QUERY VARCHAR(8000), @SUBQUERY VARCHAR(8000)

SELECT @STARTCOUNT = 1, @MAXCOUNT = MAX(ID) FROM #TableCols;
SELECT @QUERY = '', @SUBQUERY = ''

WHILE(@STARTCOUNT <= @MAXCOUNT)
BEGIN
    SELECT @COL_NAME = COLUMN_NAME FROM #TableCols WHERE ID = @STARTCOUNT;

    IF(@COL_NAME != 'DATE_COL' AND @COL_NAME != 'ID')
    BEGIN
        SET @SUBQUERY = @SUBQUERY + ' A.' + @COL_NAME + ' != B.' + @COL_NAME + ' OR ';
    END

    SET @STARTCOUNT = @STARTCOUNT + 1
END

SET @SUBQUERY = LEFT(@SUBQUERY, LEN(@SUBQUERY) - 3);
SET @QUERY = 'SELECT A.*, B.* FROM TableA A INNER JOIN TableB B ON A.ID = B.ID WHERE A.DATE_COL != B.DATE_COL AND (' + @SUBQUERY + ')';
EXEC (@QUERY);

Hope this helps.

How do I compare two structurally similar tables and only return the column name and value of differing values in SQL Server?

Sounds like UNPIVOT is what you need. From MSDN: Using PIVOT and UNPIVOT

Here's a possible solution to your question:

SELECT UniqueID, ColumnName, Value, ImportDate, AuditDate, Action
FROM
(
    SELECT 
        TableA.UniqueID, 
        CAST((CASE WHEN ISNULL(TableB.Address, '') <> ISNULL(TableA.Address, '')
              THEN ISNULL(TableB.Address, '')
              ELSE NULL END) AS nvarchar(255))
        AS Address, 
        CAST((CASE WHEN ISNULL(TableB.HairColor, '') <> ISNULL(TableA.HairColor, '')
              THEN ISNULL(TableB.HairColor, '')
              ELSE NULL END) AS nvarchar(255))
        AS HairColor, 
        TableB.ImportDate,
        TableB.AuditDate,
        TableB.Action
    FROM TableA INNER JOIN TableB ON TableB.UniqueID = TableA.UniqueID
) AS p
UNPIVOT
(
    Value FOR ColumnName in (Address, HairColor)
) AS up
WHERE Value IS NOT NULL

Note that I cast the values to nvarchar(255) in order to resolve conflicts between types. You may need adjust that to something more appropriate.

Also, at a glance, I do think the generated results may need some refining based around the import and audit dates. But that's another topic. Also, I ended up using an INNER JOIN since a LEFT JOIN didn't make sense to me in this case. Maybe I'm missing something.

So, all that said, this should get the results that you're looking for.

Fastest way to find distinct matching records

Each one of these solutions are taking time, the best one (Justin) took almost 45 mins without even returning for 2 million records. I ended up with inserting matching records in a temp table and then removing duplicates and i found it much faster than these solutions with this data set.

Insert rows from TableA that are not in TableB using concatenated keys

Simply use NOT EXISTS:

INSERT INTO billing..whse
SELECT * FROM billing..whse_temp S
WHERE NOT EXISTS
(
  SELECT NULL
  FROM billing..whse w
  WHERE w.runtimestamp = s.runtimestamp
    AND w.cs_datacenter = s.cs_datacenter
    AND w.customer = s.customer
    AND w.servername = s.servername
);

The appropriate index for this:

CREATE INDEX idx ON billing..whse (runtimestamp, cs_datacenter, customer, servername);

---

Related Topics