Providing DB Name on the fly in the query

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)

Providing DB Name on the fly in the query

Author

Topic

eziners
Starting Member

1 Post

Posted - 2005-06-13 : 12:36:36

Hi guys,

I have a list of databases that I store in a central database. Now, when I have to run an INSERT or UPDATE, I retrieve all the DB Names from the central database and then form a SQL String and EXECUTE it using a CURSOR to go through each database. All the databases have the same table names and structures, just different names.

Is there an easier way to run the INSERT or UPDATES without having to form a SQL String? Right now I do something like this

Set @vSQL = 'select * from ' + @dbName + '.dbo.Person'
Exec(@vSQL)

Issue with this is that if I want to insert a new entry and find its @@Identity, this will not give me the result.

Thanks.

SJB

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-13 : 13:23:39

Did you try IDENT_CURRENT?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-13 : 13:36:16

instead of manually performing actions to all databases, why don't you perform them in one database and replicate to other DBs?


create table myTable (rowid int identity(1,1), col1 int)
go
declare @vSQL nvarchar(200)
	,@scope_identity int
Set @vSQL = '
insert myTable (col1) values (1) 
set @ident = scope_Identity()
'

exec sp_executesql @vSQL, N'@ident int output', @ident = @scope_identity output
select @scope_identity
go
drop table myTable
go

Be One with the Optimizer
TG

EDIT:
Ident_current('myTable') works too but I suppose you could end up with an identity from a different, concurrent insert.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources