Q:Duplicate A Record Except One Or Two Specific Fields Using SQL

Oct 30, 2000

Using Transact-SQL how can I copy all fields except one from one record to another? The field in question being the identity field. Since, this field cannot be duplicated a simple INSERT statement fails. How can I specify an exclusion list of fields?

-Sumit

View 1 Replies


ADVERTISEMENT

How To Create An Copy Of A Certain Record Except One Specific Column That Must Be Different && Insert The New Record In The Table

Sep 1, 2006

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

View 6 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View 6 Replies View Related

Update Fields With Searched First Date Record Fields

Jul 23, 2005

Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/

View 3 Replies View Related

Duplicate Data In Table For Specific Columns

Nov 27, 2015

I have an table with duplicate data. I need to delete the duplicate records based on the RequestId.

I want to delete the records based on the RequestID 1001, Delete the duplicates in 1002, 1003 .

DECLARE @table TABLE ([Employee_SID] [int] NOT NULL,
[CalculatedTotalSalesYear] [numeric](19, 2) NULL,
[CalculatedTotalSalesYearAnnualized] [numeric](19, 2) NULL,
[RequestID] [int] NOT NULL)

[Code] ....

Expected Result

select * from @table

View 4 Replies View Related

Check If DataSet Contains Specific Fields?

Mar 9, 2008

Hi,
I have two FormView, and I need to know if the DataSet contains specific fields to display it in the first FormView or in the Second FormView.
Is it possible?

View 6 Replies View Related

How To Search For Fields Containing A Specific Text String

May 26, 2006

Hi All,I'm familiar with the syntax that looks like this:SELECT * FROM Users WHERE Email LIKE '%aol%'Which would return all users that whose Email column contains "aol".However, if I wanted to do that sort of a search using a prepared statement, how would I do it?I can't simply doSELECT * FROM Users WHERE Email LIKE '%@MatchAgainst%'Can somebody clue me in?

View 1 Replies View Related

Update Specific Fields With An Import (DTS Package)

Dec 15, 2006

Hello all

I am trying to import an XLS to a table in my database. Sounds simple, but the import should be an UPDATE, not an INSERT. In other words, I want to transfer data from the XLS sheet, to specific field in my database.

Let me explain more clearly.

I have an XLS sheet with a manager ID and a employee ID. The table in the database is already populated with all employee ID's but the field manager ID is still empty. Every employee has a different manager.

The import should place the manager ID's in the fields where the employee ID is the same as in the XLS sheet.

If somebody could please help me out, thank you!

Best regards
Maarten

View 7 Replies View Related

Duplicate Records - Two Fields

Feb 13, 2014

I have a table that contains some duplicate records. The entire record is not duplicated, but there are two fields which if both are duplicated, result in what I call a duplicate record. I can see that I have these duplicates by doing the following:

SELECT DISTINCT ReferenceNumber, ShipToCompanyID
FROM tabMedia
WHERE (MediaTypeID = '1') AND (FinishDate > '2011-01-01')
GROUP BY ReferenceNumber, ShipToCompanyID
HAVING (COUNT(ReferenceNumber) > 1) AND (COUNT(ShipToCompanyID) > 1)
ORDER BY ReferenceNumber

but of course, this only returns two fields. I need the entire record returned where the duplicate exists...

View 2 Replies View Related

How To Get Specific Record From Database

Feb 26, 2008

 HI How to get 100 ,500 and 1000 or (any particular record) record fromDatabase .Shafqat Soomro     

View 3 Replies View Related

How To Select Specific Record.

Oct 24, 2007

Group_Code *****Station_nbr*****Beg_Eff_Date*****End_eff_date

00000002 D01G00733 1/1/2007 8/31/2007
00000002N D01G00733 4/1/2007 8/31/2007
00000002W D01G00733 1/1/2007 12/31/2007


For the report I just want to be able to pick up the first and the last line.

Using dbo.Station_group Table

Thank you for the help

View 14 Replies View Related

Duplicate Data For Unique Fields With Different Name?

Oct 25, 2013

I'm designing an app for stock keeping. In my DB, I have a field called "ItemSerialNo" and I made it unique(but not the table's primary key). On my front end, I have a text box for Item Serial No and a combo box loaded with the item brands and also a save button. I know that if I try to save a serial no already existing in my DB, the app won't allow me because of the unique property of the field named "ItemSerialNo". But I want to be able to save a serial no already existing in my DB but with a different brand name.

