x

find out data which is not inserted in copied table

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.
more ▼

asked Oct 12, 2010 at 10:19 PM in Default

asksql gravatar image

asksql
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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  
more ▼

answered Oct 12, 2010 at 10:41 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

I like that use of a LEFT JOIN.
Oct 13, 2010 at 10:46 AM Mark
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 13, 2010 at 10:59 AM

ozamora gravatar image

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, 2010 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, 2010 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, 2010 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, 2010 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, 2010 at 08:44 AM ozamora
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 13, 2010 at 04:54 PM

Scot Hauder gravatar image

Scot Hauder
6k 13 15 18

hmm, one answer to knocked off two keywords which I never use :)
Oct 13, 2010 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, 2010 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, 2010 at 07:12 PM Scot Hauder
+1 i like intersect and except and uses them from time to time
Oct 13, 2010 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, 2010 at 01:10 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

+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
more ▼

answered Oct 13, 2010 at 06:56 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 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, 2010 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, 2010 at 12:52 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x280

asked: Oct 12, 2010 at 10:19 PM

Seen: 1748 times

Last Updated: Oct 14, 2010 at 04:56 AM