I have a table with many fields but there is a single field that I do not want duplicates. If I index this specific field preventing duplicates, the entire record does not append. (The field in question is not keyed).
Thanks
Bill Howard
without writing code in my application? Does SQL Server have stored procedure to do it?
Any help is appreciated.
Thanks.
without writing code in my application? Does SQL Server have stored procedure to do it?
Any help is appreciated.
Thanks.
How can I achieve the following...
I have a Membership No. field which comes from a bookings table, so multiple membership no. do exist. What I want to achieve is a list of membership No.s with no duplication. Sorry to be so dumb, but we all have to start somewhere.
Hi,
I'm struggling with this. I'd like to perform a joined query
from two or more tables and take the same field from several
rows of one table into seperate fields of a single row in a new table.
Like this:
table 1
uidunameuloc
1mehere
1methere
2youhere
2 youthere
table 2
uidulocuparam#uparamval
1here1a
1here2b
1here3c
1there1d
1there2e
1there3f
2here1g
2here2h
2here3i
2there1j
2there2k
2there3l
result in table 3
uidunameuloc uparval1 uparval2 uparval3
1mehere a b c
1methere d e f
2youhere g h i
2 youthere j k l
uparam# field in table 2 always the same sequence
Any ideas????
Thanks...
Hi,
I have several row records in which
name email
--------- ---------
name1 abc@abc.com
name2 abc@abc.com
name3 abc@abc.com
name4 123@123.com
name6 123@123.com
How should I write the query which can return records with unique email address with any name attached?
The expected record set will be
name email
--------- ---------
<any> abc@abc.com
<any> 123@123.com
I was thinking to select the distinct of email and just stuck at here. I encountered this problem few times and still cannot solve it. Any help will great appreciated. Thanks in advance.
Hi
I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this..
CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner
(
@Crime_No varchar(20),
@Unit_ID int
)
as
begin
DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int
DECLARE @Defect_ID varchar(100)
select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID
SELECT
TBL_TR_Person_Details.Person_ID,TBL_TR_Person_Details.Person_Name, dbo.TBL_TR_Person_Details.Address1,
dbo.TBL_TR_Person_Details.Address2, dbo.TBL_TR_Person_Details.City_Id, dbo.TBL_TR_Person_Details.State_Id,
dbo.TBL_TR_Person_Details.Nationality_id, dbo.TBL_TR_Person_Details.EMail, dbo.TBL_TR_Person_Details.Phone,
dbo.TBL_TR_Person_Details.zip, dbo.TBL_TR_Person_Details.sex, dbo.TBL_TR_Person_Details.D_O_B, dbo.TBL_TR_Person_Details.Age,
dbo.TBL_TR_Person_Details.Occupation_ID, dbo.TBL_TR_Person_Details.Person_Type,
TBL_TR_ACCIDENT_VEHICLE.Registration_Number,
TBL_TR_ACCIDENT_VEHICLE.Crime_No,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Type,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Vanoeuvre,
TBL_TR_ACCIDENT_VEHICLE.vehicle_Make,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Model,
TBL_TR_ACCIDENT_VEHICLE.Unit_ID,
TBL_TR_ACCIDENT_VEHICLE.RowID,
TBL_TR_ACCIDENT_VEHICLE.UserID,
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Color,
TBL_TR_ACCIDENT_VEHICLE.HP,
TBL_TR_ACCIDENT_VEHICLE.Seating_Capacity,
TBL_TR_ACCIDENT_VEHICLE.Class_Of_Vehicle,
TBL_TR_ACCIDENT_VEHICLE.Unladen_Weight,
TBL_TR_ACCIDENT_VEHICLE.Registered_Laden_Weight,
TBL_TR_ACCIDENT_VEHICLE.Skid_Length,
(select TBL_TR_Person_OutsideDetails.OutSide_state from
TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state],
(select TBL_TR_Person_OutsideDetails.OutSide_City from
TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where
TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City]
---here I faced the problem-
/*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/
(SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5))
FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE
WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number)
select @Defect_ID
FROM
tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE
where
tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID
AND
TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No
AND
TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner
end
GO
If I have a database table with the following columns:
ID
Other_ID
Description
And I want to join the two ID fields to one field in another table that contains the following fields:
ID
Name
How would i do that?
Here is some sample data and what I would like returned
TABLE1
ID Other_ID Description
row 1 1 2 Number1
row 2 3 1 Number2
TABLE2
ID Name
row 1 1 John
row 2 2 Bob
row 3 3 Bill
I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like:
John Bob Number1
The only way around it now is that I store Other_Name in Table1.
Thanks.
i have taple translatio witch have theses coloumn
ID,TypeID,Status,ComID,RecordID,Translator
in this table we assign a certain company with a certain typeID to a certain translator .
so next time when the translator log he goes to the company that he assigned to it when he log to the company page,
there is another company page n arabic that is transalted by the translators , in the arabic page there is a button when you can send this company to transaltion.
but a duplicate have been happen made because user send the same documnet to the translation table so many time so translator transalte the same company profile more than one time witch is not good.
we handle the duplicate of the send to translation button put still there is duplicate record in the database witch is more taht 3000 record
how ican to remove thsi record from the DB without make ant other erroe
HI
I have created a job to run a reporting services job which then named it in the job scheduler 354EEF12-404F-46BD-B54F-708B5027837F. I then renamed the job to Rpt ETL log.
However it I was surpised to see two emails come with reference to this report. It seems to have created another one with the long job names.
Is there any way to stop this as I would really like to name to schduled rpt jobs without it duplicating.
Many Thanks
Robert
Hi I haven't included DDL etc as this is theoretical at this time. I can rustle up an illustrative example if required.Following applies to disconnected environment. In general, most edits are broad (i.e. n fields affected) against a single record. However, there are certain circumstances where a single field will be edited against a deep (n) set of records. So business request:User needs to access and edit n records at a time (for arguments sake n is unlikely to be enourmous - say 50 max) but only editing one single field, and always the same field. The values for this edited field will differ for each record. Initially retrieving data for the app is no problem, nor is identifying those records that have been edited. What is the best means of updating the table? There are, to my mind, three ways of dealing with the latter senario - 1) Client calls the server n times editing a record at the time.2) Client creates a csv string and passes to sproc. Sproc parses string using some UDF split function (probably chucking into table variable) and updates using a single set set based operation.3) Client creates some flat file or other that is BULK inserted (or similar) by SQL Server. 3 - I think would only be an option if n was a very large number and or having the changes immediately reflected in the data is not priority.1 - would create a chatty app and presumably put the most load on the server. So I am left thinking 2 is (depending on circumstances) the best method. Is this fair? Are there any particular considerations gotchas I should be aware of? I know where to get hold of as many TSQL split functions as I could want so I'm not looking for code just opinions. OR - am I just plain wrong? Is there a better alternative or am I dismissing the other two methods prematurely? Thanks in advance
View 5 Replies View Related
Hi all,
My code below creates a trigger that fires whenever a change occurs to a 'myTable' row, but this is not what I want. I only want to log changes made to a single field called 'Charges', when that field is changed I want to log it, can anyone tell me how to modify my code to do this, thanks
Create Trigger dbo.myTrigger
ON dbo.[myTable]
FOR UPDATE
AS
Declare @now DATETIME
Set @now = getdate()
BEGIN TRY
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'BEFORE',Charges,@now, suser_sname()
FROM DELETED
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'AFTER',Charges,@now, suser_sname()
FROM INSERTED
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
Hello
I noticed a spelling mistake in the data in a column of several tables, I used the following syntax to alter the spelling:
UPDATE [dbo].[Prod_Cat] SET [ProdName]=N'merseyside' WHERE ProdName = 'mmserseyside'
The above code correctly updated the spelling error, but it also inserted a new row with the corrected data.
So I found myself with two Identical rows containing the corrected information. I had to manually delete the extra row. Because if I had put in a DELETE statement, I would have then lost both rows.
What do I need to do to prevent this happening next time. As I find that I need to update the names of some products, but I don't want to duplicate them.
Thanks
Lynn
Hello,
I have a table which consists of 27,000 of records. Among these records, there is one record which
is a duplication of another record.
Is there any way to track this record from the same table by the SQL statement ?
I have been advised to use the following statement but it does not help:
Select count(*) As Duplicate, columnname from tablename group by columnname
Scrolling 27,000 lines of records with bare eyes is very painful.
Any help is appreciated.
Cheers
Hi
I have a set of excel files that i need to export to sql2005 using ssis. Now the issue is that i have no idea about he data ie it may have duplication in the primary key column. If i export it as it is to sql server, it will cause me problems. Is there any way i can filter out the rows which have duplication in the primary key column?
Umer
Hi, I having a problem with my query...
I want to copy data from 4 different database to 1 database... but if the destination database have already the same Primary Key the copying stops/terminated and not copying others that is not yet in the destination...
I don't have knowledge in T-SQL like IF...ELSE
my database is SQL Server 2000 but i'm using SQL 2005 Express Management for the query...
What i'm doing is like this:
Use osa (Destination Database)
Go
DELETE FROM tblFaculty (*I'll delete first the datas to avoid duplication)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM cislucena.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM amapn.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM abe.dbo.tMasFaculty)
INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode)
(SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM aclc.dbo.tMasFaculty)
My problem is if the facultyID (PrimaryKey) which i'm copying is already on the destination which is osa, the copying stops/terminated regardless whether there is more to copy. On the 4 source database, there might data that other database also has. That's why the copying is terminated. All i want to do is to check first each FacultyID if it is already on the destination before copying it to avoid error or duplication of Primary Key so it won't terminate the copying.
How is this possible sir? Anyone care to help? Thanks in advance! More Power!
Best Regards
Hi All,
Scenario: De-duplication logic should pick one record from source and check with all the destination records and insert if not duplicate. Else raise error. There are average 8 to 10 lookup check for each logic path. Key fields used for de-duplication check are FirstName, LastName, DOB, Gender, SSN.
Issues:
· Since picking row by row and processing the performance is constrained.
· Since 8-10 comparison is done using lookup performance downstream. (Lookup is used without caching, if cashing is used the package is failing after sometimes as if memory is failing. Can we handle this memory problem?)
Please give some suggestion to improve the performance. Current performance is around 2500 records per hour, but there are 8 lac records in total to process.
I am looking for guidance on this issue. If someone can guide me on how to do it better it would help me a lot.
Thanks,
S Suresh
Hi I have a table named UserMaster having a column Sex for male and female.Sex MaleMaleFemaleFemaleFemale I want to replace all Male with Female and all Female with Male with a single query. Can any one please help me out
View 4 Replies View RelatedI have a table JOBCODE which contains a list of codes.
I want to insert these values into table VIEWS as a list separated by spaces.
E.G.
Table Jobcodes looks like this
code
1
2
3
4
5
6
And I want table Views to look like this:
field1
1 2 3 4 5 6
How do I go about this?
How do I reliably symmetrically encrypt a single node in an XML field in my SQL Server 2012 database? I know I cannot use SQL Server encryption to encrypt an XML field and I have been getting unpredictable results with a home grown solution.
View 9 Replies View RelatedIn SSRS I am trying to get a textbox value to hold text with a mixture of formatting, along the lines of "name (country)" where the "name" part is bold and the "(country)" is normally formated.
Can anyone help?
Thanks in advance.
I have table called email_address and have all the email ids in this table. using sql task, I am getting the ids and passing to string. This string variable is passed to the to field using expression.Since it is a string only the first email id is set in the to field and the rest is ignored. Can you tell me a way to parameterize all the email ids to the to field in this case.Email ids from Table -> To field..I would like to put all the email ids in the to fields at once and send only a single email!
View 10 Replies View Related
IE:
ID ContactID
1 4, 5, 6, 8
2 3,4,6
Someone coded their database like this. It is a SQL server table with these two fields.
How do I use SSIS to parse out that single field?
Hello, everyone. I am having problems catching a data duplication issue. I hope I can get an answer in this forum. If not, I would appreciate it if someone can direct me to the right forum.
I am working on a vs2005 app which is connected to a sql2005 db. Precisely, I am working on a registration form. Users go to the registration page, enter the data, ie. name, address, email, etc. and submit to register to the site.
The INSERT query works like a charm. The only problem is that I am trying to reject registrations for which an email address was used. I put a constraint on the email field in the table and now if I try to register using an e-mail address that already exists in the database I get a violation error (only visible on the local machine) on the sql's email field, which is expected.
How can I catch that there is already an email address in the database and stop the execution of the code and possibly show a message to the user to use a different address?
Thank you for all your help.
Antonio
i just can't find a way to perform this Select Query in my ASP.Net page. I just want to find out the sales for a certain period[startDate - endDate] for each Region that will be selected in the checkbox. Table Sales Fields: SalesID | RegionID | Date | Amount This is how the interface looks like.Thank You.
View 1 Replies View Related
My task is to add income by taking few variables from webpage. I had take User ID(From database), Field value by selecting it from DropDownBox( Which value is once again taken from database), Income Description, Date, Amount . I had completed this task successfully by binding DropDownBox to database by query string and added income using stored procedure as below.
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this?
ASPX.CS file
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = null;
con = DataBaseConnection.GetConnection();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select PA_IFName from PA_IncomeFields where PA_UID=@PA_UID", con);
da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
da.Fill(ds);
IFDdl.DataSource = ds;
IFDdl.DataTextField= ds.Tables[0].Columns[0].ToString();
IFDdl.DataValueField = ds.Tables[0].Columns[0].ToString();
IFDdl.DataBind();
}
protected void IncAddBtn_Click(object sender, EventArgs e)
{
SqlConnection con = null;
try
{
con = DataBaseConnection.GetConnection();
SqlCommand cmd = new SqlCommand("AddIncome", con);
cmd.CommandType = CommandType.StoredProcedure;
//SqlCommand cmd = new SqlCommand("",con);
//cmd.CommandText = "insert into PA_Income values(@PA_UID,@PA_IFName,@PA_IDesc,@PA_IDate,@PA_IAmt)";
cmd.Parameters.Add("PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
cmd.Parameters.Add("@PA_IFName", SqlDbType.VarChar, 10).Value = IFDdl.SelectedValue;
cmd.Parameters.Add("@PA_IDesc", SqlDbType.VarChar, 50).Value = IFDescTB.Text;
cmd.Parameters.Add("@PA_IDate", SqlDbType.DateTime).Value = Convert.ToDateTime(IFDateTB.Text);
cmd.Parameters.Add("@PA_IAmt", SqlDbType.Money).Value = Convert.ToDecimal(IFAmtTB.Text);
cmd.ExecuteNonQuery();
IFLabelMsg.Text = "Income Added Successfully!";
} // end of try
catch (Exception ex)
{
IFLabelMsg.Text = "Error : " + ex.Message;
}
finally
{
con.Close();
}
}
Stored Procedure
ALTER PROCEDURE dbo.AddIncome (@PA_UID int,@PA_IFName varchar(10),@PA_IDesc varchar(50),@PA_IDate datetime,@PA_IAmt money)
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
/*SET NOCOUNT ON*/
insert into PA_Income values(@PA_UID,@PA_IFName,@PA_IDesc,@PA_IDate,@PA_IAmt)
ASPX File
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="AddIncome.aspx.cs" Inherits="AddIncome" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<h2>
Add Income </h2>
<br />
<table>
<tr>
<td>
Select Income Field</td>
<td>
<asp:DropDownList ID="IFDdl" runat="server" Width="247px" >
</asp:DropDownList>
<a href="addincomefield.aspx">Add Income Field</a>
</td>
</tr>
<tr>
<td>
Enter Income Amount
</td>
<td>
<asp:TextBox ID="IFAmtTB" runat="server" Width="96px"></asp:TextBox>
Date <asp:TextBox ID="IFDateTB" runat="server" Width="93px"></asp:TextBox>(MM/DD/YY)</td>
</tr>
<tr>
<td>
Enter Income Description
</td>
<td>
<asp:TextBox ID="IFDescTB" runat="server" Width="239px"></asp:TextBox></td>
</tr>
</table>
<br />
<asp:Button ID="IncAddBtn" runat="server" Text="Add Income" OnClick="IncAddBtn_Click" /><br />
<br />
<asp:Label ID="IFLabelMsg" runat="server"></asp:Label>
</asp:Content>
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this?
I am storing formatted data (including spaces and line breaks) in a single field in a table.
When I run a report on that field, the preview of the report is automatically removing all the extra spaces and line breaks, making the report unreadable.
When exporting the report to PDF or printing it, it shows the line breaks and spaces as expected.
Does anyone know how to make the report preview show the spaces and line breaks?
I am using Remote Data service to Query an Sql Server Database using MDAC. The Os in which server is loaded in Window 2003 and the MDAC 2.8 version is installed. Now I create a table X with identity column. Then when I try to insert records in that table using Insert into X select * from Y statement. The statement gets executed by when i check the X table I find that the duplicate records are present with different identity no's.
Even when i truncate and retry the same thing occurs.
What could be the reason for the same??
Regards
Pranali.cons
I've a weird problem in my application. In of the pages, while trying to update the text box "Name", when I enter Linda's test, it gets saved as Linda''s test. I'm not sure if this is a problem due to SQL server. When I look at the stored procedure, I don't anything different. Also, when I update the table directly in SQL Server, the result is displayed in single quote. But if I update the field thro' the application, the returned name is with double quotes instead of single quote. Has any of you faced problems like this? What am I missing? What do I need to do to get the name saved the way I entered (with single quotes) instead of double quotes?
View 1 Replies View RelatedHellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department: Sells:
1 Meat
1 Rice
1 Orange
2 Orange
2 Apple
3 Pears
The Data I would like read separated by Semi-colon:
Department: Sells:
1 Meat;Rice;Orange
2 Orange;Apple
3 Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
We have a large table which is very old and not much ppl take care about, recently there is a performance problem from the report need to query to this table. Eventally we find that this table have primary key missing and there is duplicate data which make "alter table add primary key" don't work
Besides the data size of this table require unacceptable time to execute something like "insert into new_table_with_pk from select distinct * from old table"
Do you have any recommendation of fixing this? As the application run on oracle , sybase and sql server, is that cross database approace will work?
When I tried to create a bar chart using SSRS 2012, the vertical axis values are repeating for smaller data sets values. It's only happening when the data labels are below 5, when the data is above 5 this chart represents data fine.
I tried specifying the custom intervals and this option all together eliminated the  bar for value 1, instead it only showed the value 1 as text on the chart.
I tried changing the data interval type as number and the data type is of Integer, these are counts which I am showing in the chart.
I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..