How To Compare Data Before Deletion
SET identity_insert dbo.table1 on
GO
insert into dbo.table1(
PrimaryKeyCol,Col1, Col2 .....)
select
PrimaryKeyCol,Col1, Col2......
from [Sever].Database.dbo.table1 as ClientColumn
where not exists(
select * from dbo.table1 as ServerColumn
where ServerColumn.PrimaryKeyCol = ClientColumn.PrimaryKeyCol
)
DELETE FROM [Server].Database.dbo.table1
where exists(
where ServerColumn.PrimaryKeyCol = ClientColumn.PrimaryKeyCol
)
SET identity_insert dbo.table1 off
GO
I can't complie this code.. anybody see where I went wrong??
Thanks for all your help.
View Complete Forum Thread with Replies
Related Forum Messages:
Alert On Data Deletion
We have an employee table that contains bank details and are experiencingproblems with account numbers being erased and lost. In order to track downwhy this is happening (either due to our application code or SQLreplication) we'd like to be able to prevent certain columns from beingdeleted if they already contain some data.Is it possible to setup a check constraint to prevent our ee_acct_no columnsfrom being set to NULL or blank strings if it contains an account number(i.e a 9 digit number)? We have setup the column to allow NULL's as we don'talways know employees bank details until later, so we do need to put them onour database without bank details initially.Also, if possible, can someone suggest a stored procedure or trigger i couldcreate that would fire a user-defined error message that would email anoperator if a bank account number changed?Many thanksDan Williams.
View Replies !
Deletion Old Data In Replication Environment
Hi to allI have a question about deletion of amount of data:My production environment is this one:- one publisher with a database (historycal events)- 50 subscribers with the prev database in unidirectional replicationunidirectional (from subscribers to publisher)My target was capturing events from the subscribers to send them topublisher (later I can do reports on it).Once the data is on the server i don't need them any more in subscribers.Now I would like to delete the oldest data (year 2003) of some table on thepublisher (remember that replication is unidirectional S->P).The tables contain about 6-7 millions of records.I delete one month per time. The process is about 30 minutes long and themerge agent subscribers changes in retry state.Can I use these queries to make faster this process? Eventually what kind ofproblems can I have ?DELETE FROM mydb WITH (PAGLOCK) WHERE mydb.dbo.mydate Between date1 anddate2orDELETE FROM mydb WITH (ROWLOCK) WHERE mydb.dbo.mydate Between date1 anddate2Thank you very much for your support.Marco
View Replies !
Deletion Of Data Other Than English Language
Anil Chauhan writes "Hello experts, I have a table in sql server 2000 which has over 94000 records. I have to delete a record from table ,which record having a language other than english . I need to clean the table by removing all the data which are in other language . My main table has 12 fields . Thanks in advance."
View Replies !
Data Deletion On MS Access Table Hangs
Hello, I have not been able to locate information on the following problem. The first step I have in a packge (Execute SQL Command) is to delete the data from an MS Access database table. The package hangs at this step after all validation is complete. In the package, once the table data is deleted, it is repopulated in a later step. The deletion step and the repopulation step use the same connection manager. There is no information in the log about an error. At the time the package ran, there was a lock file on the database with about six users connected. I'm not sure what version of Access the database was created in, but I have 2003 on my machine, and I cannot open the database. Any ideas? Thank you for your help! cdun2
View Replies !
Large Amount Data Deletion Blocking Other Operations
Hi Experts: We have several database linked via merge replications. Due to business requirements, we need to delete 5M rows in one table, we did it on one subscriber. However, the publisher kept uploading the deletion operations from the subscriber and blocked any downloading operation from publisher to subscriber. How can we acceralte the replications now as this has already operated in 2 days, and will continue 1-2 days? Is it possible to set the publisher take the downloading before uploading? How to speed up large amount data deletion operations in replication environment? Thanks in advance! Ron
View Replies !
Compare Data
I've created two tables. One table (Classes) stores data about classesthat we offer. The Classes table stores the class id (classid) and themax number of students allows (maxstudents). The other table(Students) stores student data and the class they register for.When a user registers for a class, the classid column data from Classespopulates the class column in Students.I'm not sure how to count the number of students who registered forcourse X, subtract that from the max number of students in the Classestable, and display that the class if the max is reached either in awarning dialog box or as text on the page.I'm also populating a drop-down field on the registration form with theclass information from Classes. Confused yet?I don't know much about SQL or .ASP. Any help is appreciated.
View Replies !
Compare Like Data
Hello all,I am new to sql and have some Access experience.In sql, how do I: compare 2 identical tables, (except for data); then updatetable 1 with new data from table 2TIAJake
View Replies !
Compare Data Pls
was able to load data from 2005 to 2000 about 100+ tables. Now i'm only concerned about if data is the same the rows and data types (not sure if there is any difference). Any ideas how to test it? should i go row-by-row???? its over 100 tables. OH MY goood...
View Replies !
Data Compare
Hello All, How to I compare the data between prod and dev databases. I need to create sql script to list the new data and the modified data in development tables comparing with the production tables. Can anyone give me suggestions on how to do it? Thanks in advance, -S
View Replies !
Compare Data In Tables
I am trying to determine the changes an application makes to a database.The plan is to copy the existing schema (active) to a reference schema, runthe application and then diff the table data between the reference and thea active schema. I have found one software vendor who has a tool to dothis, but it will only do one table at a time (interactively); I have morethen 300 and will run this a few times.One other way of determining the changes, I guess, would be to log all sqlstatements (in order), but I don't know how to do this (either).Any pointers would be greatly appreciated.Leo
View Replies !
SQL Data Compare Error
Using SQL data compare i get the following error message:Could not allocate space for object '(SYSTEM table id: -701903460)' indatabase 'TEMPDB' because the 'DEFAULT' filegroup is full.The comparison is being run on one server between to databases. It hasbeen run several times but each time we get that error message, thetable ID number tends to change each time. We also had a problembefore where the 'TEMPDB' did not have enough space in its log filebut that was fixed by allowing the database to expand in size.
View Replies !
Parse Data And Then Compare
I am using MSSQL v8 (if that matters) The data looks like the following --------------------------- | PBP 20070420 2:26pm | --------------------------- Now the data in this field is not uniform it can be blank, a sentence or have a different pre fix, instead of PBP, but the date will be YYYYMMDD when it is supplied. I need to find all the dates that are within the last 10 months. How do I perform this task?
View Replies !
Compare Data And Add To Table
Hi all, Newbie here. I was wondering if any of you gurus could answer a question for me. Here is what I need to do (and I stress need): I have 2 tables. Table A has 3 columns, column 1 is unique customer numbers, column 2 is ticket numbers, column 3 is empty records. Table B has 2 columns, column 1 is unique customer numbers (same numbers, although not the same order as Table A) , column 2 is invoice numbers. I need to compare Table A where records in column 1 match records in column 1 in Table B. Where the records do match, I need to copy the records from Table B, column 2 to Table A column 3. Can anyone here help me with this, please? It would really get me out of a jam with this, since it is the last step I have to take to finally get this new app rolled out. Thanks a lot. Mark
View Replies !
Compare Data In Two Tables
I have a list of names in one table1 (nvarchar 100). I would like to find the names that don't exist in table2 I try using the following scripts, but this takes to long. Any suggestions??? SELECT DISTINCT G.NAME INTO ##TEMP FROM TABLE1 G, TABLE2 T WHERE T.NAME <> G.NAME THANKS.
View Replies !
Data Compare Tool
Does anyone have an opinion on specific “data comparison tools�? We are looking for something to use in our test or dev environments that will be able to compare snaps shots of the data in a database before verse after a test event. We have been able to record and compare data in specific tables but are learning that other tables were also being changed that we didn’t track. We want to be able to see all changes to a database. Michael
View Replies !
Compare Data In Two Table
Hi all, i have a question regarding data comparison in two tables in same database with same table structure. my table structure is like this CREATE TABLE xxgfs_gen_text_lookups_new ( lookup_type VARCHAR2(200) NOT NULL, region_code VARCHAR2(30), nongfs_value1 VARCHAR2(200), nongfs_value2 VARCHAR2(50), nongfs_value3 VARCHAR2(50), gfs_value1 VARCHAR2(200), gfs_value2 VARCHAR2(50), gfs_value3 VARCHAR2(50), retain_nongfs_for_dflts VARCHAR2(1), retain_gfs_for_dflts VARCHAR2(1), comments VARCHAR2(500), created_by NUMBER(15), creation_date DATE, last_updated_by NUMBER(15), last_update_date DATE, last_update_login NUMBER(15), Source_description varchar2(300), Oracle_description varchar2(300), defaults varchar2(300) ) ALTER TABLE xxgfs_gen_text_lookups ADD CONSTRAINT xxgfs_gen_text_lookups_uq_1 UNIQUE (lookup_type,region_code,nongfs_value1,nongfs_value2,nongfs_value3); i have some data in excel which i have uploaded using sql*loader using control card.Now i want to compare the data in both tables having same table structure only do any body having idea how to compare the data using storeprocedure. thanks in advance regs, Rajnish kumar
View Replies !
Need To Compare Data For What Is Missing...
I have a billing database with patient names in it. I received a tab delimited file from insurance plan of our roster of assigned patients. I now want to compare the insurance roster to our database to see who is missing. The roster is layed out like this (info jumbled to protect privacy): Eligibility List Sample Last Name First Name Date of Birth Gender Insured ID VW Acct # ALLEN CARRIE A4/16/1939FDH36664A572576-02 BAKER AMBER S11/24/1956FFXI2824C596439-02 BARKLOWLOREN R12/15/1956MKVF0092A588878-01 BRENNANPATRICIA A 1/14/1959FFXI8763A549675-02 BROWN MARTHA E8/14/1967FBD65508A366963-02 CALDWELL MICHAEL V 12/19/1969MLR500N2J595087-01 CLARK CYNTHIA A4/24/1971FVO600M8O596011-02 DEMPSTER SCOTT A 2/21/1976MCC85242A573371-01 DUNNE ANNETTE M10/26/1976FAE88375D598423-02 DUNNE CHRISTOPHER M 8/1/2021MBV81536A598423-01 I have loaded the text into an Excel Spreadsheet to work with it. I was able to query our patient profile data base to get people with this insurance plan...but of course the data is never an ideal match. For instance, some of the roster patients above have Middle Initials Concatenated to the First Name. In my database it is a mixed bag of missing initials, initials concatenated to first name or initials in separate Middle field. Thus a strict match on name is not going to work. Date of Birth should hopefully be valid between both data sources. Probably the best source of data to validate on would be the VW Acct# as I trust this to be the same in both sets of data. However in my patient data base it is buried in a note field preceded by a "Vital Works ID: " and then the number 602659-02. Generally it is the first part of the note field, but there could be additional notes preceding or trailing this Vital Works ID info. An example of the query I was able to pull from the patient data base is as follows: LastFirstMiddleDate of BirthGenderNotes ClarkLawrence J9/7/1955MVital Works ID: 7575-01 ClarkKayleeann NULL1/3/1955FVital Works ID: 7575-02 ColeCodyNULL8/19/1948FVital Works ID: 8771-02 snt ref req to ohms for impact appt tbs Sent ref req back to ohms for Impact-DX. CreaseyWadeL7/9/1988FVital Works ID: 602659-02 KennyRoyJ2/27/1953FVital Works ID: 602679-02 UttJannieC4/11/1984MVital Works ID: 602715-01 WestAliciaG9/9/1992MVital Works ID: 602736-02 WrightMinnieO2/17/1991MVital Works ID: 602736-03 YankeeDonald E10/27/1996MVital Works ID: 602762-03 YankeeStephana A4/4/2001FVital Works ID: 602762-04 How could I now construct a query that would tell me what patients were in the eligibility roster that didnt have a match in the patient database? I would like to then save that to Excel or somewhere that I could print it out from so I can have someone up date the database. Thanks for your help.
View Replies !
Data Transform And Compare With In A Query
Okay all I have a problem. I have two list of adresses and phonenumbers. I do not have control over the contents of the lists The onlyunique field between the two is the phone number. I need to be able toinner join the two lists on phone number.This would normally be straigt forward but the problem is that they areformated different and one of them does't even have a control on theformating.*Phone numbers are US phone numbers only.The one list (table) that does have a control uses this formatAAA3334444where AAA is the area code333 is the 3 digit prefix4444 is the four digit suffixthe second list (table) does not have any standardized formating andcan be filled with extraneous spaces, parentheses and dashes not tomention the leading 1 in some instances.I thought that I could do some kind of regular expression to do acomparison but I havn't as yet found a good resource to tell me how todo it or if it is even possible. Or maybe break up the one I know hasa standardized format into something like this:'%AAA%333%4444%' and doing my comparison that way. however It is veryimportant that only those list items in both list that are truly thesame place be listed.suggestions and solutions are apreciated
View Replies !
Need To Compare Data In SQL To Oracle / Import Changes
We will need to routinely import only changed data from an Oracle data base into a SQL database. So we need an agent that will 1) Compare data in both databases (From disparate tables) 2) Import only that which is changes or new. I am new to SQL server administration and am looking for a best practice method that we can be run on a weekly basis. I am open to using third party software solutions, but would prefer a native MS SQL 2000 solution. Can someone point me in the right direction? Thanks.
View Replies !
Compare And Insert Data On Two Tables
Hi everybody... looking for a way to compare and insert data on two tables.. I have two tables Tbl_email1 emailID email 1 info@sample1.com 2 info@sample2.com 3 info@sample3.com tbl_email2 emailID email 1 info@sample1.com 2 info@sample4.com 3 info@sample5.com I'm trying to compare tbl_email2 (email filed) with tbl_email1 (email field) if the record exist it it does nothing if not it adds the email field in tbl_email1 the result would be Tbl_email1 emailID email 1 info@sample1.com 2 info@sample2.com 3 info@sample3.com 2 info@sample4.com 3 info@sample5.com thanks
View Replies !
Compare Data In Two Tables With SQL Query?
I am trying to QA data being put into a SQL database by an outside source (from Excel) and therefore need to compare two (for the sake of simplicity) tables within a database to one another. The two tables should contain the same data, and the QA process is meant to find and report any discrepancies. The column names are slightly different. My question then is, is it possible to write a simple SQL query which will compare the data from the two tables and select only those rows where the data in any given column does not match? My data is mostly text, not numerical. I'm very new at using SQL and my knowledge of the query syntax is very basic. Thanks for any help in advance! ~Lacy
View Replies !
Matrix Compare Year Data
i am working on a report in matrix that will list out each year sales. e.g. 2006 2007 total product A $100 $120 $220 product B $230 $250 $480 is it possible to create some analysis in the matrix directly like "change % vs last year" so in above case, it will look something like: 2006 2007 chng % total product A $100 $120 20% $220 product B $230 $250 8.6% $480 thanks in advance.
View Replies !
How Do I Compare Data In 2 Different Tables Using Objrs?????
Hey all, got abit of a problem. Ive got a database where ive got 2 tables. I want to create a command which selects * from table 1 and if the same values are in table2 - it updates otherwise adds. Ive got a loop so i can run through the data records But i dont know how i check 1 objrs against another within an sql statement?? please look at coding example below: Im calling my data from the 2 tables using select commands- which is ok.- uses objrs1 and objrs2 Then im running my loop to check if data from table 1 is in table2: Dim Q For J = 1 to iRowCount2 Q= Q+1 Call select2Cmd( ) response.write(Q) response.write("</br>") Next response.end Heres my sql statement which checks this: sub select2Cmd( ) '======================================================================================== SET objrs5= SERVER.createObject( "ADODB.recordSet" ) dim sqlStatment sqlStatment = "SELECT * FROM [table2] WHERE [Name] = objRs2(Name) " objrs5.OPEN sqlStatment,objConn2,1,3 '======================================================================================== end sub I want to check the field [Name] in table2 with data from table1 and then loop to the next record. How do I do this? Please note the bold part is what im stuck on. Thanks
View Replies !
Compare Data On Replication Server
I need to compare data on two replication server I want to make a Stored Procedures for it Do I use LINKED SERVER to link the server together How do i get the name of the server on the Replication to compare the between two server. This will be used on more than one Replication Server It should auto get the name of the server to compare without the user input
View Replies !
How To Compare Data In Two Tables Using SSIS
Hi there- Here is my situation. I have got Table A in database DB1 and Table B in database DB2 with same table structure. I need to compare the data in table A and Table B and add the missed records in Table B. Can anyone help me on how to do this in a SSIS Package? Thanks, Sundar.
View Replies !
Open Source Tool: Data Compare
Hi all, Does anyone know any open source tool out there for these purposes? 1. Compare data rowcounts from two databases of the same structure.(On different servers). 2. Compare the structures of two databases.(On different servers). 3. Tool a developer can run on his/her desktop to capture a log of the database that his application is hitting. Again all, I am new to the database world. Thank you for all assistance.
View Replies !
Needs A SQL 2005 Data And Schema Compare Tool
Just wondering if there are any tools available for SQL Server 2005 which allow the comparison and scripting of data and schema between two databases. This is so that I can migrate between Dev, QA, and Live easily. A free tool would be best please.. Thanks in advance. Gaj
View Replies !
Cross Server Data Compare Using Findstr
I am trying to compare the data from 2 different servers. It is the same table. I did a select * and put the data into 2 different text files. I then used the Windows NT findstr utility to compare them to find the differences. I used the command findstr /v /i /x /g:file1 file2 > file3, where file1 is the larger table, and file2 is the smaller one. I want to find the records in file2 which are different or do not exist. I recieved no data back. I have ASCII characters in the text files. My question is does anyone know of a better way to do this, or how to make this command work?
View Replies !
Lookup For Compare The Source And Destination Data
Hi, I am new to SSIS can u help me out in this i have a source(Flat File) and target SQL Server 2005. Source has got 2 columns- Col1 and Col2 even target has got 2 col's Col1 being the PK. I have created a package that checks if target and if the record exist it updates and if it does not it inserts. My package looks like Source - lookup on target- Conditional split- Derived Column and then 2 OLE DB Destinations 1 for inserts and 1 for updates. I have created a relationship in lookup with col1 from source and col1 from target and col2 as lookup col and connected red output to 1 OLE DB for inserts and redirect rows to it. Green out put i have taken to conditional split and gave condition like Col2 from source is not equal to lookup col2. I run the package with this it is inserting new records but not updating it. I tried to add derived column after conditional split but lacks in writing expression that says update col2 records if they changed at source. Can u help me out in this scenario. I would appreciate if you could get back to me ASAP. Thanks
View Replies !
Lookup For Compare The Source And Destination Data
Hi i want to compare the source and destination data my source is Oledb source and destination is sql server destination I want to Compare the Data in destination with source and also want to add validation like iF Any row is there is destination but not in source delete that row if any row in source but not in destination Insert that row nd compare source with destination respect to Primary key if There is change in any column of that row Update that if not change than move to next record. Please tell me what is the sutable object and what to do Please Help me Out Thanks Sandeep
View Replies !
Compare Fields-two Flat Files-load Data
Hi All, I am totally new to SSIS and im in the learing phase. I have a requirement as below, I have two flat files (mainframe files), the structure i have given below, File1: 070113 12345johnk 23456james 1st row is header record which has got date in YYMMDD format and remaining rows have emp no and emp name File2: 070113 070113 070113 070113 contains 4 records which are dates. The requirement is to compare the header date in file1 with the 4 dates in file2, if they are equal then it should load all the records in file1 except the header into a table and if they donot match then it should log an err msg. Please could someone provide a lead on this. The files have same record length and fixed field delimited. Thanks in advance raj
View Replies !
Compare Data-type Xml In A Temp/variable/physical Table In MSSQL 2000
Does abyone know how to compare data-type xml in a temp/variable/physical table in MSSQL 2000? I tried this works in MSSQL 2005, Code Snippet create Table #t1 ([c1] int identity(1,1) not null, [c2] text) create Table #t2 ([c1] int identity(1,1) not null, [c2] text) Insert into #t1 Values('This is a test') Insert into #t2 Values('This is a test') Select * from #t1 Select * from #t2 Select * from #t1 where [c2] LIKE (Select [c2] from #t2) drop table #t1 drop table #t2 but not MSSQL 2000. Server: Msg 279, Level 16, State 3, Line 12 The text, ntext, and image data types are invalid in this subquery or aggregate expression. Is this true (from BOL)? Code SnippetIn comparing these column values, if any of the columns to be compared are of type text, ntext, or image, FOR XML assumes that values are different (although they may be the same because Microsoft® SQL Server„˘ 2000 does not support comparing large objects); and elements are added to the result for each row selected.
View Replies !
Deletion
Hi all, I have a table in xyz database and there is no column in table like creation_date or modified_date. The problem is I want to delete records which has been added in the table before 1st jan 2007. The size of table is 85 GB Immediate help would be appriciable. Regards, Frozen
View Replies !
Compare The Data In The 1st Row With Data In The 2nd Row
I need to be able to compare the value of a column in row 1 of table with the value of the same column in row 2 and then row 2 with row 3 and row 3 with row 4 and so on and so. I have to be able to do this with a sql query not in a stored proc or function, etc. Any ideas? I am using SQL Enterprise Manager Version: 8.0
View Replies !
Compare Old Data To New Data
I'm trying to compare two dates of data for loan balances. For example, if I want to know what the balances for branches A,B, and C are, today vs. yesterday, I want a report that has two columns: today and yesterday. And 3 rows: branches A, B, and C. The report is accurate, only if the 3 Branches exist today and yesterday. What if Branch B, for example didn't exist yesterday? How do I compare something to nothing? I wouldn't want total loan balances to be misrepresented because a particular branch didn't exist at one point in time when the comparison is ran. How do I go about this?
View Replies !
Restrict Deletion
What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query? Thank you in advance.
View Replies !
Deletion Of Duplicate Row
Hi Everyone,I have a table in which their is record which is exactly same.I want to delete all the duplicate keeping ony 1 record in a table.ExampleTable AEmpid currentmonth PreviousmonthSupplimentarydays basic158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00I want to delete 2 rows of above table.How can I achieve that.Any suggestion how can i do that.Thank you in advanceRichard
View Replies !
Replication Without Deletion
Hello there, We are currently setting up out production server to the following requirements: 1. Every month, delete records that haven't been changed in the last 90 days. 2. Replicate insert statements to a backup database which will keep track of all data, and act as an archive/data warehouse. The first step is easy, as it is just a script that checks the date of the last change on each row. However, the second step is a bit more tricky. We tried setting up replication between two test databases, but we ran into the following problem: Whenever old data has been deleted in the production database, the replication agent deletes it in the data warehouse database too. Is it possible to override or disable this, so data is only inserted/updated, and not deleted? No applications using the database deletes records, so database integrity should not be a problem. Thanks for your time, Ulrik Rasmussen
View Replies !
|
TRACKER
