February 3, 2011 at 8:04 am
Hi,
Every now and then (mainly when the need arises) I dabble with TSQL to achieve a particular goal. Sometimes I get by but most of the time I need to call on some TSQL experts to help me out and this is one of those times!
We have 2 databases residing on the same SQL named instance DatabaseA & DatabaseB. DatabaseA has a table called Users which pulls data in from an rdbms hosted on one of our Unix servers (DB & server managed by a 3rd party).
DatabaseB has only one table called tbl_users. This table is populated by using "insert into" of selective columns from DatabaseA.dbo.users (approximately 500k rows of data).
USE DatabaseB
GO
DROP TABLE dbo.tbl_users
CREATE TABLE dbo.tbl_users
(ID VARCHAR (8) NULL,
UserID VARCHAR (30) NULL,
PinNo VARCHAR (8) NOT NULL,
CSNTGroup INT NULL)
INSERT INTO DatabaseB.dbo.tbl_users (ID, UserID, PinNo)
SELECT ID, userid, pinno
FROM DatabaseA.dbo.users
WHERE (USERS.typecode Not Like 'NI%')
UPDATE DatabaseB.dbo.tbl_users
SET CSNTGroup=498
I would like to add in some means of checking so if DatabaseA goes offline then the code below does not execute. Any ideas how this is best achieved?
I thought about adding an IF Exists statement but got confused with the syntax when added to the tsql above.
IF EXISTS (SELECT * FROM sys.databases where name = 'DatabaseA' and state_desc = 'ONLINE')
Many Thanks.
February 3, 2011 at 8:14 am
I think your proposed solution should be fine. Just do something like this:
if exists(select ............)
begin
-----
----- Your SQL statements
-----
end
The probability of survival is inversely proportional to the angle of arrival.
February 3, 2011 at 8:32 am
Thanks for your reply!
So the TSQL now looks like this...
IF EXISTS (SELECT * FROM sys.databases where name = 'DatabaseA' and state_desc = 'ONLINE')
BEGIN
USE DatabaseB
GO
DROP TABLE dbo.tbl_users
CREATE TABLE dbo.tbl_users
(ID VARCHAR (8) NULL,
UserID VARCHAR (30) NULL,
PinNo VARCHAR (8) NOT NULL,
CSNTGroup INT NULL)
INSERT INTO DatabaseB.dbo.tbl_users (ID, UserID, PinNo)
SELECT ID, userid, pinno
FROM DatabaseA.dbo.users
WHERE (USERS.typecode Not Like 'NI%')
UPDATE DatabaseB.dbo.tbl_users
SET CSNTGroup=498
END
But I get the error below. any ideas?
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'DatabaseB'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'END'.
February 3, 2011 at 8:44 am
the "go" will not work inside a begin - end. begin end is only valid for the current batch.
do something like this:
use databaseb
go
if exists(select 1 from master.sys.databases where ......)
begin
end
The probability of survival is inversely proportional to the angle of arrival.
February 3, 2011 at 8:48 am
Thanks a lot for your help! Perfect!
Easy when you know how!
Cheers.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply