Delete Rows With Duplicate Column Data But Unique Row Data
Hello,
This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.
I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.
I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,
Kerry
View Complete Forum Thread with Replies
Related Forum Messages:
Dynamiclly Remove Duplicate Rows From Results Table Based On Column Data?
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them. each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field. Ideas? Thanks, Ad.
View Replies !
Renaming Duplicate Row Data To Be Unique?
I just converted an old non-relational database into something that MS SQL likes. The old primary keys were broken up into two columns, one being useful. The column I need to use has some rows with the same values in them. I am looking for some way in a SQL script to look for the duplicate rows and add "_X" to the data where X is a value incremented by 1 for each duplicate row found. For example, 3 duplicate rows with "5443aa" would return "5443aa", "5443aa_1","5443aa_2". Any ideas? --MartinZ
View Replies !
Duplicate Key But Unique Rows.
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique. How do I filter out only the duplicate zips. Randy Garland
View Replies !
How To Delete Duplicate Data From Field
my table structure is id field1 1 i am from india 2 i am from usa 3 i am from delhi So i want to remove common data from field1 , means after run the query table should be like id field1 1 india 2 usa 3 delhi thanks in advance saumitra tamrakar
View Replies !
Unique Rows Of Data Query
How would I get the unique email addresses and its associated row of data from a SQL Server table that has no unique fields defined? If there is a duplicate email address then only show the first one and not the other rows with the same email address. Example table and data UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997623 KAIS-22 A.KAIS-22@SampleISD.org997624 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.ZKAIS-22@SampleISD.org997627 KAIS-22 AX.KAIS-22@SampleISD.org Result UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.KAIS-22@SampleISD.org Thanks
View Replies !
Unique Index Returns Duplicate Rows
We are running the following query, which has a unique index on Table_2 (col1 and sys1), and Column col1 from Table_1 is unique. select top 100 s.*, x.col1 from Table_1 s left outer join Table_2 x on x.col1 = s.col1 and x.sys1 = 'SYSTEM0' Unfortunately this query returns duplicate rows. And every time the result is different But once we dbcc dbreindex the unique index on Table_2, the result will not have any dups. Any ideas? Thanks Steve
View Replies !
Deriving Unique Rows From Historical Data
My application is to capture employee locations.Whenever an employee arrives at a location (whether it is arriving forwork, or at one of the company's other sites) they scan the barcode ontheir employee badge. This writes a record to the tblTSCollected table(DDL and dummy data below).The application needs to be able to display to staff in a control roomthe CURRENT location of each employee.[color=blue]>From the data I've provided, this would be:[/color]EMPLOYEE ID LOCATION CODE963 VB002964 VB003966 VB003968 VB004977 VB001982 VB001Note that, for example, Employee 963 had formerly been at VB001 but wasmore recently logged in at VB002, so therefore the application is notconcerned with the earlier record.What would also be particularly useful would be the NUMBER of staff ateach location - viz.LOCATION CODE NUM STAFFVB001 2VB002 1VB003 2VB004 1Can anyone help?Many thanks in advanceEdwardNOTES ON DDL:THE BARCODE IS CAPTURED BECAUSE THE COMPANY MAY RE-USE BARCODE NUMBERS(WHICH IS DERIVED FROM THE EMPLOYEE PIN), SO THEREFORE THE BARCODECANNOT BE RELIED UPON TO BE UNIQUE.THE COLUMN fldRuleAppliedID IS NULL BECAUSE THAT PARTICULAR ROW HAS NOTBEEN PROCESSED. THERE ARE BUSINESS RULES CONCERNING EMPLOYEE HOURSWHICH OPERATE ON THIS DATA. ONCE A ROW HAS BEEN PROCESSED FORUPLOADING TO THE PAYROLL APPLICATION, THE fldRuleAppliedID COLUMN WILLCONTAIN A VALUE. IN THE PRODUCTION SYSTEM, THEREFORE, ANY SQL ASREQUESTED ABOVE WILL CONTAIN IN ITS WHERE CLAUSE (fldRuleAppliedID IsNULL)if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSCollected]GOCREATE TABLE [dbo].[tblTSCollected] ([fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldTimeStamp] [datetime] NULL ,[fldRuleAppliedID] [int] NULL ,[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOINSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (963, 'VB001', '2005-10-18 11:59:27.383', 45480)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (963, 'VB002', '2005-10-18 12:06:17.833', 45480)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB001', '2005-10-18 12:56:20.690', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB002', '2005-10-18 15:30:35.117', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (964, 'VB003', '2005-10-18 16:05:05.880', 45481)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB001', '2005-10-18 11:52:28.307', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB002', '2005-10-18 13:59:34.807', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB001', '2005-10-18 14:04:55.820', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (966, 'VB003', '2005-10-18 16:10:01.943', 97678)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB001', '2005-10-18 11:59:34.307', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB002', '2005-10-18 12:04:56.037', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (968, 'VB004', '2005-10-18 12:10:02.723', 98374)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (977, 'VB001', '2005-10-18 12:05:06.630', 96879)INSERT INTO dbo.tblTSCollected(fldEmployeeID,fldLocationCode,fldTimeStamp,fldBarCode)VALUES (982, 'VB001', '2005-10-18 12:06:13.787', 96697)
View Replies !
How To Eliminate Duplicate Rows In Data Flow Task ?
Dear all, I built a package to transform data from flatfile into temp table. Then execute a stored procedure to tranform from the temp table into the real table. Since the real table have primary keys so it goes failed when there're duplicate rows from temp table. I let the temp table has no primary key. If I had to check the temp table for duplicate rows it would be using cursor through the data and the package will get slow. Is there task in SSIS to identify the duplicate data and eliminate it without using cursor ? Thanks in advance, Best regards, Hery
View Replies !
Unique Column Data
Absolute SQL Beginner here I am using SQL Server 2005 Express Edition in conjunction with asp.net 2.0 Is there any way to guarantee when INSERTing data, that all values in a given column (which is of a char data type) are unique? Or do I have to do this programatically at the asp layer? Thanks in advance Larry
View Replies !
Delete Duplicate Rows
Suppose that we have the following rows in a MSSQL table : 1administrateur1NULLNULL 2administrateur2NULLNULL 1administrateur1NULLNULL 2administrateur2NULLNULL How to remove duplicates (leave only the 2 first rows) ?
View Replies !
Delete Duplicate Rows
Hi, I have the following query to select duplicate rows from the table. How can i delete them with out using temp table. select UserName, Title, Name, ColWidth, Sequence from table1 (nolock)) Group by UserName, Title, Name, ColWidth, Sequence Having count(*) >1 Any help would be greately appreciated. Thanks
View Replies !
DELETE DUPLICATE ROWS
CAN ANYBODY REPLY FOLLOWING QUESTIONS. I WANT TO DELETE DUPLICATE ROWS IN MY TABLE WITHOUT USING TRANSACTION TABLE. AND ONE MORE QUESTION HOW TO GET YESTERDAY DATE BY USING ISQL WINDOW. THANKS JK
View Replies !
Delete Duplicate Rows
I have a table which looks as follow: field1 field2 field3 field4 field5 ...... A B C A X ...... A B C B Y ...... A B C C Z ...... A B C A Y ...... . . . . . ...... I want to delete all the rows except one row. Anybody can help? Thank you very much.
View Replies !
How Do You Delete Duplicate Rows......
How do you delete duplicate rows when there are constraints or like in professional terms as we say "Referential Integrity". Mr.Madhivanan i hope you remember me. Because it is after all purpose that binds us all together...purpose...it is inevitable Mr.Madhivanan.
View Replies !
Duplicate Data In Column
I know that I have duplicate data in a column. The column has over 40,000 rows so manual checking would not be feasible. I don't think the whole row is the same, but I know for a fact that one particular column has at least 2 rows with the same data. Help please!
View Replies !
Update Rows To Resolve Issues About Duplicate Keys On Create Unique Index
Hi there ...here comes a tricky one. I have a database table which needs to make the Index "ParentREF, UniqueName" unique - but this fails because duplicate keys are found. Thus I now need to cleanup these duplicate rows - but I cannot just delete the duplicates, because they might have rows in detail tables. This means that all duplicate rows needs an update on the "UniqueName" value - but not the first (valid) one! I can find those rows by SELECT OID, UniqueName, ParentREF, CreatedUTC, ModifiedUTC FROM dbo.CmsContent AS table0 WHERE EXISTS ( SELECT OID, UniqueName, ParentREF FROM dbo.CmsContent AS table1 WHERE table0.ParentREF = table1.ParentREF AND table0.UniqueName = table1.UniqueName AND table0.OID != table1.OID ) ORDER BY ParentREF, UniqueName, ModifiedUTC desc ...but I struggle to make the required SQL (SP?) to update the "invalid" rows. Note: the "valid" row is the one with the newest ModifiedUTC value - this row must kept unchanged! ATM the preferred (cause easiest) way is to rename the invalid rows with UniqueName = OID because if I use any other name I risk to create another double entry. Thanks in advance to whoever can help me
View Replies !
To Retrieve And Delete The Dupilicate Rows From Which Does Not Have Any Unique Fields
is there any way to Retrieve duplicate rows from a table which does not have any unique columns. that is a select should be written in such a way that it returns all the duplicate rows. a delete statement should written to delete those duplicate rows. if there any seudo colums in sql server as in oracle. if not how to implement the concept. that is there should be a dummy column in the result set of the select which should display the row number like the seudo column rownum in oracle.
View Replies !
How To Delete Duplicate Rows Retaining Only One Of Them From Every Set Of Duplicates.
I have a table employee_test having the sample data. The rows with EmployeeID=6 are duplicate rows. I want to delete the duplicates retaining one row for the employeeid=6. Note :- I don't want to use a temporary table. I want to do this using a single query or at the most in a SP query batch. Please advise. EMPLOYEEID ENAME SALARY MANAGERID 1 Anee 1000 11 2 Rick 1200 12 3 JOHN 1100 13 4 ABC 1300 14 5 DEF 1400 15 6 DEF 1400 15 6 DEF 1400 15
View Replies !
Delete Data On Behalf Of Multi Rows
HI, is there any statement by using that i can delete data from the table on behalf multi rows. like delete from Table where = (select id from table1 where group=1) second statemetn returns 4 rows by group =1 is there single statment.. Thanks
View Replies !
How Do I Use The Visual Studio 2005 To Make A Data Base Column Unique?
I am sorry if this is the wrong forum for this question but I can not find better fit. I am using Visual Studio 2005 to write an asp.net web site which includes an SQL data base. In the databaseI have a table called 'ACCOUNTS' which has primary key column called 'ID' and a nvarchar column called 'Name'. I want the values in the 'Name' column to be unique in the table so that all accounts have unique names. I don't want to make the 'Name' column the primary key because I want people to be able to change the name of their account after it has been created. It seems to me there must be some way to add such a constraint to the table but I can find none. I have searched the documentation and seen loads of references to unique columns but I can't find any way to set a column to unique thru the IDE (or any other way). Can anyone help me? Thanks in advance for any help.
View Replies !
How To Delete Data When Column Have Depency With Other Column?
in Table A Column is PriKey No Name 1 1 Apple 2 2 Orange 3 3 Juicy in Table B column is Prikey ColumnA Price 1 1 10 2 3 2 3 2 5 TableA.Prikey have Depency with TableB.ColumnA when I am trying to Delete data from Table A , I got error message becaue the depency how to delete data when there have depency? I just know when table have trigger , we can disable trigger before delete Data, and enable trigger when data deleted does there have a way to disable depency and then enable ? thank you
View Replies !
Trying To Modify Some Column Data In Some Rows
Trying to change some of the rows in a table specifically one column. column type is varchar ex. of data current desired $345,434.0 345434.0 (stripping out of $ & commas) 435.0 leave as is general txt leave as is having trouble updating data in table tried using temp table, but update command make it so it won't see table ex. piece of code update currency_conversion set currency_varchar = cast (cast (currency_varchar as money)as varchar) select * from currency_conversion where substring (currency_varchar from 1 for 1) = '$' ; sql 2000 trying to do from query analyzer thanks glnsk8ter@yahoo.com thanks Glenn
View Replies !
Help With Combining Data From Multiple Rows Into One Column In A View
Hi, I am stumped and was hoping someone could help me out. Any help isappreciated.I have a view that looks sort of like this (but with a lot moreentries of course)UniqueIdentifyierColumn1Column21 9999 1002 9999 2003 9999 300What I want to do is to add a column to the view that will contain alist of the values from column 2 where column 1 is the same.UniqueIdentifyierColumn1Column2Column31 9999100100, 200, 3002 9999200 100, 200, 3003 9999300100, 200, 300
View Replies !
Matrix That Adds A New Column After 5 Rows Are Filled With Data
Hello, I have a report in wich I show a list of countries. The length of this list differs each month and comes from a SSAS datasource. I want to show this list on my report in a matrix like this: country1 country6 country11 country2 country7 country12 country3 country8 country4 country9 country5 country10 How can I do this? I need some sort of check that counts the number of countries added and then adds a new column after 5 countries. I'm sure there is somebody that did this or knows how to do this. Thanks in advance! Jorg.
View Replies !
Select Statement That Will Output Related Rows With Different Column Data Per Row?
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like: rowtype| ID | value A | 123 | alpha B | 123 | beta C | 123 | delta A | 124 | some val B | 124 | some val 2 C | 124 | some val 3 etc... where for each ID, I have 3 rows that are associated with it and with different corresponding values. I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table. i.e. each ID iteration will do something like: insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha') insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta') insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta') etc.. After my loop, I will just do a select * from #someTempTable Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
View Replies !
Import Data Where Current Data Has Unique Identity
I have data for online catalogue in SQL 7.0. The web grogrammer asked me to add a unique key for reference. I used int datatype with identity seed of 1 and increment of 1. This works fine BUT when I try to import new data I get an error because the csv file has no column and therefore no value for the unique field which will not allow null by definition. How can I maintain a unique field to act as primary key in my data when I want to add (and delete) data that doesn't have this field. I tried adding the uniqueidentifier field but this gives error message. The only work round is to delete the unigue field altogether and then add the new data and afterwards create a new unique field. At 600000 + lines of data, this is time and memory consuming Any help appreciated,Thanks, Keith
View Replies !
Transform To Remove Rows From Data Set A That Match Rows In Data Set B On A Given Key?
Hi, I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input. E.g. If I have the following data: Main input: Key Name --- ---- 1 Steve 2 Jamie 3 Donald Second Input Key DontCareAboutThisField1 --- ----------------------- 1 ... 3 ... 4 ... Then I would like the following output: Main Output Key Name --- ---- 2 Jamie Second Output Key Name --- ---- 1 Steve 3 Donald Can I do this with a standard transform, or will I have to write my own? Any help on this would be greatly appreciated! Thanks in advance, Lawrie
View Replies !
REPLACE In Derived Column Transform Causing Repeat Data In Rows That Should Be Blank
W2k3 server, SQL 2005. @@version = Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1) I have my first SSIS package almost working, but I'm having an odd problem and can't find any information to help resolve it. I'm importing from a flat file (csv) to an existing table (append). I've got a Derived Column transformation in the middle to do some data cleanup. It's all working except for one little problem... One of the transformations is 'REPLACE([Column 3],"^","; ")', output to a new column. (The input file has a field that uses carets as delimiters between an unknown number of items; I'm changing that to semicolons for easier reading.) Not all rows have data in this column, some will have one item, some will have multiple items. The REPLACE works except that it fills in repeated data for all the blank rows. Example: Incoming data is: 1 Smith,Jane^Jones,Jane 2 Brown,John 3 4 Adams,James^Adams,Jim 5 6 White,Debra Data inserted into the table is: 1 Smith,Jane; Jones,Jane 2 Brown,John 3 Brown,John 4 Adams,James; Adams,Jim 5 Adams,James; Adams,Jim 6 White,Debra I've tried to use a Conditional to skip the empty rows, but I can't get that working at all (get syntax errors no matter what I put in). Any suggestions on how to fix this would be most appreciated! Thank you.
View Replies !
Importing Unique Data && MAX Data To Table Using DTS
I am creating a DTS package that is combining several tables, converting one column of data to a new column removing all special characters, then exporting the unique data based on this column and another column, and the max of other duplicates to a new table. Now that I have the data in this table, I want to import any data that is not in my main table. This "CLEANED" table does not have a designated "key" column, but the table I want to import the unique items does have an ID column that is also a primary key column. DTS seems to want me to have a Key column to reference when importing from the CLEANED table to the MAIN table. How would I go about checking the MAIN table against the CLEANED table, having DTS import only the unique items from the CLEANED table that are not present in the MAIN table based on three columns? The rest of the columns I want to just extract the MAX data from the duplicates. Now here is the query I use to extract the unique values from the "CLEANING" table to get the data to the "CLEANED" table, but do not know how to use this to import into the MAIN table using something similar. Code: select partno2, MAX (partno) as partno, alt, MAX (C_alt) as C_alt, Max (cmpycd) as cmpycd, MAX (type) as type, compFN, MAX (pndesc) as pndesc, MAX (equipment) as equipment into tbl_CLEANED from tbl_CLEANING group by partno2, alt, compFN ORDER BY partno, compFN The three main columns I need to check against are: partno2 alt compFN I have named the columns the same in both tables. partno2 is the column that has been copied from partno with all special characters & spaces removed. This is the main column I am using as a reference for unique values, then if no match, I have it check against the alt column, then the comFN column. If there are no matches in any of these columns, then I want to extract the data to the MAIN table. How can I compare these tables and import only unique info to the MAIN table? In addition, how can I also check items that are the same in both tables and update the MAX info for the other columns (not the three I use for reference - these I need to leave alone) and update those if there is more data in the CLEANED table then in the MAIN table?
View Replies !
How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns
Please can anyone help me for the following? I want to merge multiple rows (eg. 3rows) into a single row with multip columns. for eg: data Date Shift Reading 01-MAR-08 1 879.880 01-MAR-08 2 854.858 01-MAR-08 3 833.836 02-MAR-08 1 809.810 02-MAR-08 2 785.784 02-MAR-08 3 761.760 i want output for the above as: Date Shift1 Shift2 Shift3 01-MAR-08 879.880 854.858 833.836 02-MAR-08 809.810 785.784 761.760 Please help me.
View Replies !
Problem Loading Data From FlatFile Source Data For Column Overflowed The Disk I/O Buffer
Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error The column data for column "Column 20" overflowed the disk I/O buffer. I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this . In case of Bad Data how to clean up the source.. Please help me with this
View Replies !
Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer): user1 answer1 answer2 answer3user2 answer1 answer2 answer3 How can this be done? How can all answers of a user appear on a single row Thanx,Danny.
View Replies !
System.Data.SqlClient.SqlException: Syntax Error Converting The Varchar Value 'V' To A Column Of Data Type Int
I am using a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure; SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@opwd", SqlDbType.NVarChar);SqlParameter p3 = com.Parameters.Add("@role", SqlDbType.NVarChar);p3.Direction = ParameterDirection.ReturnValue;p1.Value = username.Text.Trim();p2.Value = password.Text.Trim();com.ExecuteReader();lblerror2.Text = (string)(com.Parameters["@role"].Value); can your figure out what is the error ? Is it a coding error or error of the databse
View Replies !
Adding A New Data Column (not Derived) Midway Thru A Data Flow
I need to know what a table's max row Identity is part way thru a data flow. I can't get it at the beginning of the data flow. I need to either (1) add it to the data buffer part way thru or (2) set it into a package variable and then reference the var in a script component. I've not found a way to add a database column to the data buffer without doing a lookup for each row (too slow and not appropriate here) or some goofy oledb source and then merge join into the data buffer on a contrived join. I've read questions about referencing package vars in scripts but I can't get that to work. DTS.Variables("varname").Value isn't recognised when I code it up. Anyone have an idea or solution for either one of these? If you're gonna explain the script code, please include the entire snipet including the INCLUDEs, etc.
View Replies !
Using SSIS 2005 To Strip Out Bad Rows In Excel And Then Insert Detailed Rows Into OLE DB Data Source
Environment: Running this code on my PC via VS 2005 .Net version 2.0.50727 on the server (shown in IIS) Code is in ASP.NET 2.0 and is a VB.NET Console application SSIS 2005 Problem & Info: I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel. Desired Help: How to perform 1) stripping out all undesired rows 2) importing each column into sql table
View Replies !
Indexing Non-unique Data
I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily. The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B). Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?
View Replies !
Elimenating Duplicate Keys With Unique Row.
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique. How do I filter out only the duplicate zips. So in effect I only have one row per unique key. Randy Garland if you just want a list of all rows with duplicate zipcodes then ... SELECT * FROM TableName WHERE zip IN ( SELECT zip FROM TableName GROUP BY zip HAVING COUNT(*)>1 ) Duncan Duncan, I tried this but it does not return one row per key. Randy Garland
View Replies !
Can I Duplicate Data?
Hello guys! Is it possible to duplicate a primary key? I would like my database to accept data with the same primary key. Is it possible? How do you declare ON DUPLICATE KEY UPDATE? Please help me. Thanks in advance.
View Replies !
Duplicate A Row Of Data MS Sql
I want to be able to duplicate a row of data in sql....Does anyone know if there is a sql command that will do that. I have a table with an auto increment primary key and I want to duplicate everything except the key into a new record. Thanks.
View Replies !
Duplicate Data
Hello all, I have recently been working on a project that requires one simple table to insert data into. The problem here is that all the data inserted must only access the database via stored procedure and I want to ensure that no duplicate data is inserted in the database. I have done quite a bit of research for many ways to perform duplicate data testing from building temp tables and on, but nothing has really stood out to me yet. I would really like to find some information on how to perform duplicate data testing using a stored procedure that allows to test the data being inserted before it is saved to the database; therefore, when the user inserts the fields and clicks the insert button, the fields will be tested against the existing data (via stored procedure) within the database before being added. Can anyone help? Thanks
View Replies !
|
TRACKER
