Updating a database

I have two table which have exactly the same tavle structure. I want to append data in one table using data from another table. So far I have implemented this using a loop but it is a very lengthy process. Is there a better way of doing it?

I'm using VB6 and MS Access 2000

what you mean by "I want to append data in one table using data from another table"

You want to update the data using the data in the 2the table (kind of synronize) or really append the records from table 2 into 1.

The 2the is easy, just u and insert command.
like

Code:

---

SELECT * FROM [TableDestination] SELECT * FROM [TableSource]

---

This command will append all record from table 2 into table 1

Code:

---

SELECT * FROM [TableDestination] SELECT * FROM [TableSource] WHERE [TableSource].[LinkID] <> [TableDestinaction].[LinkID]

---

This command will append only the records from table 2 into table 1 that do not exist in table 1.

Code:

---

UPDATE [TableDestination] SET [TableDestination].[Field1] = [TableSource].[Field1], [TableDestination].[Field2] = [TableSource].[Field2] FROM [TableSource] INNER JOIN [TableDestination] ON [TableSource].[LinkID] = [TableDestination].[LinkID]

---

This code should update all records in table 1 that with the data from table 2 if the records exists in both tables.


With a mix of these commands you can create a total updater process.

Thanx a lot man. You really understood what I wanted but it looks like this will only work if both tables are on the same database. Let me exactly tell you what I want to do:

I have a function that takes a recordset as a parameter.

E.g UpdateServer(rsData as Recordset)

My system is a client server model system and this function is the server method.It is called by the client to update the table on the server with the data from the table on the client side. This tables have exactly the same structure but pls note that they reside on different databases.

Thanx a lot in advance.

Have you tried using the linked servers option in MSSQL? :)

Cheers!
Abhijit

Quote:

---

Originally posted by pml
Thanx a lot man. You really understood what I wanted but it looks like this will only work if both tables are on the same database. Let me exactly tell you what I want to do:

I have a function that takes a recordset as a parameter.

E.g UpdateServer(rsData as Recordset)

My system is a client server model system and this function is the server method.It is called by the client to update the table on the server with the data from the table on the client side. This tables have exactly the same structure but pls note that they reside on different databases.

Thanx a lot in advance.

---

Code:

---

SELECT * FROM [TableDestination] SELECT * FROM [TableSource]

---

oops this one is wrong must be

Code:

---

INSERT INTO [TableDestination] SELECT * FROM [TableSource]

---

:)


If your tables are on different databases use

Code:

---

INSERT INTO [DB1].dbo.[TableDestination] SELECT * FROM [DB1].dbo.[TableSource]

---

Its also possible to use tables on different server, but then u must first link the sql server. I think thats the most advanced link there is.

Code:

---

[SQLServer].[Databasename].[Databaseowner (on sql most cases dbo)].[Tablename].[Fieldname]

---

for example

Code:

---

SELECT [Server1].[DB1].[dbo].[Table1].[Field1], [Server1].[DB2].[dbo].[Table1].[Field1], [Server2].[DB1].[dbo].[Table1].[Field1]
FROM [Server1].[DB1].[dob].[Table1], [Server1].[DB2].[dob].[Table1], [Server2].[DB1].[dob].[Table1]
WHERE [Server1].[DB1].[dob].[Table1] = [Server1].[DB2].[dob].[Table1] AND [Server1].[DB2].[dob].[Table1].[FieldID] = [Server2].[DB2].[dob].[Table1].[FieldID]

---

This is a pretty advanced query, that links 2 databases on the same server with a database on a 2the server.

:) Greets Nightmare :)