SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


WHERE [column name] NOT IN check after each insert


WHERE [column name] NOT IN check after each insert

Author
Message
masoudk1990
masoudk1990
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 299
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.
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5235 Visits: 3232
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
masoudk1990
masoudk1990
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 299
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.
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5235 Visits: 3232
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
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5235 Visits: 3232
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
masoudk1990
masoudk1990
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 299
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.
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5235 Visits: 3232
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
masoudk1990
masoudk1990
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 299
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.
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5235 Visits: 3232
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
atapia_
atapia_
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search