Copy Records From One Database To Another Database With Difference Of Records.
Apr 6, 2008
Hi,
I already submitted this type of question before and i receive reply. But unfortunately i found out errors when performing on my system.
My problem regarding to this one:
Suppose i have two databases with same tables with different records and I would like to copy the records from one database to another data and vice-versa. So that both the tables contains same number of records inside the tables.
Example:
Database1 (EmployeeTable) contains 6 records.
Database2 (EmployeeTable) contains 10 records.
It should copy only those records which is not present in each other database. No duplicate records.
Before i was recommend to use Primary key, if it is not present use index.
I have an application which stores records on a local SQL Express and I need to move the records to a SQL 2000 database. I have the SQL 2000 server linked in the Express Management Console (under Linked Servers). I'm trying to use a stored procedure to accomplish this, but get an error "Invalid object name 'ngtxa4-rsmsz-01.newpurchase.tblRequest'." Express uses a table named tblTRequest in the TempPurchase database, while 2000 uses a table named tblRequest in a NewPurchase database. Here is the stored procedure I'm using: USE [tempPurchase] GO /****** Object: StoredProcedure [dbo].[InsertRequestToMain] Script Date: 07/09/2007 08:54:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[InsertRequestToMain] AS BEGIN INSERT INTO [ngtxa4-rsmsz-01].newpurchase.tblRequest (fldRequestDate, fldRequiredBy, fldUserID, fldWorkAreaID, fldVendorID, fldClassID, fldEstimate, fldMemo, fldStatusID, fldStatusDate, fldSystemID, fldtmpRequestID, fldUpdateCode) SELECT fldRequestDate, fldRequiredBy, fldUserID, fldWorkAreaID, fldVendorID, fldClassID, fldEstimate, fldMemo, fldStatusID, fldStatusDate, fldSystemID, tmpRequestID, UpdateCode FROM tblTRequest WHERE (fldHold = 0) END
Any assistance with this would greatly be helpful. Thank you.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
how do you determine the date difference between the current record and the next record. i want to use the enddate on record 1 and calulate the time it took the next occurance to happen (start time) on record 2? and so forth.
I want to group the records on the time difference
declare @tbl as table(id int,intid int,val int,dt datetime) insert into @tbl select 1,1,10,'03/31/2006 15:05:22' union all select 2,1,12,'03/31/2006 15:10:22' union all select 3,1,15,'03/31/2006 15:15:22' union all select 4,1,12,'03/31/2006 15:25:22' union all select 5,1,8,'03/31/2006 15:30:22' union all select 6,1,6,'03/31/2006 15:35:22' union all select 7,1,4,'03/31/2006 15:40:22' union all select 8,1,3,'03/31/2006 15:45:22' union all select 9,1,10,'03/31/2006 15:50:22'
declare @tbl1 as table(intid int,Tm int,val int) insert into @tbl1 select 1,5,10
I want a output such that when the val in @tbl goes below the val in @tbl1 for the Tm mentioned in @tbl1 then the time difference should be shown.For example record 1 it starts with 10 the records remain more than 10 till record number 5.From 5 the records remains lower than 10 till record number 9.So I need to show the the time difference from record number 5 till 9. But there is a catch.In @tbl1 there is column named Tm.The time difference sould be calculated only if the diff more than Tm value in @tbl1. For example if the value of Tm is changed to say 25 then the there is no need to show the time difference since the time difference value from record 5 to record 9 is less than 25. Hope I am clear.
I have a data set like so:UTC_TIME Timestamp NodeID Message FlagLineStation11/19/2005 10:45:07 1132397107.91 1 3 5 1028103411/3/2005 21:05:35 1131051935.20 2 3 5 1009104311/25/2005 21:12:16 1132953136.59 3 3 5 10371049I added the UTC_TIME column in as aconversion of the unix timestamp inthe TIMESTAMP column.Keeping things simple and straightforward, I need to be able tocalculate the difference from one record to the next (ordered byTIMESTAMP or UTC_TIME) and output the result into another column in thetable.NODEID is the unique id.First, what is the function to do so if, say, I only wanted tocalculate the difference between 2 records as just a basic SELECTstatement. That way I can answer quick question based on any one or twoNODEID's.Second, how would I further that to continually calculate (as statedabove)?WOuld this be a stored procedure? A trigger? A cursor?I am learning as I go here. Any help is greatly appreciated.R.
I am having a table where i have the following columns where the date format is dd/mm/yyyy Purchase Description From_Date To_Date------------------------------- --------------- ----------------Desktop 2/2/2007 2/3/2007Mouse 2/1/2007 28/1/2007Laptop 5/1/2008 15/3/2008Speaker 4/1/2008 21/1/2008 My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure? Please provide me with full stored procedure Thanx in advance
My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure?
hi all, How do i avoid duplicate records on my database? i have 4 textboxes that collect user information and this information is saved in the database. when a user fills the textboxes and clicks the submit button, i want to check through the database if the exact records exist in the database before the data is saved. if the user is registered on the database, he wont be allowed to login. how can i acheive this? i thought of using the comparevalidator but i'm not sure how to proceed. thanks
I am using this code to delete records from a table in the database that has two fields (both are primary keys) I am not getting any error, but nothing gets deleted. Thanks for the help. Code:protected void btnRemove_Click(object sender, EventArgs e) {
Hello. I need to design a database structure that will keep ALL table records and not delete anything. Every record will have associated users, but I need to keep perminant track of what every user did to any record. Can you suggest a reading or backup method? Should I keep older versions of a record within the same table or move them to another? Or backup to log files? Any suggestions? Thank you in advance - Jasem
i have duplicate records, but they have unique ids.
the duplicates resulted as a result of using a view to enter the data using BCP to load data from text file that had many dulicate records as part of consolidting the data i would like to get rid of the duplicate
is there some way of checking if the Name, Address, Telephone and Image are the same as on a record with another then remove them.
How to use db-lib to update/insert database records without using SQLlanguage. I want to change the value of the data individually withoutplugging in the new values in the SQL language then execute it. Theperfect situation for me is loop through the retrieved records thenedit the values individually until EOF.Thanks,Carlo
I have a question about new record insertion. I have a SQL Database that I have cofigured as a publisher for a mobile database. I want to be able using the PDA to insert new Customers and synchronize the CE database with the SQL DB. But I encounter a problem with the column field rowguid that SQL inserts as a unique indentifier during the publication. It cannot accept null values and I can't insert a value using the keyboard since the value is generated by SQL.
So my impression is that you cannot use PDA to insert new records, only to update old ones. Is that true or I miss something??
my table does not have primary key so i created a seperate index on each of the table.
I used the recommended tablediff utility and it works successfully. But its only show the difference of records in each table and does not copy rows from source to destination and destination to source table. I was expecting database1.dbo.table1 contains same records as in database1.dbo.table2.
C:Program FilesMicrosoft SQL Server90COM>tablediff /sourceserver kashif-pcs qlexpress /sourcedatabase AB /sourcetable table1 /destinationserver kashif-pcsq lexpress /destinationdatabase CD /destinationtable table2 Microsoft (R) SQL Server Replication Diff Tool Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values: /sourceserver kashif-pcsqlexpress /sourcedatabase AB /sourcetable table1 /destinationserver kashif-pcsqlexpress /destinationdatabase CD /destinationtable table2
Table [AB].[dbo].[table1] on kashif-pcsqlexpress and Table [CD].[dbo].[table2] on kashif-pcsqlexpress have 5 differences. Err Sno Src. Only 101 Src. Only 102 Dest. Only 103 Dest. Only 104 Dest. Only 105 The requested operation took 0.466767 seconds.
Can you write a short script for my problem, just like comparison of database1.dbo.table1 compares in database2.dbo.table2 and which ever records not present it should copy those and vice-versa.
It means Database1.dbo.table1 contains 5 records Database2.dbo.table2 contains 5 records
I have an incident reporting management application. People are supposed to report incidents by this application and every time some one reports an incident, they also select thier employee#(reqiured field). so how can write an sql statement that returns only the Top 5 incident reporters i.e going by employee number. Iam thinking of applying a COUNT function on the incident_id and grouping by Employee# but then how do i make sure that only the top 5 incident reporters are returned.
How do I select data from another database name (in the same SQL SERVER LOCATION) and its child table? SELECT user_id FROM [10.10.20.3].CC_host.usr_table Where(email LIKE N'%@%' And email LIKE N'%usa.com') Order By user_id
Hi, I am having three text box which accepts user data & insert that in sql database. But I am not able to do this , I think this is the simplest of all . Can somebody plz tell me how this can be done from scratch that is connection string & settings in web.config ? Plz guide using C#ThanksRegards,-Sunny,
VWD 2005 Express. Visual Basic. SQL Server 2005. I know how to set up SqlDataSources and their insert, delete, update, and select commands. I also have code for querying a Sql table and populating a dataset and scanning the dataset for values. However, I do not know how to modify records in a Sql table programmatically. Here is what I need to do: I need to open a Sql table. I need to process record bny record to check a particular field for a particular value. If the field has the particular value, I need to change it and write the record back to the table with the modification. The table is named "SystemUser" and the field is call "SystemUserTypeId." Can anyone provide me with sample code that would accomplish this? Thanks for the help.
Hi, all I got one stupid question in mind. The idea is that I want my SQL database to install into another computer. The best way for me is I want to create a script file for creating tables, triggers, stored procedure. But I want all the SQL statement that insert all the records in to each table too.
Does anyone have a solution for me? Or there be other way? I am always waiting for ur suggestion.
I have a SQL Server 2K db with some 10 tables and I want to setup a nightly job to truncate all db records which are more than 15 days old. Can anyone provide me with steps involved? Any help will be highly appreciated.
I need to be able to find certain data as the user has submitted that data twice and delete one record,except that have hundreds of tables and don't know the table where she would have submitted the data, but I have some other key info that I can start with.
My question is,how do i select a record from the database if I don't know the table it comes from?
I have 1 table. In that table there are thousands of records. I have an ID that is autonumbered for a unique id.
What i need to do is at any given time select as many records from that table that have a certain Forein Key in it. Take all those records and copy them back into the same table but at that time i need to change some of the values including the Forien Key and the name.
Is there a bulk way to do this without having to loop? If so can you show me.
If not can someone show me the best way to get all the records and copy them back into its own talbe with a new Forien Key and Name.