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 is
the Sale is made up of three tables: Sale, SaleEquipment, SaleParts.
Each sale can have multiple pieces of equipment with corresponding
parts, or parts without equipment. My problem in copying is when I go
to copy the parts, how do I get the NEW sale equipment ids updated
correctly on their corresponding parts?

I can provide more information if necessary.
Thank you!!


View 6 Replies


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

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


View 6 Replies View Related

Transact SQL :: Query Is Trying To Insert Duplicate Record?

May 18, 2015

My query wants to insert new supplier if there is any. And it should ignore, if the supplier is already present in the table. But it is trying to insert the supplier which is already available. For example, I have PART A with 2 suppliers ABC and DEF. I am getting data from third party for PART A with supplier DEF. As per the condition, it should ignore the record because DEF is already available . But my query is trying to insert supplier DEF and following that, I am getting primary constraint error.

-- Inserting new preferred supplier into R5CATALOGUE



View 5 Replies View Related

Duplicate Last Record When Using SqlDataAdapter.Update For Insert Command

Jun 24, 2007

I'm getting duplicate records for the last record in the datatable. No matter how much or how little my datatable contains row records, it always duplicate the last one for some reason. Is there something wrong with my code below? EXAMID pulling from another stored procedure, which is outputed back to a variable.
---Data Access Layer---- If dt.Rows.Count > 0 Then
'INSERT EXAM ROSTERInsertComm = New SqlCommandsqladapter = New SqlDataAdapterInsertComm = New SqlClient.SqlCommand("ExamOfficers_AddOfficerSpecificExamRoster", conndb)InsertComm.CommandType = CommandType.StoredProcedure
sqladapter.InsertCommand = InsertCommInsertComm.Parameters.Add("@examid", SqlDbType.Int)InsertComm.Parameters("@examid").Value = examidInsertComm.Parameters.Add("@officerid", SqlDbType.Int, 12, "Officer_UID")InsertComm.Parameters.Add("@reimburse", SqlDbType.Bit, 12, "ReimburseToDb")InsertComm.Parameters.Add("@posttest", SqlDbType.Int, 12, "Post_Test")InsertComm.Parameters.Add("@pqcdate", SqlDbType.DateTime, 12, "pqc_date")InsertComm.Parameters.Add("@pqcscore", SqlDbType.Int, 12, "pqc_score")
sqladapter.UpdateBatchSize = 100InsertComm.UpdatedRowSource = UpdateRowSource.Nonesqladapter.Update(dt)
End If
----Stored Procedure----
ALTER PROCEDURE [dbo].[ExamOfficers_AddOfficerSpecificExamRoster]
@ExamID as int,@OfficerID as int,@reimburse as bit=NULL,@posttest as int=NULL,@pqcdate as datetime=NULL,@pqcscore as int=NULL
Insert Into Exam_Officers(EXAM_UID,Officer_UID,reimburse,post_test,pqc_date,pqc_score)values(@ExamID,@OfficerID,@reimburse,@posttest,@pqcdate,@pqcscore)

View 1 Replies View Related

Reporting Services :: Group And Sum Items / Sub-items Into One Record

Apr 10, 2015

I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).

I have a table of items (i.e. products).  In some cases, items can be components of another item (called "Kits").  In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items".  In our Items table, we have a field called "Parent_Item_Id".  Records for Child Items contain the Item Id of the parent.  So a sample of my database would be the following:

ItemId | Parent_Item_Id | Name | QuantityAvailable
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100

Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.

So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item.  So an example of the report would be the following:

Name | Available Qty
Kit A | 15
Kit B | 25
Item 4 | 100

How I can setup my report to group properly?

View 6 Replies View Related

Lookup && Update Record && Insert Record

Mar 26, 2008

Hi All,

I am trying to create package something like that..

1- New Customer table as OleDB source component
2- Lookup component - checks customer id with Dimension_Customer table
3- And if same customer exist : I have to update couple fields on Dimension_Customer table
4- if it does not exist then I have insert those records to Dimension_Customer table

I am able to move error output from lookup to Dimension_Customer table using oledb destination
but How can I update the existing ones?
I have tried to use oledb command but somehow it didnt work
my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)

I will be appreciated if you can help me...

View 3 Replies View Related

Duplicate Record

Jan 30, 2006

Dear All,

I need to identify duplicate records in a table. TableA [ id, firstname, surname] Id 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

Im 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

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')
                        ROLLBACK transaction
                        RAISERROR('Error: step: %s.  rollback is done.', 16, 1, @step)
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)


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,

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?


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.................


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


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.


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



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:

,CONVERT(CHAR(8),r.[submitDate],10)AS submitDate
,CONVERT(CHAR(8),r.[dueDate],10)AS dueDate
,CONVERT(CHAR(8),r.[revisedDueDate],10) AS revisedDueDate
,u.lastName + ', ' + u.firstName AS submittedBy

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

Reporting Services :: Add Horizontal Line After Each Record?

Apr 21, 2015

I have 10 records seperated by ID. I m using List in SSRS to show data on report. I need to add horizontal line after first record, then after second record till 10 th record. How do we add this line?

View 4 Replies View Related

Duplicate Record Inserted. Weird.........

Jul 5, 2007

1 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
2 Dim sqlStr As String
3 Dim sqlStr2 As String
4 Dim myConnection As MySqlConnection = Nothing
5 Dim myCommand As MySqlCommand = Nothing
6 Dim myConnection2 As MySqlConnection = Nothing
7 Dim myCommand2 As MySqlCommand = Nothing
8 Dim myReader As MySqlDataReader = Nothing
9 Dim IC As String
11 IC = txtIC1.Text + "-" + txtIC2.Text + "-" + txtIC3.Text
14 Try
15 sqlStr2 = "SELECT * FROM User WHERE uLogin='" & txtUserName.Text.Trim() & "'"
17 ' Connection
18 myConnection2 = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString())
19 myConnection2.Open()
20 ' Command
21 myCommand2 = New MySqlCommand(sqlStr2, myConnection2)
22 ' Reader
23 myReader = myCommand2.ExecuteReader()
24 Catch ex As Exception
25 ' Exception Error Here
26 Response.Write(Err.Number & " - " & Err.Description)
28 End Try
29 ' Checking
30 If myReader.Read() Then
33 Label2.Text = "Username already exist. Please choose another username"
34 Label3.Text = "*"
36 Else
38 Try
41 sqlStr = "INSERT INTO userapplication(uaName,uaIC,) VALUE (?uName,?uIC )"
45 ' Connection
46 myConnection = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString())
47 myConnection.Open()
48 'Command
49 myCommand = New MySqlCommand(sqlStr, myConnection)
51 myCommand.Parameters.AddWithValue("?uName", txtName.Text)
52 myCommand.Parameters.AddWithValue("?uIC", IC)
55 myCommand.ExecuteNonQuery()
56 myConnection.Close()
57 Response.Redirect("Register.aspx", False)
59 Catch ex As Exception
60 ' Exception Error Here
61 Response.Write(Err.Number & " - " & Err.Description)
62 Finally
63 ' Clean Up
64 If Not IsNothing(myCommand) Then
65 myCommand.Dispose()
66 End If
67 '
68 If Not IsNothing(myConnection) Then
69 If myConnection.State = Data.ConnectionState.Open Then myConnection.Close()
70 myConnection.Dispose()
71 End If
72 End Try
75 End If
77 End Sub
 above is my code for the user registration page.the code that i bold,which with number 55,56 and 57,are where the problem occur.
when it run,it run 55, then 57,then back to 55, then 57 again
means that my db hav duplicate record being insert
anyone know how to solve this problem?

View 2 Replies View Related

Simple Duplicate Record Question

Feb 27, 2002

I have a table with 2 columns, col1 is unique, col2 is not.
col1 is numeric col2 is varchar.
Here is the problem,
col2 will have duplicate values, I need the largest numeric value from col1 with unique value from col2.
Thanx for any help.

View 1 Replies View Related

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?


View 1 Replies View Related

Help Selecting Duplicate Record Details

Oct 10, 2006

I have the following query I am using to identify duplicate records in one of my database tables:


SELECT memberID,
COUNT(memberID) AS NumOccurrences
FROM ChapterMembers
HAVING ( COUNT(memberID) > 1 )

Executing the above proc returns 4079 records...

Now, I would also like to know the ChapterID for each member with a duplicate record. ChapterID is also stored in the ChapterMembers Table...

I tried running the following procedure:


SELECT memberID,
COUNT(memberID) AS NumOccurrences, chapterID
FROM ChapterMembers
GROUP BY memberID, chapterID
HAVING ( COUNT(memberID) > 1 )

But zero results are returned ...

The ultimate goal here is to identify duplicate records where one of their chapterID's = '81017' and to delete that record from the database.

Anyone have any ideas what I am doing wrong? Also, any suggestions for removing the records would be appreciated.



View 3 Replies View Related

Filtering Multiple Duplicate Record

May 2, 2014

I am new in SQL programming world, following is query that i had created

select interfaces.nodeid as 'Node Id',
nodes.caption as 'Node Name',
netflowsources.Lasttime as NetflowLastTime

from interfaces inner join nodes on interfaces.nodeid = nodes.nodeid
inner join netflowsources on interfaces.interfaceid = netflowsources.interfaceid

where netflowsources.LastTime NOT LIKE GETDATE()

which is from that query i get a return successfully, but i just noticed that, for column nodeid was showed me multiple duplicated records, for example

nodeid value = a,a,a,b,b,c,c,c,c,d,d,d

But what i expected was to get a return without any duplicate record within it. and i also have tried using "distinct" command, but that only impacted on "a" value, but others value not change at all.

View 9 Replies View Related

Deleting The Duplicate Record From Table

Mar 26, 2008

Hi ,
i am using sql server 2005.
i have one table where i need to find records that have same citycode and hospitalcode and doctorcode then delete the record keeping only one record of them
my problem is table structure have idendtity column which is unique.
that is m table structure is something like

recid citycode hospcode doctorcode otherdesp
1 0001 hp001 d0001 ...
2 0002 hp002 d0002 ...
3 0001 hp001 d0001 ...
4 0002 hp002 d0002 ...

please suggest

thank you

View 2 Replies View Related

Fetch Returning Duplicate Last Record

Sep 13, 2006

Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!

Thanks in advance, Larry

ALTER FUNCTION dbo.TestFoodDisLikes


@ResidentID int


RETURNS varchar(250)



DECLARE @RDLike varchar(50)

DECLARE @RDLikeList varchar(250)


SELECT @RDLikeList = ''





FROM tblFoodDislikes

WHERE (ResidentID = @ResidentID) AND (Breakfast = 'True')

OPEN RDLike_cursor



SELECT @RDLikeList = @RDLike





SELECT @RDLikeList = @RDLikeList + ', ' + @RDLike


CLOSE RDLike_cursor





View 5 Replies View Related

Transact SQL :: Duplicate Record Search

Jul 23, 2015

I have been given a task to locate duplicate and report duplicate records and am trying to determine the best way to do this with databases that have 1 million records plus.

Say I have a table with 20 columns, I need to check to see if 3 of 10 specific columns match.

So if 2 columns are the same its no problem however if 3 or more match, they are considered duplicate.

View 15 Replies View Related

How To Extract Duplicate Record Using Ssis

Nov 16, 2006

ex. from source the file you want to split the record into two, the one with a clean record and the other one with duplicate records

View 3 Replies View Related

Copy A Record Using A Sp

Oct 6, 2005

In a sp I want to select a record from my sql table and copy it to a new record in the same table, changing the value of one of the fields. The value of this field will come from my web page. Is this possible ?

View 3 Replies View Related

Opinion On Preventing Duplicate Record Insertion

Oct 26, 2007

Hi, i need an opinion on prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this  article , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005
I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit....thanks ..

View 2 Replies View Related

Duplicate Record Inserted With Stored Procedure

Feb 4, 2008

I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000.  Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO  Sub AddUserLog(ByVal Username As String)
Dim SqlText As String
Dim cmd As SqlCommand
Dim strIPAddress As String

'Get the users IP address
strIPAddress = Request.UserHostAddress

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
SqlText = "sp_AddUserLog"
cmd = New SqlCommand(SqlText)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con

cmd.Parameters.Add("@Username", SqlDbType.VarChar, 100).Value = Username
cmd.Parameters.Add("@IP", SqlDbType.VarChar, 100).Value = strIPAddress

End Try

End Sub 

View 7 Replies View Related

Copyrights 2005-15, All rights reserved