find out data which is not inserted in copied table

Question

i have two table t1 and t2
in t2 i created dual primary key on two columns
i want to insert t1 table data to the t2 table.
bt before inserting t1 table data to the t2 table
i want to create report of data which will not going to insert in t2 table
Means because of dual primary key of t2 table ,only unique records will insert ,nt duplicate.
nw in this scenario before inserting i want to create report of data which is not going to insert?
I used for insert and after insert, but i want the details which is not going to insert before inserting data in second table.Please Help me.
Thanks in Advance.

Answer 1

0

SourceTable (C1, C2, C3) C1 & C2 Composite Primary key DestinationTable (dC1,dC2,dC3) dC1 & dC2 Composite primary key

  -- Records that will not insert on DestinationTable SELECT C1,C2 FROM SourceTable INNER JOIN DestinationTable ON C1 = dC1 AND C2 = dC2

-- Records that will insert on DestinationTable SELECT C1,C2 FROM SourceTable LEFT JOIN DestinationTable ON C1 = dC1 AND C2 = dC2 WHERE dc1 IS NULL AND dc2 IS NULL

answered Oct 12 '10 at 10:41 PM

Cyborg
10.1k ● 29 ● 39 ● 44

I like that use of a LEFT JOIN.

Oct 13 '10 at 10:46 AM Mark

(comments are locked)

Answer 2

0

If I understood your question correctly, this might be viable.

Report of data which is not going to insert:

-- Assuming that the unique constraint in t2 is (column1, column2):
WITH CTE_data AS (
    SELECT column1, column2, ..., columnN, 
       ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY column1) AS sequence
    FROM t1
) SELECT column1, column2, ..., columnN
FROM CTE_data
WHERE sequence > 1

Insert:

-- Insert Unique Rows:
WITH CTE_data AS (
    SELECT column1, column2, ..., columnN, 
       ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY column1) AS sequence
    FROM t1
) INSERT INTO t2 (column1, column2, ..., columnN)
SELECT column1, column2, ..., columnN
FROM CTE_data
WHERE sequence = 1

answered Oct 13 '10 at 10:59 AM

ozamora
1.4k ● 2 ● 3 ● 5

I would +2 you if I could, this is clearly the best answer here. I'm not sure if people are reading the question right.

Oct 14 '10 at 01:11 AM Matt Whitfield ♦♦

I think this is the closest to the correct solution. That only point I would add is that the windowing function, only partitioning/ordering on col1 & col2 does not make it deterministic for t1, assuming no key on t1, which I think the OP is saying. Given that, the report (first query) could return rows that are actually inserted. This can be fixed by ordering by all columns in the windowing function. Again assuming no two rows are exactly the same--which gets back to the rule of always having a key. If the OP does have a key on t1.col one then there will be no problem moving any rows to t2 and this is moot

Oct 14 '10 at 05:13 AM Scot Hauder

@Scot Hauder. Based on the question seems that ordering the data is not a requirement. So "ORDER BY column1" is really a placeholder to meet the windowing requirement of having an order by. It can simply be just "ORDER BY NEWID()" and randomize the output for each partition.

Now, if OP is running SQL Server 2000, then this answer gets invalidated. I hope he is not.

Oct 14 '10 at 06:12 AM ozamora

It is required for your solution to work correctly. The windowing function needs to be deterministic, meaning the combined columns in the partition and order by need to uniquely identify the row so that they are always numbered consistently. Eg after running your first query the sequence #1 might be labeled sequence #2 and what was sequence #2 on the report is now sequence #1 during the insert

Oct 14 '10 at 07:45 AM Scot Hauder

@Scot Hauder. You are right. OP will need to order by a unique constraint within the partition. The safest route will be to include all columns.

Oct 14 '10 at 08:44 AM ozamora

(comments are locked)

Answer 3

0

If they are true copies, meaning columns in t1 are not modified after they have been copied to t2 you can do this.

Report:

SELECT * FROM t1 
INTERSECT
SELECT * FROM t2

Insert:

INSERT t2
SELECT * FROM t1 
EXCEPT
SELECT * FROM t2

answered Oct 13 '10 at 04:54 PM

Scot Hauder
5.7k ● 13 ● 15 ● 18

hmm, one answer to knocked off two keywords which I never use :)

Oct 13 '10 at 05:01 PM Scot Hauder

@Scot Hauder I know that intersect and except exist, but personally, I never used them in T-SQL yet simply because they smell like Oracle where they were available and widely used since way back when :)

I suppose that they will not work if the tables have some varchar(max), varbinary(max) or xml columns which are not too suitable for selecting distinct records :)

Oct 13 '10 at 06:53 PM Oleg

I want to say Peso has done some extensive performance tests on these and EXCEPT actually out performs many other solutions--but I never see a real use for them for the reasons you mention and because I rarely need to compare every column. The other answers are more realistic since data changes. OTOH MERGE might be more apropos for this...

Oct 13 '10 at 07:12 PM Scot Hauder

+1 i like intersect and except and uses them from time to time

Oct 13 '10 at 11:54 PM Håkan Winther

See I would +1 this, if it was the answer to a different question... But he's not saying 'I already have data in table B' - he's saying 'I have data in table A with a PK on one column, and in table B the PK would be on two columns - so what data will I have a problem with'?

Oct 14 '10 at 01:10 AM Matt Whitfield ♦♦

(comments are locked)

Answer 4

0

+1 to Cyborg, but to find the actual records which have more than one entry for the given keys, you could use something like this:

SELECT * FROM SourceTable
INNER JOIN (SELECT C1, C2
              FROM SourceTable 
             GROUP BY C1, C2
            HAVING COUNT(*) > 1) iDat 
   on iDat.C1 = SourceTable.C1 
  and iDat.C2 = SourceTable.C2

answered Oct 13 '10 at 06:56 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

wouldnt you need something to guarantee the order for the rows from t1 so that you can be certain which gets inserted and which is left behind?

Oct 14 '10 at 05:34 AM Fatherjack ♦♦

I wasn't actually saying 'here's how to do the insert' - just how to find the rows which would cause a problem - showing both rows in each case...

Oct 14 '10 at 12:52 PM Matt Whitfield ♦♦

(comments are locked)

find out data which is not inserted in copied table

Follow this question

Related Questions