Synchronize two tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Synchronize two tables

Author

Topic

RMH1972
Starting Member

11 Posts

Posted - 2010-08-10 : 08:13:12

Hello,
I have a problem with synchronizing two tables in the same database
We have to change data in Access and with and with a programm named Marval.
the programm Marval placed 35 indexes on the Table dbo.TRAKIT_ASSET so Access can’t connect to this table. (Access has max 32 Indexes)
Now I made a copy of the table dbo.TRAKIT_ASSET with the command
SELECT *
INTO dbo.TRAKIT_ASSET_ACCESS
FROM dbo.TRAKIT_ASSET
And access can work with dbo.TRAKIT_ASSET_ACCESS table begorse there are no indexes in this table.(not copyed)

Now my problem, How can I synchronize all the data (not any indexes) between the two tables, this needs to happen every ½ hour.

If you have a better solution that can do this, also great.

Greetings
Robert

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-10 : 08:17:18

Don't know if it is possible in Access to connect to a view instead of a table.
But if it is possible then you can create a view.

CREATE VIEW TRAKIT_ASSET_ACCESS
AS SELECT * from TRAKIT_ASSET

No, you're never too old to Yak'n'Roll if you're too young to die.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-10 : 08:20:56

How much data is in the table? If the table is small it might be faster to just do a full reload of the table every 30 mins.

BEGIN TRAN

TRUNCATE TABLE dbo.TRAKIT_ASSET_ACCESS

INSERT INTO dbo.TRAKIT_ASSET_ACCESS
SELECT *
FROM dbo.TRAKIT_ASSET

COMMIT TRAN

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-10 : 08:22:20

And another thing...35 indexes?? Does that really make any sense?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

RMH1972
Starting Member

11 Posts

Posted - 2010-08-10 : 08:25:45

Hello Webfred,

Thanx for your fast responce

This is somthing I stared with, but I can add data to the table but i can't change data than i get an error message saying that i can't change the data.

RMH1972
Starting Member

11 Posts

Posted - 2010-08-10 : 08:34:57

Hello Lumbago

No, but Marval created the database and they say that it needs those 35 indexes.

I don’t think I get your solution
If I use this option than the data goes one way, right ?
TRUNCATE TABLE dbo.TRAKIT_ASSET_ACCESS
I need it to go two ways, so I can change the table dbo.TRAKIT_ASSET_ACCESS and dbo.TRAKIT_ASSET
Then they need to sync with another.
Or im I wrong ?

Greetings

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-10 : 08:39:09

I need it to go two ways
That wasn't clear in your first post.

No, you're never too old to Yak'n'Roll if you're too young to die.

RMH1972
Starting Member

11 Posts

Posted - 2010-08-10 : 08:40:19

Sorry, My error :-(

Greetings

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-10 : 08:41:13

It is a bad idea to change the data in both directions.

No, you're never too old to Yak'n'Roll if you're too young to die.

RMH1972
Starting Member

11 Posts

Posted - 2010-08-10 : 08:45:24

I know but, I don't see another solution than this.
but I know it is not changed very often it is for the CMDB part of the database.

Greetings

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-10 : 08:51:03

Syncing both ways can be a mess really...what do you do if the same records are modified within the same 30 min period? I think I'd delete a few of the indexes to get under the 32 limit.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

RMH1972
Starting Member

11 Posts

Posted - 2010-08-10 : 08:58:33

that is what I tryed to do but the company Marval don't want this, i can also sync every 5 minuts.

or an other solution but I don't know how, the view dusn't work

Greetings

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources