Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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


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

Maria


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
How To Create An Copy Of A Certain Record Except One Specific Column That Must Be Different && Insert The New Record In The Table
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 Replies !   View Related
Duplicate Last Record When Using SqlDataAdapter.Update For Insert Command
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")
conndb.Open()
sqladapter.UpdateBatchSize = 100InsertComm.UpdatedRowSource = UpdateRowSource.Nonesqladapter.Update(dt)
InsertComm.ExecuteNonQuery()InsertComm.Dispose()
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
ASBEGIN SET NOCOUNT ON;
Insert Into Exam_Officers(EXAM_UID,Officer_UID,reimburse,post_test,pqc_date,pqc_score)values(@ExamID,@OfficerID,@reimburse,@posttest,@pqcdate,@pqcscore)
END

View Replies !   View Related
Lookup && Update Record && Insert Record
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 Replies !   View Related
Duplicate Record
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 Replies !   View Related
Duplicate Record
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 Replies !   View Related
Duplicate Inserted Record
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 Replies !   View Related
Duplicate Record Trigger
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 Replies !   View Related
Duplicate Record Problem
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 Replies !   View Related
Duplicate Record Question
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 Replies !   View Related
Deleting Duplicate Record
hi to all,

How to delete duplicate record in the recordset?

Thanks...

View Replies !   View Related
Update A Duplicate Record
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 Replies !   View Related
Extracting Duplicate Record On The Same Id
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 Replies !   View Related
Remove Duplicate Record
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 Replies !   View Related
Duplicate Record Problem
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 Replies !   View Related
Eliminate Duplicate Record(s)?
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 Replies !   View Related
Delete Duplicate Record
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 Replies !   View Related
Duplicate Record Inserted. Weird.........
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
10
11 IC = txtIC1.Text + "-" + txtIC2.Text + "-" + txtIC3.Text
12
13
14 Try
15 sqlStr2 = "SELECT * FROM User WHERE uLogin='" & txtUserName.Text.Trim() & "'"
16
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)
27
28 End Try
29 ' Checking
30 If myReader.Read() Then
31
32
33 Label2.Text = "Username already exist. Please choose another username"
34 Label3.Text = "*"
35
36 Else
37
38 Try
39
40
41 sqlStr = "INSERT INTO userapplication(uaName,uaIC,) VALUE (?uName,?uIC )"
42
43
44
45 ' Connection
46 myConnection = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString())
47 myConnection.Open()
48 'Command
49 myCommand = New MySqlCommand(sqlStr, myConnection)
50
51 myCommand.Parameters.AddWithValue("?uName", txtName.Text)
52 myCommand.Parameters.AddWithValue("?uIC", IC)
53
54
55 myCommand.ExecuteNonQuery()
56 myConnection.Close()
57 Response.Redirect("Register.aspx", False)
58
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
73
74
75 End If
76
77 End Sub
78
79
 
 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 Replies !   View Related
Q:Duplicate A Record Except One Or Two Specific Fields Using SQL
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 Replies !   View Related
Simple Duplicate Record Question
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 Replies !   View Related
Help Selecting Duplicate Record Details
I have the following query I am using to identify duplicate records in one of my database tables:


Code:


SELECT memberID,
COUNT(memberID) AS NumOccurrences
FROM ChapterMembers
GROUP BY memberID
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:


Code:

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.

Thanks,

Jandrews

View Replies !   View Related
Deleting The Duplicate Record From Table
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 Replies !   View Related
How To Extract Duplicate Record Using Ssis
 

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 Replies !   View Related
Fetch Returning Duplicate Last Record
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)

AS

BEGIN

DECLARE @RDLike varchar(50)

DECLARE @RDLikeList varchar(250)

BEGIN

SELECT @RDLikeList = ''

DECLARE RDLike_cursor CURSOR

LOCAL SCROLL STATIC

FOR

SELECT FoodItem

FROM tblFoodDislikes

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

OPEN RDLike_cursor

FETCH NEXT FROM RDLike_cursor

INTO @RDLike

