Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

WHERE [column name] NOT IN check after each insert Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 2:24 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:01 AM
Points: 61, Visits: 276
I have a table like this:

OLD_TABLE:

ID National_Code
----------------------
1 0000000000
2 1111111111
3 1111111111
4 1111111111
5 1111111111
6 2222222222
7 3333333333

I need to insert above National_Code values into new table WITH THIS CONDITION:
National_Code shouldnt repeat more than 3 times.
In other word I need to my new table be like this:

NEW_TABLE:

ID National_Code
----------------------
1 0000000000
2 1111111111
3 1111111111
4 1111111111
5 2222222222
6 3333333333

I already tried this:

INSERT INTO NEW_TABLE[ID,National_Code)
SELECT [ID],[National_Code]
FROM OLD_TABLE
WHERE [National_Code] NOT IN(
SELECT [National_Code]
FROM OLD_TABLE
GROUP BY [National_Code]
HAVING COUNT([National_Code]) > 2
)

But it don't work, because inner HAVING condition return 0 results, and it make outer WHERE condition true and wrongly it insert all repetitive 1111111111 National_Codes.

Thank you for help


___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1404612
Posted Wednesday, January 9, 2013 3:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
Something like this should work, provided the New table is empty.

Create TAble #Old_Table
(Id int, National_Code varchar(20))

Create TAble #New_Table
(Id int, National_Code varchar(20))

Insert into #Old_Table
Values (1,'0000000000')
,(2, '1111111111')
,(3, '1111111111')
,(4, '1111111111')
,(5, '1111111111')
,(6, '2222222222')
,(7, '3333333333')


With Cte AS (
Select
ID
,National_code
, ROW_NUMBER() OVER (Partition by National_Code Order by Id) Rn
From #OLD_TABLE
)
INSERT INTO #New_Table [ID,National_Code)
SELECT [ID],[National_Code]
FROM Cte
Where Rn <=3

If its not an empty table then theres more work to be done.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1404644
Posted Wednesday, January 9, 2013 3:39 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:01 AM
Points: 61, Visits: 276
Thank you very much for help. But NEW_Table is not an empty table

There might already be some [National_Codes] in New_Table.


___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1404653
Posted Wednesday, January 9, 2013 3:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
Ok, That is what I thought.

The question is do you want to allow duplicates?

For example would you want to have Id's 1,6 and 7 duplicated in the new table if they already exist?



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1404655
Posted Wednesday, January 9, 2013 4:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
Heres another way, but it assumes that you dont care if other Rows are duplicated, and will add rows to New_Table Provided the total in New_Table doesnt exceed 3 rows

With Cte AS (
Select
ID
,National_code
, ROW_NUMBER() OVER (Partition by National_Code Order by Id) Rn
From #OLD_TABLE
)
INSERT INTO #New_Table
([ID],National_Code)
SELECT [ID],c.[National_Code]
FROM Cte c
left JOIN (Select National_Code
,Count(*) e_cnt
From #New_Table
Group by National_Code) chk
on chk.National_Code=c.National_Code
Where Rn+ISnull(e_cnt,0)<=3



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1404663
Posted Wednesday, January 9, 2013 4:09 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:01 AM
Points: 61, Visits: 276
ID is not primary-key, Its okay if they duplicate.

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1404664
Posted Wednesday, January 9, 2013 4:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
masoudk1990 (1/9/2013)
ID is not primary-key, Its okay if they duplicate.


Ok, looks like our posts crossed, the above should work.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1404665
Posted Wednesday, January 9, 2013 4:55 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:01 AM
Points: 61, Visits: 276
Thank you very much.
I couldn't find thanks button any where.
I'm really really thank you. It worked perfectly.

It solved my problem but I want to learn how does it works.
I will trace this query and If I didn't understand it I will ask you for help.

Thank you again


___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1404686
Posted Wednesday, January 9, 2013 5:04 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
Basically it works by ordering the data in the source table and assiging a Row number to each of the source rows (Select in the CTE)

Then a Left Join with sub select that counts the rows in the Destination table and returns a coutn value.

So if we only want a max of 3 rows, if the Row Number from the first query + the count from the sub select <3 it will insert rows where RN + Count <3.

so

Id 1, 0000000, RN=1
Id 2, 1111111, RN=1
Id 3, 1111111, RN=2
Id 4, 1111111, RN=3

If we have no rows in the destination table, then the count will be NULL (hence the IsNULL(e_cnt,0)), so RN-0<3 will insert all 4 rows.

On a second pass the e_cnt will be 1 for 0000000 and 3 for 1111111, so for 0000000 (RN-1)<3 = true but for 1111111 the e_cnt is 3, so RN-3 <3 is false.

Hope that makes sense.
you can run each the CTE select and sub select on the join individually to see how it works with a populated and unpopulated table.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1404694
Posted Wednesday, January 9, 2013 6:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, January 10, 2013 5:15 AM
Points: 339, Visits: 71
Hello, I would do as follows there are many others but please verify whether the expected results.

Create TAble #Old_Table
(Id int, National_Code varchar(20))

Create TAble #New_Table
(Id int, National_Code varchar(20))

Insert into #Old_Table
Values (1,'0000000000')
,(2, '1111111111')
,(3, '1111111111')
,(4, '1111111111')
,(5, '1111111111')
,(6, '2222222222')
,(7, '3333333333')
,(8, '2222222222')
,(9, '2222222222')
,(10, '2222222222')


SELECT Id, National_Code

,RANK() OVER

(PARTITION BY National_Code ORDER BY Id DESC) AS nTimes

INTO #Temporal

FROM #Old_Table

ORDER BY National_Code;

INSERT INTO #New_Table(Id, National_Code )
select Id, National_Code from #Temporal
where nTimes <= 3

SELECT * FROM #New_Table

DROP TABLE #Temporal

DROP TABLE #New_Table

DROP TABLE #Old_Table

Results:
Id National_Code
----------- --------------------
1 0000000000
5 1111111111
4 1111111111
3 1111111111
10 2222222222
9 2222222222
8 2222222222
7 3333333333

You're welcome...


Ing. Aneurys Tapia González
DBA + BI Architect
Dominican Republic
atapia@outlook.com | admin@atapia.net
http://sqlservermasbi.wordpress.com
http://atapia.net

Post #1404724
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse