|
|
Posted by teddysnips on 11/01/06 09:25
I have a need to create a database, and then populate it. However, the
code below doesn't work as I hoped it might (it creates the table in
the "master" database, which is Not A Good Thing). I know already
(thanks Tony!) that if you use Dynamic SQL for the USE command, then
the subsequent operations need to be Dynamic SQL as well, which is a
pity since there are over 11,000 lines of it and I don't really fancy
debugging it!
Does anyone have a cunning plan? In a nutshell, I would like to be
able to:
1. Create a new database with a derived name - as in the case below a
name based on the year, though it might be month as well.
2. Create and populate tables in this new database.
These operations would ideally be running from a scheduled job.
Any thoughts?
TIA
Edward
====================================
USE MASTER
DECLARE @DBName VARCHAR(123)
SET @DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)
if not exists(select dbid from master.dbo.sysdatabases where name =
@DBName)
exec('CREATE DATABASE ' + @DBName)
else
raiserror('Database already exists.',3, 1)
EXEC ('USE ' + @DBName)
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[TestTable]
GO
CREATE TABLE [dbo].[TestTable] (
[TestID] [int] NOT NULL ,
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Navigation:
[Reply to this message]
|