SELECT @RDLikeList = @RDLike

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM RDLike_cursor

INTO @RDLike

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

END

CLOSE RDLike_cursor

DEALLOCATE RDLike_cursor

END

RETURN @RDLikeList

END

View Replies !   View Related
Duplicate Record Inserted With Stored Procedure
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

Try
con.Open()
cmd.ExecuteNonQuery()
Finally
con.Close()
End Try

End Sub 

View Replies !   View Related
Opinion On Preventing Duplicate Record Insertion
Hi, i need an opinion on this...to 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 http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , 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 Replies !   View Related
Duplicate A Record Using Update Trigger Question
I am new to SQL and these forums, so please bear with me :)

My basic question is if I can create a update trigger that will pull info from another record in the same table if data in certain fields match the existing record.

An example:

The user creates a new record. If said user enters data in specified fields that matches data in the same fields in another record in the same table, can a update trigger be used to fill out the rest of this record with the data from the record that matches?

If you need more Info on my problem, ask and I will try to explain better. There may be a better way of doing this than using a trigger, but I am not sure. The fields that I would use to match the data would not be the primary key fields.

Thanks!

View Replies !   View Related
How Do I Select The Maximum Date For Each Record Having Duplicate ID
 

Hi All,
Here is my story,  how to change a column called Flag_Status based on the maximum Updated date. i.e. i want to make Flag_Status be 1 for the records which have maximum Updated_date (current record) and the rest to make it 0. for example accountID 1 has three records updated, but only one is current the rest are historical, thus i want the history record to be Falg_status 0 and the current record be 1. Note that Inserted_Date and Updated_Date are created using SSIS Derived column During loading the source table, it helps me when each record is inserted into the Data warehouse.
 
Here is My source table Name: Source_table,
 

CREATE TABLE dbo.Source_table

(

AccountID INT PRIMARY KEY,

Price int,

Address varchar(30),

added DATETIME DEFAULT GETDATE(),

edited DATETIME DEFAULT GETDATE(),

Flag_Status Int Default 1

)

GO
 
Here is the Fact_table
 

CREATE TABLE dbo.Fact_table

(

Fact_table_Key Int Identity (1, 1) NOT NULL,

AccountID INT,

Price INT,

Address varchar(30),

added DATETIME DEFAULT GETDATE(),

edited DATETIME DEFAULT GETDATE(),

editor VARCHAR(64),

Flag_Status Int Default 1,

Inserted_Date DATETIME DEFAULT GETDATE(),

Updated_Date DATETIME DEFAULT GETDATE()

)

GO
 
Source_table:
 
   AccountID      Price      Address            added              edited                 Flag_Status
  ----------------    ---------     -------------           -----------            -------------            ------------------
       1                10          xyz                 01-2006            01-2006                     1
       2                14          abc                 02-2006            02-2006                     1
       3                13          mno                03-2006            03-2006                     1
 
Here is the fact table, table name= Fact_table
 
  

Fact_table_Key        AccountID    Price  Address     added       edited        Flag_Status  Inserted_Date  Updated_Date
--------------------          -------------   ---------  ------------       -------         ---------       ----------------     ------------------    -------------------
1                                  1          10         xyz         01-2006        01-2006         1                05-2006          NULL
2                                  2          14         abc         02-2006        02-2006         1                05-2006          NULL
3                                  3          13         mno        03-2006        03-2006         1                05-2006          NULL
4                                  1          17         ght          01-2006        06-2006         1                NULL             08-2006
5                                  2          18         dmc        02-2006        07-2006         1                NULL             08-2006
6                                  3          20         kmc       03-2006         09-2006         1                NULL             10-2006
7                                  1          19         xyz        01-2006        11-2006          1                NULL             12-2006
8                                  2          19         klm        02-2006        01-2007          1                NULL             02-2007
9                                  3          21        pqr         03-2006         03-2007          1                NULL             04-2007
 
Here is what i am thinking: But it gives me Wrong Flag_Status.
 

UPDATE Fact_table

