/*   Sunday, 18 July 2010 12:51:00   User:    Server: SERVER   Database: TBSdata   Application: SQL Server Enterprise Manager*/
setuser 'dbo'
GO
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
COMMIT
EXECUTE sp_rename 'tServiceMnemonic', 'Tmp_tServiceMnemonic'
GO
BEGIN TRANSACTION
CREATE TABLE [tServiceMnemonic]
	(
 	[ID] int NOT NULL IDENTITY (1, 1),
	[ServiceCode] [varchar] (5) NOT NULL,
	[Prefix] [varchar] (5) NULL,
	[Pass] [varchar] (5) NULL,
	[Active] [bit] NOT NULL DEFAULT 1
	) ON [PRIMARY]
GO
COMMIT
IF EXISTS(SELECT * FROM tServiceMnemonic)
	 EXEC('INSERT INTO tServiceMnemonic(ServiceCode, Prefix, Pass)
		SELECT ServiceCode, Prefix, Pass FROM Tmp_tServiceMnemonic')
GO
IF EXISTS(SELECT * FROM tServiceMnemonic)
	EXEC('UPDATE tServiceID
		SET ServiceMnemonicID = MnemonicID
		FROM (SELECT tServiceID.ID AS "SID", tServiceMnemonic.ID AS "MnemonicID"
			FROM tServiceID
			LEFT JOIN Tmp_tServiceMnemonic ON tServiceID.ServiceMnemonicID = Tmp_tServiceMnemonic.ServiceID
			LEFT JOIN tServiceMnemonic ON Tmp_tServiceMnemonic.ServiceCode = tServiceMnemonic.ServiceCode) AS ServiceID
		WHERE tServiceID.ID = ServiceID.SID')
GO
/* DROP TABLE Tmp_tServiceMnemonic */
/* GO */