For example, I want to be able to save information like:

1. ITEM SERIAL NO = 12345
BRAND NAME = AA
2. ITEM SERIAL NO = 12345
BRAND NAME = BB.

View 2 Replies View Related

Setting Duplicate Fields To NULL

Mar 19, 2008



I have a result set stored in a temporary table variable. The fields need to have heirarchy when they finaly get displayed to the user. I need to replace the repeating fields per group with NULL and leave out only one set of unique records per group.

I want to do something like this- make the fields in red NULL while leaving out the ones in green.

{ Col.Group I } { Col. Group II }

Col1 Col2 Col3 Col4 Col5
------- ------- ------- ------- -------
ValA ValA2 ValA3 ValA40 ValA50 {
ValA ValA2 ValA3 ValA40 ValA50 Row. Group A
ValA ValA2 ValA3 ValA41 ValA51 }

ValB ValB2 ValB3 ValB40 ValA50 { Row. Group B
ValB ValB2 ValB3 ValB41 ValA51 }



I was able to achieve this by using @@ROWCOUNT with in a while loop by performing one UPDATE at a time (SET ROWCOUNT = 1). But i have to run Count(*) each time I loop through the update query to figure out the remaining number of rows I have per group as I need to leave out one untouched. But the Count function is taking too much time and I'm having performace issues.

Any idea how I might be able to do this in my stored proc. efficiently?

Thanks,

Samtadsh

View 8 Replies View Related

Duplicate Record

Jan 30, 2006

Dear All,

I need to identify duplicate records in a table. TableA [ id, firstname, surname] I’d like to see records that may be duplicates, meaning both firstname and surname are the same and would like to know how many times they appear in the table

I’m not sure how to write this query, can someone help? Thanks in advance!

View 3 Replies View Related

Duplicate Record

Jan 14, 2007

Hi guys how do you hide duplicate records, how would I do a select statement for that

In (SELECT [AccountNo] FROM [2006_CheckHistory] As Tmp GROUP BY [AccountNo] HAVING Count(*)>1 )

I have about had it with this database I have been asked to make a report out of

View 14 Replies View Related

Constraint/identity Which Allows Duplicate Null Fields

Jan 4, 2007

hi,
I've done Googling and forum hunting but haven't had success finding a simple answer... My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question and also identity doesn't seem to fit since I'm not using autofill for this particular field. Is there some other way doing this on Sql Server 2005?
 
Thank you.

View 7 Replies View Related

Find Duplicate Records Based On Certain Fields

Jul 28, 2014

How can I pull out duplicate records based on certain fields?

Table called Bank

I want to pull out records that have duplicate inv_no, cus_no, amount,ordernum

Not all the fields are the same in each record. But I want the records that have these fields that are the same.

View 1 Replies View Related

Want To Query One Specific Record From Database Table

Feb 27, 2008

I have two tables in my database: order_id with fields order (text) and comp_ID (int) and another table called customers with comp_ID (int) and company name (text) and other company information fields. The link between the two tables is the comp_ID. With every order that's made the company that made the order is stored with it in the order_id table.  If I type in the order id (text), I want to be able to use the order id to search the order_id table and find out what the comp_ID of the company that made that order is. Then use that comp_ID to pull up the record of company information from the customers table with the same comp_ID. Is there some way to do this in one query? Or how do I accomplish this?  

View 4 Replies View Related

T-SQL (SS2K8) :: CTE Query To Find Specific Record?

Sep 18, 2015

I need to build a CTE query to find for the same Cabstamp (document) where i have different Origin.

I know that i can build this with a correlated subquery, but i´am curious about using CTE.

I post sample create Script:

create table #temp (Cabstamp varchar(10), account varchar(10),document varchar(15), origin varchar(2), debit numeric(10,2), credit numeric(10,2), datalc datetime)

insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc)
select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110'
union
select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110'
union
select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110'
union

[code]....

View 5 Replies View Related

Passing A Specific Cursor Record To A Function

Jul 20, 2005

Hello,Is it possible? Can I select a specific record of the cursor to besent to a seperate function to do all the computations etc.?Regards,VS

