Fix Legacy Data - Missing Primary Key + Duplication Record + Large Table
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?
View Complete Forum Thread with Replies
Related Forum Messages:
Duplication In The Primary Key Column
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
View Replies !
Inserted Records Missing In Sql Table Yet Tables' Primary Key Field Has Been Incremented.
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails. I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record. And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
View Replies !
Error Catching On Data Duplication In A Sql2005 Db
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
View Replies !
Insert Statement Results In Data Duplication Using MDAC 2.8
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
View Replies !
Under 6.5, Best Way To Archive Data Out Of Large Table?
Hello: The purchased-application mssql 6.5, sp 4 that I am working on has one large table has 13m illion. It the largest table considering thenextlatgest table is only1.75 million rows. Thew vnedor has made a change to this largest table in recommending changing a data type -- char to varchar. To make this change easier to do, I want to "archive" older data not necessary for the current year or current processing to another table. What is the best way to do this archiving? Any information you can provide will be greatly appreciated. Thanks. David Spaisman
View Replies !
Missing Record - Phantom Record
Hi All,Have come across something weird and am after some help.Say i run this query where rec_id is a column of table arlhrl,select * from arlhrl where rec_id >= 14260This returns to me 2 records with rec_id's of 14260 and 14261Then I run this queryselect * from arlhrl where rec_id >= 14263This returns 7 records with rec_ids of 14263 up.How come the first query doesn't return the records returned by the2nd query also?If I select for 14262 no records are returned. It is like this is aphantom record or has an end of file character in it.I tried re-creating the indexes but to no avail. If anyone has anyideas about what could be causing it or how to fix it it would be muchappreciated.Thanks in advance,Andrew
View Replies !
Deleting Large Amount Of Data From The Table......
I need to delete data from a particular table which has more than half a million records. The data needs to be deleted is more than 200,000 records from the table. What is the best way to delete the data from the table other than importing into a temporary table and performing the same operation? Let me know if the strategy to be followed is okay. 1. Drop all the triggers 2. Drop all the indexes 3. Write a procedure with a loop setting ROWCOUNT to 1000 and delete the records. ( since if I try to delete all the rows it will give timeout error ) The above procedure will delete 1000 records for each batch inside the loop till it wipes out all the data for the specified condition. 4. Recreate Indexes and Triggers. Please let me know if there are any other optimal solution. Thanx, Zombie
View Replies !
Making The Value Of One Coloumn The Same For A Large Set Of Data In A Table?
Hello, Im a very new to SQL server etc, so please bear with me. I am currently trying to find a quick way of making a large number of database entries within a particular table all have the same value in one particular column. I have created a query in MS SQL Server Management Studio, which outputs all the entries in a particular table that I want to change. Currently they all have different values in a certain column in this table, and I want them all to have the same value in that column. How do I do this within Server Management Studio? Thank you.
View Replies !
Find Missing Data From Table
Here is an issue that has me stumped for the past few days. I have atable called MerchTran. Among various columns, the relevant columns forthis issue are:FileDate datetime, SourceTable varchar(25)SQL statement:SELECT DISTINCTFileDate, SourceTableFROMMerchTranORDER BYFileDate, SourceTableData looks like this:FileDate DataSource-----------------------------------2005-02-13 00:00:00.000S12005-02-13 00:00:00.000S22005-02-13 00:00:00.000S32005-02-14 00:00:00.000S12005-02-14 00:00:00.000S22005-02-14 00:00:00.000S32005-02-15 00:00:00.000S22005-02-15 00:00:00.000S32005-02-16 00:00:00.000S12005-02-16 00:00:00.000S22005-02-16 00:00:00.000S32005-02-17 00:00:00.000S12005-02-17 00:00:00.000S22005-02-18 00:00:00.000S12005-02-18 00:00:00.000S22005-02-18 00:00:00.000S32005-02-19 00:00:00.000S12005-02-19 00:00:00.000S3We run a daily process that inserts data in to this table everyday forall 3 sources S1, S2, S3Notice how some data is missing indicating the import process for thatparticular source failed.Example: Missing record2005-02-15 00:00:00.000S12005-02-17 00:00:00.000S32005-02-19 00:00:00.000S2Can someone please help me with a SQL Statement that will return me the3 missing records as above.Thanks in advance for all your help!DBA in distress!Vishal
View Replies !
Missing Sysprocesses Table Data
Our applications connect to our SQL Server database using DSNs. When the Client Configuration is set to TCP/IP, the sysprocesses fails to pull the nt_username and the hostname. When the Client Configuration is set to use Named Pipes, all of the data that we are looking for is there. Our problem is that all of the connections that use DSNs with Named Pipes doen't seem to be dieing when the user closes the front-end application. The applications are coming in from Terminal servers, Citrix, and Internet Information Server.
View Replies !
Missing Table Data In SQL 2005 Standard
Hello, There has been a sudden loss of data in some (not all) of the tables in my database. I am using SQL Sever 2005 Standard with a simple recovery model for a single database. I can't find any specific hints in the logs to explain the loss, and this did not occur during a backup or scheduled maintenance routine. It seems the data just vanished at a specific point this morning, and I am not able to find out why. Do any of you have a suggestion that would help explain the loss of data in specific tables and not others in a given database without the known use of ROLLBACK or DELETE? Or do any of you know a way that I can look back in the server to identify what may have happened? Any help would be appreciated. Thanks, Billy
View Replies !
Missing Data From Msdb.sysmaintplan_log Table
I have a number of maintenance jobs created using both the wizards and by script, however when I needed to delete a plan and had to cleanup a foreign key, I noticed that the following columns all contained nulls in the SYSMAINTPLAN_LOG table. Source_Server_Name Plan_Name Sub_Plan_Name Should these columns not be populated?
View Replies !
INSERT Data Into Table That Maybe Have That Primary Key Already
Hi, I'm not user to inserting data into databases, usually I just read the data. So I think my problem might be pretty common.I have a table of longitudes, latitudes, city names, and country names. I set the primary key to be the columns longitude and latitude. I have a method that generates the user's location and the mentioned data. So I want to only insert the new data into the database if it is new and unique. currently if the same user goes to my site, it inserts the data fine the first time and then throws and error the second time because it is inserting duplicate primary key information. Do I need to query the database to see if the data record already exists? or is there a way to insert the record only if it is "new"?? Thanks for the help!!
View Replies !
Missing Primary Keys
HI Folks , I have a problem with the primary keys in my main Db , I want to setup replication and looks like someone tampered with my database by removing the primary keys.and in order to setup replication i need this table to have primary keys .THere are duplicates in that table but they are nessecary...and for this reason the primary keys do not want to "stick" when i try and specify them.Can anybody help Burner
View Replies !
Replicatioin - Primary Key Icon Missing!
I am performing replication from a Publisher to Subscriber Server. Both Servers are running NT 4.0, SP3, SQL Server 6.5 SP5a. The Servers are set up in a Workgroup. I noticed that when I do an Edit on the table that was copied to the Subscriber Server that I do not see the Icon that indicates a field is a Primary Key. All fields are being replicated. The Primary Key indicator is just not there. How do I get this Primary Key Icon to automatically appear on the replicated table? Thanks in advance, Kevin
View Replies !
Log Shipping - Backup Job Missing On Primary
Hi everyone, I'm trying to configure log shipping on a 2005 sql server. I follow the wizard's instructions (see http://technet.microsoft.com/en-us/library/ms190640.aspx) and everything looks right except for the backup job that somehow is not being created on the primary server. Secondary server contains copy, restore and alert jobs. No errors reported on job monitor. Any ideas? Regards,
View Replies !
Sql 2005 Updating Table Definition With 'large' Amounts Of Data - Timeout
I'm trying to move my current use of an sql 2000 db to sql 2005. I need to update a table definition (to change a field to an Identity) I'm getting a dialog box (in SQL server management studio) on save saying : 'xxxx' table - Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible. I press 'Yes' to the dialog box. After 35 seconds, I get another dialog box saying: 'xxxx' table - Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Well, the server is responding and I can query that talbe and other, I can add/delete rows to other columns. I can modify other (smaller) tables. Any ideas where I can change this timeout? Daniel
View Replies !
Sql Table Data Stroring Based On Varchar Primary Key
Hi, I have a Users table that I use for membership. I am using username varchar(30) as the primary key for this table since username will always be unique. The question I have is regarding how SQL Server actually stores data: I see that when I add users, they are always stored alphabetically sorted on username. I was expecting that all users will appear on the users table in the order they were added. Example: I have 3 users (john, jonah, wilson). Now I added 4 user with username='bob' If I execute select * from users, it returns me (bob, john, jonah, wilson). Look bob has become the first row of the table. My question: Is Sql server moving 3 older rows to make room for 'bob' and it is also rebuilding part of the index due this new username 'bob'? If this is the case, then it will have big impact if I have 100K users and I add one user that becomes firstrow. In that case 99,999 rows will have to move. Bottom line, insert, delete will be very expensive. I know sql server keeps data physically sorted on PK. But I am concerned here since rows are losing the order in which they were inserted. Thanks
View Replies !
Report Model Problem: Missing Primary Key
Hi, I'm trying to create a report model but stumpled upon a little problem. Some of the tables that I'm using does not have any column that can be made into a primary key (all of the columns contains duplicate values). This causes some headaches since the tables won't get generated to the model. Is there some way around this? Perhaps create a "fake" PK column that works similar to an id column. Any help is highly appreciated. Kind Regards, Blixt
View Replies !
Populating An Access Combo Box With Large Amount Of Data Causes Table Lock In SQL Server
I have a combo box where users select the customer name and can eithergo to the customer's info or open a list of the customer's orders.The RowSource for the combo box was a simple pass-through query:SELECT DISTINCT [Customer ID], [Company Name], [contact name],City,Region FROM Customers ORDER BY Customers.[Company Name];This was working fine until a couple of weeks ago. Now wheneversomeone has the form open, this statement locks the entire Customerstable.I thought a pass-through query was read-only, so how does this do atable lock?I changed the code to an unbound rowsource that asks for input of thefirst few characters first, then uses this SQL statement as therowsource:SELECT [Customer ID], [Company Name], [contact name],City, Region Fromdbo_Customers WHERE [Company Name] like '" & txtInput & "*' ORDER BY[Company Name];This helps, but if someone types only one letter, it could still bepulling a few thousand records and cause a table lock.What is the best way to populate a large combo box? I have too muchdata for the ADODB recordset to use the .AddItem methodI was trying to figure out how to use an ADODB connection, so that Ican make it read-only to eliminate the locking, but I'm striking outon my own.Any ideas would be appreciated.Roy(Using Access 2003 MDB with SQL Server 2000 back end)
View Replies !
Missing Sysuser Record
Is there a reason why a record in sys.sysuser would be visible to the system admin login but not to a standard user? If I want the standard user to see all the records in sys.sysuser, is there a permission setting I can change to make that happen? If I give the standard user system admin permissions, all records show up. But I cannot keep the standard user as system admin. Thanks.
View Replies !
Missing Sysuser Record
Is there a reason why a record in sys.sysuser would be visible to the system admin login but not to a standard user? If I want the standard user to see all the records in sys.sysuser, is there a permission setting I can change to make that happen? If I give the standard user system admin permissions, all records show up. But I cannot keep the standard user as system admin. Thanks.
View Replies !
Question On Displaying Large Number Of Record?
I have datagrid that needs to display a log table which has more than million records. Since it it huge number, it is not possible to get dataset using "select * from log_table" to fill and to bind to datagrid.Is there anyway to display first 100 rows on first page and show next 100 rows if use clicks on page 2?Thank you very much in advance!Justin
View Replies !
How To Find Missing Records From Tables Involving Composite Primary Keys
Table 1 Code Quarter 500002 26 500002 27 500002 28 500002 28.5 500002 29 Table 2 Code Qtr 500002 26 500002 27 I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS Can anybody help me with how to compare the two tables to find the records not present in Table 2 That is i need this result Code Quarter 500002 28 500002 28.5 500002 29 I have come up with this solution select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2) i need to know if there is some other way of doing the same Thanks in Advance Jacx
View Replies !
Some Of New Record Missing During Transaction Replication
i have transactional replication setup. recently i found that some of the new records in the publisher are not replicated to subscriber, but the replication is going on as normal with most of the record replicated. the only this happen is user stop and restart the synchronization. why this happen, how to find all the records which are not replicated and how to replicate to subscriber now.
View Replies !
Get Primary Key Of Last Inserted Record
Ok I know this might not be the most accurate place to post this but I know someone here has an answer for me on it.I need to get the product_ID of the new record that is created by this insert statement INSERTINTO products ( class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo ) SELECT class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo FROM productsWHERE product_ID = @productID
View Replies !
Insert New Record - Primary Key
Hi I have a table in sql server with a numeric field as Primary Key. When i insert a new record i need that primary key increments automatic (like access auto increment) because i want avoid the possibility of duplicate Primary Keys. Is that possible? Thank you
View Replies !
Primary Key Record Inserts
I'm performing an insert on a table that has a relationship, but I also want to update the other table that has a foreign key relationship. How do I go about this? Thanks :)
View Replies !
Check For Primary Key Before Inserting New Record
Hi, Can someone please tell me the best practices for checking the primary key field before inserting a record into my database? As an example I have created an asp.net page using VB with an SQL server database. The web page will just insert two fields into a table (Name & Surname into the Names table). The primary key or the Names table is "Name". When I click the Submit button I would like to check to ensure there is not a duplicate primary key. If there is return a user friendly message i.e. A record already exisits, if there no duplicate, add the record. I guess I could use try, catch within the .APSX page or would a stored procedure be better? Thanks Brett
View Replies !
Import Csv Data To Dbo.Tables Via CREATE TABLE && BUKL INSERT:How To Designate The Primary-Foreign Keys && Set Up Relationship?
Hi all, I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations: -- ImportCSVprojects.sql -- USE ChemDatabase GO CREATE TABLE Projects ( ProjectID int, ProjectName nvarchar(25), LabName nvarchar(25) ); BULK INSERT dbo.Projects FROM 'c:myfileProjects.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO ======================================= -- ImportCSVsamples.sql -- USE ChemDatabase GO CREATE TABLE Samples ( SampleID int, SampleName nvarchar(25), Matrix nvarchar(25), SampleType nvarchar(25), ChemGroup nvarchar(25), ProjectID int ); BULK INSERT dbo.Samples FROM 'c:myfileSamples.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO ========================================= -- ImportCSVtestResult.sql -- USE ChemDatabase GO CREATE TABLE TestResults ( AnalyteID int, AnalyteName nvarchar(25), Result decimal(9,3), UnitForConc nvarchar(25), SampleID int ); BULK INSERT dbo.TestResults FROM 'c:myfileLabTests.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO ======================================== The 3 csv files were successfully imported into the ChemDatabase of my SSMSE. 2 questions to ask: (1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables? Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period? (2) How can I set up the relationships among these 3 dbo Tables? Please help and advise. Thanks in advance, Scott Chang
View Replies !
Inserting A Record Into The Database ..problem With Primary Key..
hii I have to insert some data into a table from the webpage. For that I need to know the last occurred value in the primary key and increment this by one and then insert the new record into the table. I am working with SQL Server 2005. I am coding in VB and ASP.NET 2.0. How will I go about this?? Is there some easy way for me to knw the last value of the primary key n then insert the record. It would be great if I get the idea more clear with the help of some code... Thanks
View Replies !
Avoid Repetition Of Primary Tables's Record
hi all,can you tell me how to avoid repetition of main records when one main(Primary table) record have more than one child(secondry table) records. I am using DataList to show records.For example I want to show the following Tools & Equipment http://www.mobmasti.com MobMasti Mobile Portal Manufactures http://www.manu.com Manufacturer desc manufactur Rental Companies http://www.rentit.com Rent It rent it desc Buy & Sell http://www.mobmasti2.com MT desc Manufactures http://www.allofuse.com all stff desc Employment http://www.mobmasti.com/ MobMasti Mobile PortalAS(I want to show in the following way) Tools & Equipment http://www.mobmasti.com MobMasti Mobile Portal Manufactures http://www.manu.com Manufacturer desc manufacturhttp://www.allofuse.com all stff desc Rental Companies http://www.rentit.com Rent It rent it desc Buy & Sell http://www.mobmasti2.com MT desc Employment http://www.mobmasti.com/ MobMasti Mobile PortalMy query is as followsSELECT DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url, '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.DescriptionFROM DirectoryMainCat INNER JOIN DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryIdWHERE DirectoryLinks.Hitcount > 0ORDER BY DirectoryLinks.HitCount DESCwhere 'DirectoryMainCat' is the main table and 'DirectoryLinks' is the child table.thanks in advance.
View Replies !
Concurrent Access - New Record, Primary Key Problem....
Can someone explain what happens when two users concurrently attempt tocreate a new record in a table with an autonumber primary key? For example,user 1 creates a new record and manipulates it within a transaction makinguse (perhaps) of the @@IDENTITY value when creating other, related records.Before this transaction is complete, user 2 creates a new record and doesthe same thing. Presumably they will both have the same @@IDENTITY? Ifthis is the case, how is it possible to manage such a situation?Thanks.
View Replies !
Fuzzy Grouping Primary Record Selection
I am using a fuzzy grouping to de-dupe some data. In this case, it is address data. One issue that I am running into is that i have a set of 4 records where 3 of them are identical, and the 4th has NULL for the state. however, the fuzzy grouping insists on making this record the primary record (_key_in = _key_out). I have tried sorting the source data in different ways, and as expected, this does not change the outcome. First, why does it think this record is the primary, and is there anyway to "force" the data requirements for the primary record? Thanks!!
View Replies !
How To Reference The Primary Key Of A Newly Added Record In Trigger?
Please help me somebody solve my problem with my first :o trigger: ALTER TRIGGER partner_update ON dbo.partner FOR UPDATE AS INSERT INTO partner (name) SELECT name FROM deleted UPDATE invoice SET id_partner= *** WHERE id_partner = (SELECT id_partner FROM deleted) *** - here I want to add a "reference" to the newly added record's automatically generated primary key (not to the updated!) Is it possible?
View Replies !
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 !
Joining Record With The Most Recent Record On Second Table
Could anybody help me with the following scenario: Table 1 Table2 ID,Date1 ID, Date2 I would like to link the two tables and receive all records from table2 joined on ID and the record from table1 that has the most recent date. Example: Table1 data Table2 Data ID Date1 ID Date2 31 1/1/2008 31 1/5/2008 34 1/4/3008 31 4/1/2008 31 3/2/2008 The first record in table2 would only link to the first record in table1 The second record in table2 would only link to the third record in table1 Any help would be greatly appreciated. Thanks
View Replies !
Update A Record Based Of A Record In The Same Table
I am trying to update a record in a table based off of criteria of another record in the table. So suppose I have 2 records ID owner type 1 5678 past due 2 5678 late So, I want to update the type field to "collections" only if the previous record for the same record is "past due". Any ideas?
View Replies !
Duplication
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.
View Replies !
Duplication Error
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
View Replies !
Report Job Duplication
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
View Replies !
Does It Store All The Results To Tempdb Database When I Query Against A Large Table Which Joins Another Table?
Hi, all experts here, I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices. With best regards, Yours sincerely,
View Replies !
Prevent Duplication On UPDATE
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
View Replies !
Track Duplication Of Records
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
View Replies !
|
TRACKER
