Dynamic Update Statement (help!)

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 2000 Forums

Transact-SQL (2000)

Dynamic Update Statement (help!)

Author

Topic

Dfly
Starting Member

11 Posts

Posted - 2006-05-08 : 04:55:32

Hi Guru's,

i have the following task to accomplish:
I must sync tables from DB Server XX to another
DB Server YY.

I do a query on the xx server to get the changed/new records.
I have to insert the new records on target YY server, but must
do an update if Id's already exist.

To make this as dynamic possible i must create a stored procedure which will will create an Update statement by only providing the source table name!

I know how to get the column names from source:
(example from forum)
declare @columns varchar(4000)
select @columns = ''
select @columns = @columns + column_name + ', ' from Information_schema.columns
where table_name = @tableName
select @columns = left(@columns,len(@columns)-1)

But don't know how to construct an update statement
by looping record by record from the changed/new select table.

so codewise it should be sort like this:

Dim sqlUpdate as string = "UPDATE DestinationTable SET "
Dim sqlUpdate2 as string

for i = 0 to updatedrecords.count
singlerecord = updatedrecords(i)
for each column in Singlerecord
sqlUpdate2 = sqlUpdate2 + dynamicColname + "=" + column .Columnvalue1
Next
next

sqlUpdate =sqlUpdate + " WHEre id = " + id

The above code is not right, but it only states what i want
to accomplish in a SQL Server Stored Procedure.

Is this possible and the right way to accomplish my task?
Thank you for helping!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-08 : 05:27:20

Is this what you want ?


declare	@sql	varchar(1000),
	@source	varchar(100),
	@target	varchar(100),
	@pk	varchar(100)

select	@source	= 'Test',
	@target = 'Test2',
	@pk	= 'ID'

select	@sql = 'UPDATE t' + char(13)
select	@sql = @sql + 'SET' + char(13)

select	@sql = @sql + 't.' + COLUMN_NAME + ' = ' + 's.' + COLUMN_NAME + ',' + char(13)
from	INFORMATION_SCHEMA.COLUMNS
where	TABLE_NAME	=  @source
and	COLUMN_NAME	<> @pk

select 	@sql = left(@sql, len(@sql) - 2) + char(13)
select	@sql = @sql + 'FROM ' + @source + ' s INNER JOIN ' + @target + ' t' + char(13)
select  @sql = @sql + 'ON s.' + @pk + ' = ' + 't.' + @pk

select 	@sql

Dfly
Starting Member

11 Posts

Posted - 2006-05-08 : 06:24:19

Wow!

Thanx alot khtan !!!!

Just one question. @source must be a subset from the source table.
Can i make the @source a cursor or temp table and use that?
Can you suply me with an example?

Thanx alot khtan!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-08 : 07:09:16

[code]
declare @tables table
(
source_table varchar(100),
target_table varchar(100),
primarykey varchar(100)
)

declare @sql varchar(1000),
@source varchar(100),
@target varchar(100),
@pk varchar(100)

declare c_cursor cursor for
select source_table, target_table, primarykey
from @tables

open c_cursor
fetch next from c_cursor into @source, @target, @pk
while @@fetch_status = 0
begin
select @sql = 'UPDATE t' + char(13)
select @sql = @sql + 'SET' + char(13)

select @sql = @sql + 't.' + COLUMN_NAME + ' = ' + 's.' + COLUMN_NAME + ',' + char(13)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @source
and COLUMN_NAME <> @pk

select @sql = left(@sql, len(@sql) - 2) + char(13)
select @sql = @sql + 'FROM ' + @source + ' s INNER JOIN ' + @target + ' t' + char(13)
select @sql = @sql + 'ON s.' + @pk + ' = ' + 't.' + @pk

print @sql
-- exec (@sql)
fetch next from c_cursor into @source, @target, @pk
end
close c_cursor
deallocate c_cursor
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-09 : 02:00:27

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Dfly
Starting Member

11 Posts

Posted - 2006-05-09 : 03:40:49

Thank you! you have been a really good help

Dfly
Starting Member

11 Posts

Posted - 2006-05-09 : 03:56:00

madhivanan and khtan,

Could you provide me with any help by accomplishing my task to
sync tables with a remote SQL Server?

What i do is:
I perform a query to get the updated or newly inserted records.
This result i put in a temporary table. Then i want to step to this table record by record to check if this record exists on target server. If it does, i do an update, if it doesnt i do an insert.

Is this the right way to sync or can i use for instance DTS to do this? I want to do this in stored procedures.
Any help on this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-09 : 06:22:38

you can use DTS or Linked Server. Refer to Books Online on how to setup the Linked Server.

If you are using Linked Server, you can use select statement to retrieve the newly inserted records

"I perform a query to get the updated or newly inserted records. This result i put in a temporary table"

insert into #your_temp_table(col1, col2, col3 . . .)
select cola, colb, colc . . . 
from   [RemoteServer].dbname.dbo.table_name
where  . . .

"Then i want to step to this table record by record to check if this record exists on target server. if it doesnt i do an insert"
Do don't have to step into this table. You can process the temp table via set based operation

insert into target_table(cola, colb, colc, . . )
select col1, col2, col3 . . .
from   #your_temp_table t
where  not exists (select * from target_table x where x.pk = t.col1)

"If it does, i do an update"

update u
       set  cola = col2,
            colb = col3,
            . . .
from   target_table u inner join #your_temp_table t
       on u.pk = t.col1
where  cola <> col2
or     colb <> col3
or     colc <> col4

Dfly
Starting Member

11 Posts

Posted - 2006-05-09 : 10:25:54

Do you know how i disable identity_insert on remote server table?

SET identity_insert [REMOTESERVER].[CATALOG].dbo.[MYTABLE] ON

This generates:

The object name 'REMOTESERVER.CATALOG.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources