How to synchronize 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)

How to synchronize tables?

Author

Topic

nick.dryaev
Starting Member

13 Posts

Posted - 2008-06-26 : 10:49:44

Hi,
I have the problem to synchronize two tables,
ex.: table1 and table2.
I want to copy values from certain column from table1 to certain column in table2. The link between the columns exist in table3.
Tables structure:

CREATE TABLE [dbo].[Table1](
[NameA_t1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NameB_t1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NameC_t1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NameD_t1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table2](
[NameA_t2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NameB_t2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NameC_t2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NameD_t2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table3](
[ColumnNames1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColumnNames2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

insert dbo.Table1
select 1, 1, 1,1 union all
select 2, 2, 2,2 union all
select 2, 2, 2,2

insert dbo.Table3
select 'NameA_t1', 'NameA_t2' union all
select 'NameB_t1', 'NameB_t2' union all
select 'NameC_t1', 'NameC_t2'

What I need is to select value from "table1.NameA_t1" and insert it to "table2.NameA_t2" because in table3 we have link between "NameA_t1" and "NameA_t2".

nick.dryaev
Starting Member

13 Posts

Posted - 2008-06-26 : 10:52:36

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 11:00:56

Can i ask you why you desined your db like this?You need to use dynamic sql if you want to achieve this requirement.something like:-

DECLARE @SourceCol varchar(100),@DestnCol varchar(200),@ID int

SELECT @ID=MIN(t.Row_No)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [ColumnNames1],[ColumnNames2]) AS Row_No,*
FROM Table3)t

WHILE @ID IS NOT NULL
BEGIN
SELECT @SourceCol=[ColumnNames1],@DestnCol=[ColumnNames2]
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [ColumnNames1],[ColumnNames2]) AS Row_No,*
FROM Table3)t
WHERE t.Row_No=@ID

Set @Sql='INSERT INTO Table2 ('+@SourceCol+')
          SELECT ' + @DestnCol + ' FROM Table1'

EXEC (@Sql)

SELECT @ID=MIN(t.Row_No)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [ColumnNames1],[ColumnNames2]) AS Row_No,*
FROM Table3)t
WHERE t.RowNo>@ID

END

nick.dryaev
Starting Member

13 Posts

Posted - 2008-06-26 : 11:28:05

quote:
Originally posted by visakh16

Can i ask you why you desined your db like this?You need to use dynamic sql if you want to achieve this requirement.something like:-

I did not desine database like this, I just have to upgrade existing software.

I am getting the following error when trying to execute the script.
But in line 1 or line 2 we do not have any column names?

Msg 207, Level 16, State 1, Line 2
Invalid column name 'NameA_t2'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NameA_t1'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'NameB_t2'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NameB_t1'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'NameC_t2'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NameC_t1'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 12:30:50

quote:
Originally posted by nick.dryaev

quote:
Originally posted by visakh16

Can i ask you why you desined your db like this?You need to use dynamic sql if you want to achieve this requirement.something like:-

I did not desine database like this, I just have to upgrade existing software.

I am getting the following error when trying to execute the script.
But in line 1 or line 2 we do not have any column names?

Msg 207, Level 16, State 1, Line 2
Invalid column name 'NameA_t2'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NameA_t1'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'NameB_t2'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NameB_t1'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'NameC_t2'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NameC_t1'.

sorry it should be other way round


Set @Sql='INSERT INTO Table2 ('+@DestnCol+')
          SELECT ' + @SourceCol + ' FROM Table1'

nick.dryaev
Starting Member

13 Posts

Posted - 2008-06-27 : 04:10:22

It works!!!

Thank you very much.

But now I am getting this in my destination table:

2 NULL NULL
3 NULL NULL
1 NULL NULL
1 NULL NULL
2 NULL NULL
2 NULL NULL
NULL 2 NULL
NULL 3 NULL
NULL 1 NULL
NULL 1 NULL
NULL 2 NULL
NULL 2 NULL
NULL NULL 2
NULL NULL 3
NULL NULL 1
NULL NULL 1
NULL NULL 2
NULL NULL 2

Is it possible to get something like this:

2 2 2
3 3 3
1 1 1
1 1 1
2 2 2
2 2 2

Do I have to first insert and update after?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-27 : 10:59:18

quote:
Originally posted by nick.dryaev

It works!!!

Thank you very much.

But now I am getting this in my destination table:

2 NULL NULL
3 NULL NULL
1 NULL NULL
1 NULL NULL
2 NULL NULL
2 NULL NULL
NULL 2 NULL
NULL 3 NULL
NULL 1 NULL
NULL 1 NULL
NULL 2 NULL
NULL 2 NULL
NULL NULL 2
NULL NULL 3
NULL NULL 1
NULL NULL 1
NULL NULL 2
NULL NULL 2

Is it possible to get something like this:

2 2 2
3 3 3
1 1 1
1 1 1
2 2 2
2 2 2

Do I have to first insert and update after?

yeah thats a solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources