Inserting Unique Records
Apr 24, 2007
Hello,
I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.
How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?
Does my request make sense? Please let me know if you have questions.
Thank you for your help!
CSDunn
View 3 Replies
ADVERTISEMENT
Apr 24, 2001
What would be the best way to insert unique values into a table/unique column ?
I cannot make that table/unique column as indentity. Right now, I use a staging table with indentity column, insert rows then insert rows back to
final table.
Suggestions are much appreciated.
Ivan
View 2 Replies
View Related
Oct 8, 2007
hi,
In the below code, i am trying to insert the identity value into the 2nd column of the Symp_Quote table. Schema of the table Symp_Quote is as follows.
CRAETE TABLE Symp_Quote
(
QuoteRevision_ID INT PK,
DocumentSeq TINYINT PK,
DocumentDisplayName VARCHAR (255)
)
1st column data is coming from the select statement. but for 2nd column i have to insert identity or some unique value. to do that i was trying withe the following code, but it failed.
INSERT INTO Symp_Quote
(
QuoteRevision_ID,
DocumentSeq,
DocumentDisplayName
)
SELECT
S.QuoteRevision_ID,
IDENTITY (INT,1,1) AS DocumentSeq,
T.Name,
FROM Symp S
INNER JOIN Trilogy T
ON
SQR.tril_gid = FQR.tril_gid
kindly correct me whr I am wrong.........
Thanks,
Rahul Jha
View 5 Replies
View Related
Sep 18, 2007
As a beginner i am having trouble with this.
i have two different tables , both have a name column, nvarchar datatype.
I would like to select from table B all the rows which contain a name which is not in table A.
Then insert these rows, into table A
tried a few different ways & just keep getting strange errors that refer to courier font ??
SQL Team Your my Hero !
View 11 Replies
View Related
Jul 30, 2007
Hi
I am trying to insert values into a table that doesn't exist there yet from another table, my problem is that because it is joined to the other table it keeps on selecting more values that i don't want.
Code Snippet
SET NOCOUNT ON
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'
WHEN 2 THEN '8'
WHEN 3 THEN '9'
WHEN 4 THEN '10'
END
FROM Members M
INNER JOIN _MemberProfileLookupValues ML
ON M.MemberID = ML.MemberID
WHERE M.Active = 1
AND OptionID <> 6
When i execute that code it returns all the values, let say OptionID = 3 is smoking already exists in the MemberProfileLookupValues table then it is going to select that persons memberID
I want to insert only members values that aren't already in the _MemberProfileLookupValues from the Members table (I think that it is because of the join statement that is in my code, but i don't know how i am going to select members that aren't in the table, because i have a few other queries that are very similar that are inserting different values, so ultimately
ONLY INSERT THE MemberID the values 6 and the statusID of X if it is not in the table already.
Any ideas / help will be greatly appreciated. Please help.
Kind Regards
Carel Greaves
View 3 Replies
View Related
Jan 8, 2008
Hi all,
I've got a large table (3mil records) with a number of columns, but currently no way to refer to any individual column. I therefore need a primary key, but does anyone know of a SQL statement I can use that will create a column (say, ID) that is automatically filled with an incrementing 'counter'? Or, instead, how can I set unique incremental values after first creating the column?
Many thanks,
Graham
View 7 Replies
View Related
Sep 13, 2006
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View 4 Replies
View Related
May 6, 2004
Is there a way to get a Stored Procedure to return only unique records from a table? I am using a Stored PRocedure to query a table and it returns all the records in the table and there are many duplicates in the information. I am using SQL Server 2K.
View 12 Replies
View Related
May 12, 2008
I have the following sql:
SELECT COUNT(patient.patientID) AS total_patients
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE sub_categoryID = 4 OR patient_record.allocated = 4
from the database this gives me a COUNT of 22, it should only be 10. I am doing a join and it gives the total records in the two tables where I only want the total in the left table(patient).
How can I GROUP BY patient.patientID in a COUNT query
Thanx, Robson
View 2 Replies
View Related
Sep 17, 2007
Hello Everyone and thanks for your help in advance. I have a SQL Server Table wtih approximately 100,000 records. I need to determine if there are duplicate records in this table. My probelm is that there is a unique ID column that was added for each row, so I'm not exactly sure how to filter the rows. Any help on this would be greatly appreciated. Thanks.
View 4 Replies
View Related
Apr 15, 2008
I'm trying to read from a table and return only the unique records from a table.
The table has 3 columns,
ID - Autoinc
Amount - decimal 6,2
Name - varchar(10)
If there are records like
ID Amount Name
1 0.03 Name1
2 0.07 Name9
3 0.05 Name3
4 0.03 Name8
5 0.07 Name4
6 0.06 Name7
I am wanting to retreive only records 3 & 6 (values 0.05
0.06) as they are the only ones where amount is unique i.e. only 1 entry in the table.
I put the following sql statement together which achieves this but it it only returns the amount value
select Count(amount),amount from bids group by amount having count(amount)<2 order by amount
So I then expanded it to read
select Count(amount),amount,name from bids group by amount,name having count(amount)<2 order by amount
not sure where i'm going wrong but the results returned were
0.03
0.03
0.05
0.06
0.07
0.07
Appreciate any help here.
View 2 Replies
View Related
Dec 3, 2007
Let's say I have a data entry from a pool of employees:
table is as follow:
EmpNo Branch Date Amount
1 A101 11/30/2007 $0.90
1 A101 11/30/2007 $1.20
2 A101 11/30/2007 $0.90
3 A101 11/30/2007 $0.80
How can I select the whole table and only take in 1 unique latest entry if there are multiple entries for the same day, same branch under same employee number?
Thanks! :D
View 7 Replies
View Related
Mar 21, 2014
I am trying to create a select query similar to the following but the problem I am having is that I want to only select one record where there may be several with the same dw_order_no. I have tried various ways using SQL developer but without success
SELECT VE_EZP_ORDER_TRANS.EZP_BILL_STATUS AS EZP_BILL_STATUS1,
VE_EZP_AGED_CUSTOMER_DEBT.SURNAME,
VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE_CATEGORY,
VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE,
VE_ORDERLINE.DW_ORDER_NO,
[code]...
View 3 Replies
View Related
Oct 7, 2005
Steve writes "I need a query to show all the medicare patients only. I want only one kind of insurance for each patient. I am getting records where each patient has more than one insurance like medicare and something else.
Each patient has one,two, three or more insurances like
medicare,medical,blue cross,tricare etc.
The result should look like this:
Sam pick Medicare
John white Medicare
Ann Richmond Medicare
My query retrieves patients with more than one insurance.
select patient_first_name f_name,patient_last_name l_name,patient_zip zip,
count(distinct payer_id) payer_id
from claims
where payer_id ='abcdefhj'
group by patient_first_name, patient_last_name,patient_zip
having count(distinct payer_id)= 1
order by patient_zip asc;
please help me. Greatly appreciate your response."
View 1 Replies
View Related
Mar 5, 2008
Could some one please help me with the following query.
I have multiple tables link together base on wo_No and prt_Mark fields and I get the following results.
wo_No | prt_Mark | dwg_Seq |
324037 | d400 | 1S2 |
324037 | d400 | A1 |
324037 | d400 | 1 |
219001 | 56 | 2B |
219001 | 56 | 2C |
219001 | 56 | 2C |
What I would like is to combine the unique dwg_Seq where they have the same wo_No and prt_Mark.
324037 | d400 | 1S2,A1,1 |
219001 | 56 | 2B,2C |
I would also be happy with the following results get the Max unique dwg_Seq and count how many more unique dwg_Seq there are.
324037 | d400 | A1 + 2 |
219001 | 56 | 2B + 1 |
Any help would be greatly appreciated.
View 2 Replies
View Related
Nov 23, 2007
I have a stored procedure that appends data from a temp table to a destination table. The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates.
The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table?
Thanks in advance.SMc
View 2 Replies
View Related
Apr 28, 2008
Hi All,
I need a bit of help with a join. I have 2 tables :
TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue
Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
thanks in advance
View 11 Replies
View Related
Mar 17, 2014
Using SSE 2012 64-bit...How can I check all fields for duplicate records?I tried OVER PARTITION..But that is returning an error message
Code:
USE db
SELECT ROW_NUMBER() OVER (PARTITION BY all fields)
ORDER BY ID --Not unique) AS RowNumber
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator
View 4 Replies
View Related
Jun 7, 2008
I am new to SQL, still learning.
If I want to retreive unique records for e.g. VendorName,City and State however no 2 vendors must be of the same city or state.
How can I do this?
I have used Select and distinct but that still brings unique vendornames in the same state and city. I only need one vendor per city and State.
Help!
View 4 Replies
View Related
Mar 5, 2006
I am trying to pull only those records with a maximum upload date for a file upload log.
This table keeps a list of files uploaded by supplierID and will have multiple records from the same supplier but with all different upload dates. Overall there are over 1,000 records for 48 uniqur suppliers.
The field names are:
SupplierID, UploadDate, FeedFileName, RecordCount, FeedFileDate, RecordCount, and FeedLoaded.
So for each distinct SupplierID I'd like to grab the line item based on the max feedfiledate for each one - in other words I am trying to get the latest uploaded file information, how many records were in the file, and when it was uploaded... I've tried multiple group by and max clauses but there is something I am missing...
Thanks Much in advance
View 4 Replies
View Related
Apr 12, 2007
Hello,
I'm hoping someone can point me in the right path with a design issue I'm running into. I'm somewhat new with database design and SQL server, so bear with me.
I'm creating a sort of project management system, and I've got a main PROJECT table, and then a STUDENT_PROJECT_PARTICIPANT table, with foreign keys to STUDENT and PROJECT tables. Each has an 'institute' field. What I want to do is to be able to report on the number of students involved in each project, the number of unique students involved in project with a particular institute, and then the number of unique students involved in all projects. I'd also like to be able to report the number of unique students involved in a custom-picked list of projects (this seems the least feasible). The trick is that I need to report this statics within a specific date range. My original idea was to just save statistics of the numbers of students involved in a project every time it is saved, but from there I'm not sure how I can find out the unique count for all projects, since I don't have the date that each student was saved. That's my basic problem. Let me know if you need any clarification.
If anyone has done something similar or has any advice for me on this, please let me know!
Thanks!
Daryl
View 6 Replies
View Related
Feb 21, 2006
> This is a common problem with some solution[color=blue]>>[/color]/************************************************** *********************************** Problem:* Determine the Duplicated Records in a table using single SELECT.** We shall be using Northwind database, add some duplicate records.** Here we want to know if 2 columns (CompanyName,* PHone) are duplicated in a table.*** ShipperID CompanyName Phone* ----------- ------------------------- ------------------* 1 Speedy Express (503) 555-9831* 2 United Package (503) 555-3199* 3 Federal Shipping (503) 555-9931* 4 Federal Shipping (503) 555-9931* 5 Speedy Express (503) 555-9831* 6 Federal Shipping (503) 555-9931***************************************************** **/================================================== SOLUTION 1: Gives me the IDs that are duplicated.================================================== SELECTShipperID, CompanyName, PhoneFROMSHIPPERSWHEREEXISTS (SELECTNULLFROMSHIPPERS bWHEREb.CompanyName = SHIPPERS.CompanyNameAND b.Phone = SHIPPERS.PhoneGROUP BYb.CompanyName, b.PhoneHAVINGSHIPPERS.ShipperID < MAX( b.ShipperID ))/* ********************* Output results********************/ShipperID CompanyName Phone----------- ----------------------------------------------------------------1 Speedy Express (503) 555-98313 Federal Shipping (503) 555-99314 Federal Shipping (503) 555-9931(3 row(s) affected)================================================== ===========SOLUTION 2: Gives me the data which are duplicate butnot the IDs================================================== ===========SELECTCompanyName, PhoneFROMSHIPPERSGROUP BYCompanyName, PhoneHAVINGCOUNT(*) > 1/* ********************* Output results********************/CompanyName Phone---------------------------------------- ------------------------Speedy Express (503) 555-9831Federal Shipping (503) 555-9931(2 row(s) affected)
View 1 Replies
View Related
Jul 11, 2006
Hi all,We have an app that uses SQL 2000. I am trying to track when a code field(selcode) is changed on an order which then causes a status field (status)to change. I tried a trigger but the app may use 2 different updatestatements to change these fields depending on what the user does. When thetrigger fires (on update to selcode), the status field has already beenchanged. So my trigger to record the changes from inserted and deleted donot get the true 'before' value of the status field.The app does use a log table that tracks these changes. The problem I amhaving is that 2 records are created, one for the change to selcode andanother for the change to status.I am looking for help with a script to combine the existence of these 2 logrecords into 1 unique record or occurance that I can track.example:ordlog: table that logs order changesordernr: order numbervarname: name of field being changedold_value: contents of field before changenew_value: contents of field after changesyscreated: date/time of log entrySELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'selcode' and ordernr = '10580'SELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'status' and ordernr = '10580' and old_value = 'A' andnew_value = 'O'So I need a way to combine these 2 log entries into a unique occurance. Theordernr and syscreated could be used to link records. syscreated alwaysappears to be the same for the 2 log entries down to the second. Selcodecan change from NULL to a number of different values or back to NULL.Statusis either 'A' for approved or 'O' for open. An order can have many logentries during its life. The selcode may be changed several times for thesame order.Ideally, I would like a result that links 2 log entries and shows the statuschanged from 'A' to 'O' when selcode changed.Thanks for your time.
View 1 Replies
View Related
May 22, 2008
hilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
View 5 Replies
View Related
Sep 27, 2005
Hello, everyone:
I have two tables with some duplicated records like,
ZZZTest:
C_IDC1C2C3
10AAA
20BBB
30AAA
40BBB
ZZZTestTable:
D_IDC11C22C33
1AAA
2AAA
3BBB
4BBB
5AAA
6AAA
7BBB
I wand to get the unique records by SELECT / JOIN statement. Now I used a query,
SELECT * FROM ZZZTest t
INNER JOIN ZZZTestTable tt
ON t.Col1=tt.Col11 AND t.Col2=tt.Col22 AND t.Col3 = tt.Col33
and got the records like,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA1AAA
10AAA2AAA
30AAA2AAA
20BBB3BBB
40BBB3BBB
20BBB4BBB
40BBB4BBB
10AAA5AAA
30AAA5AAA
10AAA6AAA
30AAA6AAA
20BBB7BBB
40BBB7BBB
What I am expecting is,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA2AAA
20BBB3BBB
40BBB4BBB
Any suggestion will be great appreciated.
Thanks
ZYT
View 3 Replies
View Related
Oct 19, 2013
I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required.
SCENARIO :
I am required to query from 2 tables for those unique record that meets both conditions below:-
1. Status is 1 @ max (trans_id), paychnl = CC
2. Status is 2 @ max (trans_id), paychnl = A or B
FYR, 2 tables and respective columns to query are as below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE
table CHFF --> col STATUS,PAYCHNL
FYI, status refers to the paychnl method status:-
==> 1 means the current paychnl method
==> 2 means the previous paychnl method
paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE.
Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID.
Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE
table CHFF --> col STATUS,PAYCHNL
SELECT DISTINCT PTFF.TRANSCODE,PTFF.ID,PTFF.TRANS_ID,PTFF.TRANSDATE,PTFF.EFFDATE, CHFF.STATUS,CHFF.PAYCHNL
FROM DBO.PTFF PTFF
JOIN DBO.CHFF CHFF
ON CHFF.ID = PTFF.ID
WHERE
PTFF.TRANSDATE BETWEEN 130501 AND 130831
AND PTFF.TRANSCODE='T522'
AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B')))
However, the script above returns :-
1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2,
2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B
3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID
Samples of the result:-
IDSTATUSTRANS_IDPAYCHNLTRANSDATEEFFDATETRANSCODE
51881712CC13082920130920T522
9361164CC13081620140813T522
78531153CC13082020130814T522
8949151CC13081220130801T522
8949251B13081220130801T522
19081455CC13051620131129T522
19082455A13051620131129T522
19081409CC11101920111129T522
19082409A11101920111129T522
19081404CC11092920111129T522
19082404B11092920111129T522
View 7 Replies
View Related
May 10, 2007
ms sql server
data:
idno Email Category
1 a@a.com c
2 b@b.com c
3 b@b.com c
4 b@b.com c
only unique email address record are requierd.
1 a@a.com c
2 b@b.com c
what could be the query for such requirements.
View 5 Replies
View Related
Nov 15, 2015
Lets say I have a table - tblProducts
View 4 Replies
View Related
Feb 8, 2007
Our users use Report Builder for writing ad-hoc queries. They have run into a problem where Report Builder is returning only distinct records is the entities unique identifier is not added to the report.
Example:
TransID Type TransQuantity
1 CS 4
2 CS 4
3 CP 2
4 CN 3
Adding the above fields to the report builder canvas will return 4 rows. However, if I were to add only Type and TransQuantity to the canvas as such, Report Builder will only return 3 rows:
Type TransQuantity
CS 4
CP 2
CN 3
Is there any way to have Report Builder return the actual number of records? Our users often will export the results from Report Builder into Excel to slice and dice the data, and with the functionality the Report Builder has right now, incorrect values will be certain.
Thanks,
Taurkon
View 3 Replies
View Related
Jan 20, 2008
Hi Respected,
I am working in .net, and according my client he want to see all record from table uniquely, and does not want to give order also.
for doing this i did use DISTINCT, but my problem is when i used DISTINCT the query return sorted records that is why, i need guide which will help to make DISTINCT and physical stored record from the table.
thanks.
bay
regards Rajat.
View 1 Replies
View Related
Aug 10, 2015
I have a very large CSV file containing name-and-address information which I am reading in a Visual Basic project using the Microsoft.JetOLEDB.4.0 provider.
The key field on which the CSV file is to be filtered is the PostCode field. This is a UK-format PostCode "XXnn Nxx" where "XX" is one or more letters denoting a geographical area within the UK and "nn" is one or more characters (starting with at least one numeric digit) which when combined with the area code denotes a specific district within the geographical area. My aim is to identify all the unique UK postal districts held within my address CSV file.
Because I do not know how to use SQL to filter on the partial contents of a database field I am presently reduced to extracting unique full PostCodes using "SELECT DISTINCT PostCode,City,County FROM [ADDRESSES.csv]" into a DataTable object, then sequentially reading that DataTable using the operation of a dictionary object to identify unique PostCode areas, to finally construct the DataTable I need.
Is it possible in SQL to select records where the value of a varying number of characters before a space character in a given (PostCode) field is unique?
View 12 Replies
View Related
May 30, 2008
Hi
I need to select unique records from a Table. I'm using Distinct Keyword for this purpose. But the result set is showing distinct records in sorted order. I do'nt want to sort records. I need the order in which they are created in table.
Please suggest me the solution for this problem.
Thanks
Regards
Avinash Vyas
View 4 Replies
View Related
Jan 17, 2007
How to insert 100 record at a time by explicit inserting of identity column i.e.., by setting identity column to false
View 4 Replies
View Related