View 5 Replies View Related

Create Output File In A Specific Layout - Extra Spaces Between Fields

Mar 17, 2014

I'm trying to create an output file in a specific layout. For some reason my output file is adding an extra 10 spaces between the Account Number and the Check Number in the statement below. The rest of the output file looks fine. Where the extra 10 spaces are coming from? I need 1 Filler Space between these fields.

SELECT DISTINCT
CASE p.PaymentMethodID WHEN 10 THEN 'I' WHEN 60 THEN 'V' WHEN 50 THEN 'S' ELSE 'I' END
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 20 - LEN(ba.AccountNumber))+ CONVERT(CHAR(20), ba.AccountNumber))
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 18 - LEN(p.CheckNumber)) + CONVERT(VARCHAR(18), p.CheckNumber))

[Code] .....

View 2 Replies View Related

Duplicate Record Trigger

Nov 24, 2006

This is part of my trigger on table T1. I am trying to check if the records inserted to T1 is available in myDB.dbo.myTable or not (destination table). If it is available rollback T1. It does not do that although I insert the same records twice.
 
            -- duplicate record check
            SET @step = 'Duplicate record'
            IF EXISTS (   
                        SELECT     i.myID, i.Type
                        FROM         INSERTED i INNER JOIN
                                              myDB.dbo.myTable c ON i.myID = c.myID
                        GROUP BY i.myID, i.Type
                        HAVING      (COUNT(*) > 1) AND (i.Type = 'In')
            )
            BEGIN
                        ROLLBACK transaction
                        RAISERROR('Error: step: %s.  rollback is done.', 16, 1, @step)
                        Return
            END
           
What is problem?
 

View 1 Replies View Related

Duplicate Inserted Record

Nov 14, 2007

Hi EverybodyThis Code duplicate the record in the database, can somebody help me understand why that happen. Thanks a LOT    CompanyName:    <asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>"    insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@CompanyName, @Phone)"    selectcommand="SELECT * FROM [items]">    <insertparameters>        <asp:controlparameter controlid="txtCompanyName" name="CompanyName" />        <asp:controlparameter controlid="txtPhone" name="Phone" />    </insertparameters></asp:sqldatasource> VBPartial Class Default2    Inherits System.Web.UI.Page    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click        SqlDataSource1.Insert()    End SubEnd Class ----------------------------------------------Yes is an Identity the Primary Key of the Table items   

View 2 Replies View Related

Duplicate Record Question

May 4, 2004

In order to check that a new users ID does not already exist in the database I thought it would be a good idea to put the Insert into a Try Catch statement so that I can test for the duplicate record exception and inform the user accordingly. I was also trying to avoid querying the data base before executing the Insert.

The problem is what to actually test for. When the code throws the exception it is a big long string . .

"Violation of PRIMARY KEY constraint 'PK_Users_2__51'. Cannot insert duplicate key in object 'Users'"

I just thought that there has to be something simplar to test for than comparing the exception to the above string.

Can anyone tell me of a better way of doing this ?

(by the way I am only using Web Matrix and MSDE in case it matters)

Mark

View 2 Replies View Related

Duplicate Record Problem

Nov 4, 2005

I am working on a web application that utilizes a sql server database.  One of the tables is a large text file that is imported through a DTS package from a Unix server.  For whatever reason, the Unix box dumps quite a few duplicate records in the nightly run and these are in turn pulled into the table.  I need to get rid of these duplicates, but can't seem to get a workable solution.  the query that is needed to get the records is:SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,  tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,  tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,  tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTimeFROM  tblAppointments CROSS JOIN               tblPTDEMO2WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)AND tblAppointments.Insurance1 = 'MED'AND tblAppointments.ApptTypeID <> 'MTG'AND tblAppointments.ApptTypeID <> 'PNV'AND DateDiff("dd", ApptDate, GetDate()) = 0Order By tblAppointments.ApptDateMy first thought was to try to get a Select DISTINCT to work, but couldn't figure out how to do this with the query.  My next thought was to try to set up constraints on the table, but, since there are duplicates, the DTS package fails.  I assume there is a way to set up the transformations in a way to get this to work, but I'm not enough of an expert with SQL Server to figure this out on my own.  I guess the other way to do this is to write some small script or application to do this, but I suspect there must be an easier way for those who know what they are doing.  Any help on this topic would be greatly appreciated.  Thanks.

