Synchronize two table - 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

General SQL Server Forums

New to SQL Server Programming

Synchronize two table

Author

Topic

gr8.jain
Starting Member

5 Posts

Posted - 2009-01-23 : 07:37:16

Hi all,
I am new to this forum.
I am using SQL Server 2005.

My need is that i want a way to synchronized two tables within sql server. both the table has same column in it (off course same data type as well), out of one is temporary("tamp_tab") table, which has new(updated data) And another one is basic table(say "ashish_tab") which need to be synchronized with "temp_tab" table.

I need some way to synchronized (update, insert and delete record of "ashish_tab") but within a very very very LESS possible time. both or either table may has 1 million of records.

please any one help me in this

-Ashish

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 07:42:37

some thing like this

update a
set a.col1 = t.col1,
a.col2= t.col2
.....
from tamp_tab t
inner join ashish_Tab a < join conditions>

gr8.jain
Starting Member

5 Posts

Posted - 2009-01-23 : 08:01:12

but i am not knowing how many columns in both the tables. also i let you know that each time my both the table are having diffrent columns when i call synchronisation

More clearly: i am using vb code for creating temp(in all case the name is temp_tab) table with same structure as in sync table(one case is ashish_tab) and insert latest data in temp_tab via my vb.net application.
Because i have 100's of table to synchronize and each table may contain any no. of record say 1000 row or 10 million row.
thats why i am not synchronising tables from vb.net application (it take lots of time and i want it as fast as possible)
e.g. - form vb.net code i get record from my source database one by one and check it in with sql table for insert /update/delete but this take time like 465000 record it take 2.5 hours(because checking is there in vb.net code) so fastest way is i am just inserting data in tamp_tab (take 30 minute only) and then i want to sync. table with tamp_tab in sql itself (i think sql did it fast)
i am not able to create identity column. also not make primary or unique key becuase data i have has iterated record for same field (which needed and not truncated).

so friend this is the scenario here

now can any one help me in this...

i just want my table synchronized as fast as in sql server via some script or procedure or trigger.

-Ashish

gr8.jain
Starting Member

5 Posts

Posted - 2009-01-23 : 08:48:24

please help me

-Ashish

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-23 : 08:54:24

This article should help you for sure.
http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

gr8.jain
Starting Member

5 Posts

Posted - 2009-01-23 : 09:47:39

quote:
Originally posted by sakets_2000

This article should help you for sure.
http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

thanks for link
may it help i try to use it with union all
will update if get success

if any more same like this will appreciate

-Ashish

gr8.jain
Starting Member

5 Posts

Posted - 2009-01-24 : 02:13:59

Hey I got one of the fastest way via join query to insert data.
Now I am able to insert and delete my data from temp table to main table. here is the insert query-

insert into dbo.myMain(MANDKENN,VERSIONN,SN,NUMMER,...<column list>..) select dbo.temp.MANDKENN,
dbo.temp.VERSIONN,dbo.temp.SN,dbo.temp.NUMMER, ...<column list>.. from dbo.temp left outer Join dbo.myMain on 
dbo.temp.SN = dbo.myMain.SN and dbo.temp.VERSIONN = dbo.myMain.VERSIONN 
where dbo.myMain.SN is null or dbo.myMain.VERSIONN is null

this insert only those record from temp which not exits in my main table
now i want to write update (query) main table from temp for those row which exist in my main table where the temp.versionn (numeric 22,0) > main.versionn only

so need help from your side

-Ashish

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-24 : 08:43:51

upto sql 2005 you can do this as follows


--insert new records
INSERT INTO ashish_tab
SELECT fields...
FROM temp_tab tmp
LEFT JOIN ashish_tab a
ON a.PK=tmp.PK
WHERE a.PK IS NULL


--update already existing
UPDATE a
SET a.Field1=tmp.Field1,
a.Field2=tmp.Field2,...
FROM temp_tab tmp
INNER JOIN ashish_tab a
ON a.PK=tmp.PK


--delete non existent records
DELETE a
FROM ashish_tab a
LEFT JOIN temp_tab tmp
ON tmp.PK=a.PK
WHERE tmp.PK IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources