Menu
I want to insert only Distinct Records from Table "A" to Table "B". Assume both the tables has same structure.
669
INSERT DISTINCT Records INTO New Tables In order to copy data from an existing table to a new one, you can use the "INSERT INTO SELECT DISTINCT" pattern. After "INSERT INTO", you specify the target table's name - organizations in the below case.
The T-SQL function OUTPUT, which was introduced in 2005, can be used to insert multiple values into multiple tables in a single statement. The output values of each row that was part of an INSERT, UPDATE or DELETE operation are returned by the OUTPUT clause.
Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
If by DISTINCT you mean unique records that are on TableB that aren't already in TableA, then do the following:
INSERT INTO TableB(Col1, Col2, Col3, ... , Coln)
SELECT DISTINCT A.Col1, A.Col2, A.Col3, ... , A.Coln
FROM TableA A
LEFT JOIN TableB B
ON A.KeyOfTableA = B.KeyOfTableB
WHERE B.KeyOfTableB IS NULL
INSERT INTO B SELECT DISTINCT * FROM A
You might not want the id column of the table to be part of the distinct check, so use this solution if that's the case: https://stackoverflow.com/a/5171345/453673
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