View 5 Replies View Related

Remove Duplicate Record

Jul 27, 2000

i'm a newbie to sql , anyone can give me suggestions on how to
remove duplicate records in a table, a table also has primary key,
thanks

View 1 Replies View Related

Duplicate Record Problem

Apr 23, 2008

So I'm working on updating and normalizing an old database, and I have some duplicate records that I can't seem to get rid of. Every column is identical, right down to what is supposed to be the key. I can't right a delete query to just isolate one row, and I can't delete (or even udpate) any row in management studio. Any thoughts on how to remove the extra rows?

There is a field that's supposed to be unique, so I can write a simple query to get all of the problem rows. The only thing is that they come back in pairs.

View 2 Replies View Related

Deleting Duplicate Record

Nov 19, 2004

hi to all,

How to delete duplicate record in the recordset?

Thanks...

View 3 Replies View Related

Update A Duplicate Record

Mar 23, 2006

Hi everybody,

I have 2 fields in a table.

Table Name--- StudentDetail

Name Address

Saju Kerala
Balaji Bangalore
Raj Kumar Tamilnadu
Saju Kerala

I want to Update one of the duplicate row as I don't have any unique id column. So can anybody update one of the the duplicate record without using any id or altering any column.

I am waiting for your reply.................

Regards,

Saju S.V

View 1 Replies View Related

Extracting Duplicate Record On The Same Id

Jul 9, 2007

Hi everybody i need help on on a query on how i can extract this... with the following table below..

id pub
1 a
1 b
2 c
2 c

I need to extract only the id and pub where pub has more than one with the same id... in the case of the above the result would be

id pub

2 c
2 c

thanks

View 2 Replies View Related

Keep Only One Record From Duplicate Entry

Feb 18, 2014

How can i keep only one record from duplicate entry.

Example
COLUMN1COLUMN2COLUMN3COLUMN4
AAA121-12
AAA121-13
AAA121-14

Here i want to keep only the top record and want to delete other 2 rows.

View 4 Replies View Related

Delete Duplicate Record

Oct 23, 2006

Hi ,

How can i delete the duplicate record from a table

use Northwind
create table Emp (Ecode char(2), Ename char(10))
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A4','D')
Insert into Emp(Ecode, Ename) values('A4','D')

select * from emp order by Ecode


Thanks

ASM

View 7 Replies View Related

Eliminate Duplicate Record(s)?

Jun 22, 2007

Hey Again,

I've been making great progress but I've hit another road block which a newbie intern like myself can't surpass. What's worse is the fact that no one is in the office today! Maybe someone can point me in the right direction with this SQL:

SELECT
r.[requestID]
,r.[requserID]
,r.[departmentID]
,CONVERT(CHAR(8),r.[submitDate],10)AS submitDate
,CONVERT(CHAR(8),r.[dueDate],10)AS dueDate
,CONVERT(CHAR(8),r.[revisedDueDate],10) AS revisedDueDate
,r.[reqStatus]
,r.[completedDate]
,d.[departmentName]
,s.[statusName]
,u.lastName + ', ' + u.firstName AS submittedBy
,ra.userID

FROMtblUserDepartment ud
INNER JOIN tblRequest rON ud.departmentID = r.departmentID
INNER JOIN tblDepartment dON r.departmentID = d.departmentID
INNER JOIN tblStatus sON r.reqStatus = s.statusID
INNER JOIN tblUser uON r.requserID = u.userID
LEFT JOIN tblRequestAssignee ra ON r.requestID = ra.requestID
WHEREud.userID= @userID


This works great except for one thing. In tblRequestAssignee, you have 1 primary assignee and can have several other assignees (that are not primary). This is denoted by a bit field "isPrimaryAssignee" in tblRequestAssignee. When I run the query, I see every request I want to but it duplicates requests with more than one assignee. What I'm trying to do is make only the primaryAssignee display if there is one. If there's not, then null is displayed (which is already happening).

Like I said, the query is mostly working right except for this duplicate record that displays when there's 2 assignees. Any help would once again be greatly appreciated.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved