Chronicals of… a developer in Johannesburg

Ever had an excel document with all the data that you want to import into a SQL database table? Bulk insert is your friend.

I know some of us (me included) are sometimes scared of handling external files in SQL. But this is really easy.

Best thing to do is get your excel document, export it as tab delimited, and import that into a table, temp table, or variable table within your sql query.

The main issue to note is that the path in the bulk insert is from the sql server’s “point of view”. So if you connect to a server through the network and need to access a file on you PC, then share it and grant the correct security permissions.

Sometimes we need to restore a database off someone else’s BAK file. If you dont do it often or have never done it, then here is how.

I like SQL Management Studio.

1. Create a new database with the name you want.
2. Right click that database and then go to Tasks -> Restore -> Database
3. In the new dialog, enter your database name, and select the backup file at the bottom.
   — Now here comes the trick —
4. Go to the options tab and select “Overwrite the Existing Database”, and select new paths to restore the files to (usually your default SQL folders).

The script to do this can be generated and here it is:

RESTORE DATABASE [q2g_dev]
FROM  DISK = N’D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\q2g_live MS SQL 2005.bak’
WITH  FILE = 1,
MOVE N’quip2go_Data’ TO N’D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\q2g_dev.mdf’,
MOVE N’quip2go_Log’ TO N’D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\q2g_dev_log.ldf’,
NOUNLOAD,
REPLACE,
STATS = 10
GO

SQL Database Restore Options

Enjoy. Use your database names instead of mine