How to create 20 databases at a time in sqlserver?

Question

Hi, I have written the following procedure to create a series of 20 databases. But it is not creating them. The database is not created even when I go to my H: drive and didnot find the ldf and mdf named bharath1 bharath2 like...

What did I do wrong with this?

USE master
GO

DECLARE @command varchar(1000), @i int

while @i < 5

  SET @command = 'CREATE DATABASE Bharath' + CAST(@i AS varchar) + '
     ON ( NAME = ''Bharath_dat'',
  FILENAME = ''H:\MSSQL10.MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\DATA\Bharath' +
  CAST(@i AS varchar) + '.mdf'', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )

  LOG ON ( NAME = ''Bharath_log'', 
           FILENAME = ''H:\MSSQL10.MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\DATA\Bharath' +
    CAST(@i AS varchar) + '.ldf'', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )'

  EXEC (@command)

  SET @i = @i + 1

GO

I don't have any idea in SQL Server stored procedure as of my knowledge and with somebody support i did like this. But i am not finding any databases.

Any help greatly appreciated.

Answer 1

OK, I don't see the BEGIN and END block right after the WHILE loop which may cause SQL to do an endless loop to run a SET @command statement only.

You need to enclose the WHILE with BEGIN and END...

DECLARE @command as varchar(1000), @i int
SET @i = 0
WHILE @i < 5
BEGIN
  SET @command = 'CREATE DATABASE Example' + CAST(@i AS varchar)
  EXEC(@command)
  SET @i = @i + 1
END

This works for me. (SET @i = 0 is a must)

Answer 2

YOu need to initialise @i, add this after your declare:

SET @i = 1

Answer 3

There's a few things you've missed - you first of all need to put your several SQL command that you want to execute over and over again into a BEGIN......END block.

Second, you need to initialize your @i counter! Otherwise, it'll never get into that WHILE loop in the first place.

And thirdly, your CREATE DATABASE call was wrong - you need to specify a filegroup where to create the database (CREATE DATABASE (name) ON (filegroup) .......).

Here's my code:

USE master
GO

DECLARE @command varchar(1000), @instanceName VARCHAR(100), @i int

SET @i = 1

WHILE @i <= 5
BEGIN
  SET @instanceName = 'Bharath' + CAST(@i AS VARCHAR(10))

  SET @command = 'CREATE DATABASE ' + @instanceName + 
     ' ON PRIMARY ( NAME = N''' + @instanceName + ''', ' +
     'FILENAME = ''H:\MSSQL10.MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\DATA\' +
     @instanceName + '.mdf'', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) ' +
     'LOG ON ( NAME = N''' + @instanceName + '_LOG'', ' + 
     'FILENAME = ''H:\MSSQL10.MSSQLSERVER\MSSQL10.MSSQLSERVER\MSSQL\DATA\' +
     @instanceName + '.ldf'', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )'

  EXEC (@command)

  SET @i = @i + 1
END
GO