How can I find out unique data from large amount of data?

Question

1.00/5 (1 vote)

See more:

i want to find out unique data from huge amount of data like 500000 records in sql 2008.

What I have tried:

DELETE
FROM dbo.w3
WHERE dbo.w3.%%physloc%%
NOT IN (SELECT MIN(b.%%physloc%%)
FROM dbo.w3 b
GROUP BY b.CHASSIS_NO);

SELECT distinct TOP 500000
[CHASSIS_NO]
FROM [TestDB].[dbo].[w3]

Posted 28-Jun-17 23:16pm

Member 13284521

Updated 29-Jun-17 20:45pm

v2

Add a Solution

Comments

Richard MacCutchan 29-Jun-17 5:36am

What is the question?

Member 13284521 29-Jun-17 8:09am

the question is ,if i have huge amount of data than which query is giving right ans and i want to store that data into another table...i tried but table is not created.

Richard MacCutchan 29-Jun-17 8:22am

SELECT returns a set of records so you now need to INSERT them into the other table. See https://www.w3schools.com/sql/sql_select_into.asp.

Member 13284521 29-Jun-17 8:29am

SELECT TOP 500000 [Slno]
,[REGION]
,[CODE]
,[DLRNAME]
,[SALU]
,[NAME]
,[ADDRESS1]
,[ADDRESS2]
,[ADDRESS3]
,[CITY]
,[PIN]
,[MOBILE_NUM]
,[CHASSIS_NO]
,[REGIST]
,[ENGINE]
,[MODEL]
,[MODEL CODE]
,[KILOMETER]
,[TYPE]
,[SERVICE_DT]
,[BILLING_DT]
,[Month ]
,[Year]
,[Quarter 12-13]
,[Quarter 13-14]
,[Quarter 14-15]
,[Quarter 15-16] into #temp
FROM [TestDB].[dbo].[w2] order by [Slno]

is this right query???

Richard MacCutchan 29-Jun-17 9:02am

No idea. Why don't you try it with a small number of records to find out?

Member 13284521 30-Jun-17 4:42am

small amount data converted proper manner but problem occur in huge amount of data.

Richard MacCutchan 30-Jun-17 4:59am

See my comment to your other question on this subject.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Accepted Answer · 2017-06-29T08:42:00

Solution 1

To store the data in a new table:

SQL

SELECT distinct TOP 500000 [CHASSIS_NO] 
INTO newtable
FROM [TestDB].[dbo].[w3]

Posted 29-Jun-17 8:42am

RickZeeland

Comments

Member 13284521 30-Jun-17 1:14am

little more help sir..when i convert excel data into sql ,my data became random means slno not in mannner .so what can i do now?

RickZeeland 30-Jun-17 4:22am

Restore from a backup ?

Member 13284521 30-Jun-17 4:33am

nope. actual data in excel format and i want to convert into sql without changing.

RickZeeland 30-Jun-17 5:34am

Maybe you can use this, don't know if it works for SQL Server Express: https://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/

Member 13284521 30-Jun-17 6:15am

i already use this method.but still problem in converted data.

RickZeeland 30-Jun-17 5:37am

Here is a more complicated way with Visual Studio: https://www.simple-talk.com/sql/ssis/moving-data-from-excel-to-sql-server-10-steps-to-follow/