SET Flag_Status =


 CASE


WHEN (SELECT Max(Updated_Date) From Fact_table

WHERE AccountID IN (SELECT AccountID FROM Fact_table

Group By AccountID Having Count(AccountID)>1)) >

(SELECT Max(edited) From Source_table

WHERE Flag_Status = 1)

THEN 1


WHEN (SELECT AccountID From Source_table

Group By AccountID

Having Count(AccountID) =1) =

(SELECT AccountID From Fact_table

WHERE Updated_Date IS NULL)

THEN 1

ELSE 0

END

 

View Replies !   View Related
Query To Update 1 Record In A Duplicate Set Of Records
How do I update a record that has duplicates.  For example, I have 3612 orders some of these orders have multiple orderid's  I want to update the record for each of these orders that was added most recently.

View Replies !   View Related
To Display An Alert Message While Inserting A Duplicate Record
I am duplicating a record from my asp.net page in to the database. When i click on save I am getting the following error message
Violation of PRIMARY KEY constraint 'PK_clientinfo'. Cannot insert duplicate key in object 'clientinfo'. The statement has been terminated.
The above message i am getting since i have tried to duplicate the clientname field of my table which is set as the primary key.
What i want is instead of this message in the browser i need to display an alert saying "the clientname entered already exists" by checking the value from the database.
Here is my code. pls modify it to achieve the said problem
if(Page.IsValid==true)    {           conn.Open();     SqlCommand cmd = new SqlCommand("insert into clientinfo (client_name, address) values ('"+txtclientname.Text+"', '"+txtaddress.Text+"')", conn);     cmd.ExecuteNonQuery();     conn.Close();     BindData();     txtclear();      System.Web.HttpContext.Current.Response.Write("<script>alert('New Record Added!');</script>");     Response.Redirect("Clientinfo.aspx");    }

View Replies !   View Related
Need Help With Adding A Duplicate Record Count Column To Query
I am attempting to create a simple recordset that would return thenumber of duplicates that exist in a table with a single column. Forexample if I had a table like the following:ID Reference Amount1 123456 1.002 123456 2.003 123 1.00I would like to get the following result:ID Reference Amount RecCount1 123456 1.00 22 123456 2.00 23 123 1.00 1Please help!Thanks,Shawn

View Replies !   View Related
Slective Duplicate Record Delete/Identify Question
HI, I have a table similiar to this:

Division Type Section Location ModificationDate
------- ----- ------- -------- ---------------
3 4 2 Los Angeles 2/1/05
3 4 2 New York 2/4/05
4 5 1 Los Angeles 2/4/05
3 4 2 Seattle 2/7/05
4 5 1 Dallas 2/6/05
3 4 4 London 2/3/05

I need to remove duplicate records that have the same division,type,section pair by slected the most recent modification date and keeping the data in the rest of the columns. The results of what I want to do would look like:

Division Type Section Location ModificationDate
------- ----- ------- -------- ---------------
3 4 2 Seattle 2/7/05
3 4 4 London 2/3/05
4 5 1 Dallas 2/6/05

Does anyone have idea how I would do something like this? Thanks.

View Replies !   View Related
Copy A Record Using A Sp
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 Replies !   View Related
How Can I Get The Actual Line Number From Inside Flatfile Sources For Each Record?
hi all,

 

maybe this is not a serious problem, but I tried for days to come toi a solution without success.

My Problem:

I have have several flatfile sources I need to import into a sql-server 2005 DB.

It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow.

I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component  won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script.

 

How can I achieve my goal? Please help me...

Thanks in advance .. Bernd

View Replies !   View Related
SELECT Query Syntax To Display Only The Top Record For Duplicate Records
Good day!

I just can't figure out how I can display only the top record for the duplicate records in my table.

Example:

Table1
Code Date
01 10/1/05
01 10/2/05
01 10/3/05
02 9/9/05
02 9/9/05
02 9/10/05

My desired result would be:
Table1
Code Date
01 10/1/05
02 9/9/05

Thanks.

View Replies !   View Related
Copy A Record From One Table To Another?
Hello,

Is there a way of copying/moving a record from one table to another identical table on the same database using ADO.NET
I can obviously do it the long way (retrieving a record, then pushing it up using a second SQL command)
I was just wondering if there is a way to do it in one database hit using some kind of cool SQL function.

View Replies !   View Related
Would SQL Prohibt Copy Record
Good Day,

I have an SQL 2005 Server running with a table called
Active and a linked table called Recall.

In my .net APP I wrote a VB code that will copy an
existing record and create a duplicate with a new PK ID.

Now here is the problem, The copy record works perfect if
I create a new record and then copy that record. I can
press my copy button and it will make as many copies as
I want. When I try to copy a record that is pre-existing
from an import I did about 6 months ago it looks like it
works but the record is never copied.

My question come down to, Is their something within SQL that
would prohibt a record from being copied ?

If the answer is yes, Can anyone advise where to look to
change setting in SQL or what type of correction I should
pursue.

I am new to SQL and coming from a foxpro arena, Which is also
where the imported data came from..

Any advice would be great.

Thank You..

View Replies !   View Related
How To Pass The Sale Date Which It Is The Highier Sale In The Month To Subreport
Hi Expert,

I have a Main Report that have the following table:-



Sale Date
Sale Value

2007-03-01
S$1000.00

2007-03-02
S$130.00

2007-03-03
S$1500.00

2007-03-04
S$1009.00

2007-03-05
S$120.00

2007-03-06
S$1020.00

I need to get the Sale Date which having Highier Sale Value using the expression to pass it to a Sub-Report that display the detail Sale Order for that Day.

I can get the Highier Value using the MAX function (Max(Fields!Sale Value.value)) but how can i return the Sale Date?

I tring to avoid writing another SQL statement to solve the problem.  Is there anyone know how to solve this using the SQL 2005 Report Service function?

Thank you

View Replies !   View Related
How To Create A Copy Of A Specefic Record
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 Replies !   View Related
Copy A Record To A Table When Change
Hi,

I was wondering if there is a easy way to do this.
I got Table Customers, and when a row is changed, deleted, i want to copy the original row to a backup table, so i have the whole history.

tnx a lot,

Mike

View Replies !   View Related
Ways To Make This Work: Several Selectable Related Record For One Main Record.
Hey all!

 

Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.

To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.

On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.

 

I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.

 

Thanks in advance,

Johan

View Replies !   View Related
Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem
Hi,

 We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.

 If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.

Thanks,
Sarah

The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.

This is running on a Websphere Application Server v6.1.

Code snippet - getting the record thru JDBC:


DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();


//    wsCon.setAutoCommit(false); //have tried with and without this flag - same results

     Statements stmt = wsCon.createStatement();


String sql = "SELECT * FROM Person where personID = 12345";
     ResultSet rs = stmt.executeQuery(sql);


if(rs.next()){
System.out.println(rs.getString("lastName"));
}

if (rs != null){
     rs.close();
}
if (stmt != null) {

stmt.close();
}
if (wsCon != null) {

wsCon.close();
}

View Replies !   View Related
SSIS: Multi-Record File Extract With 9 Record Types
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
 
I have been able to create a sample file with two of my record types.
 
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
 
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
 
Thanks in advance for any help you might be able to provide.
 
 
 

View Replies !   View Related
Add Date To Record In SQL Server Each Time Record Is Added
Hi
 
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks

View Replies !   View Related
Delete Record Based On Existence Of Another Record In Same Table?
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray

View Replies !   View Related
Record Locking: Multiple Users Accessing The Same Record
I have read several discussions about SQL 7 having built-in record locking. I am assuming that this is only during the transaction process.

I have a problem with multiple users access the same record on a SQL table. We have these users accessing the SQL data with an Access 2000 DB Project form. When one person accesses the form to pull up a record, someone doing this at the same time will get an error window that asks the user to Save/Drop changes. Is there any way to LOCK DOWN a record until a user has finished making changes to it?

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved