Creating DB and using Select Into

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Creating DB and using Select Into

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-09-21 : 05:46:08

Hi
I want to write a simple script to give to a colleague so they can run it. And it will create a database and copy data from an existing database into the new one. Kind of like a Backup.

So far I have this

++++++++++++++++++++++++++++++
Create Database Calls_Info
select *
into Calls_Info.dbo.Calllog_backup
From SupportDesk.dbo.Calllog
+++++++++++++++++++++++++++++++
My questions are;
1. If I run the whole thing it tells me that the database doesn't exist so I have to run the "Create Database" statement first then the other one- how can I get it to run with just one parse?
2. How can I get the Create database to check if the database already exists so it won't produce an error?
3. I also want to use a DROP table so that the tables are written fresh each time, but not dure how to get it to check

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-09-21 : 05:52:29

I've just solved part of my problem (Question3) by using the following snippet that i found.

IF OBJECT_ID('Callbackup.dbo.Calllog_backup','U') IS NOT NULL
         DROP TABLE Callbackup.dbo.Calllog_backup

select *
into Callbackup.dbo.Calllog_backup 
From supportdesk.dbo.Calllog

asgast
Posting Yak Master

149 Posts

Posted - 2009-09-21 : 05:58:09

use GO to separate script into logical parts
then use IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'table name') or IF OBJECT_ID() to drop and create tables
you can get this script in management studio by using script table -> create to

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-09-21 : 06:02:06

I'm doing well, I'm answering my own posts
I've used the same method to answer my second question.
Is this correct or should I be doing more here to check if a database called Calls_Info already exists
+++++++++++++++++++++++++++++++++++++++++
IF OBJECT_ID('Calls_Info','U') IS NOT NULL
Create database Calls_Info
+++++++++++++++++++++++++++++++++++++++++

Looks like question 2 still to go!

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-09-21 : 06:17:12

so is it OBJECT_ID to check if a databse exists as well?
thanks

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-09-21 : 07:23:13

Found out that it was a thing called DB_ID

+++++++++++++++++++++++++++++++++
IF db_ID('Callbackup') IS NULL
Create database Callbackup
+++++++++++++++++++++++++++++++++

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-09-21 : 07:25:47

So my script looks like this now.
I think I have completed it.
Any comments or anything I may have missed???

+++++++++++++++++++++++++++++++++++++++++++

IF db_ID('Callbackup') IS NULL
Create database Callbackup

--The First bit checks to see if a database called Callbackup is there already and if not Creates it

GO

-- the "GO" separates the commands otherwise it tries to run them all at once and you get an error
-- because the database isn't there yet when it doesn the next bit

IF OBJECT_ID('Callbackup.dbo.Profile_backup','U') IS NOT NULL
DROP TABLE Callbackup.dbo.Profile_backup

IF OBJECT_ID('Callbackup.dbo.Subset_backup','U') IS NOT NULL
DROP TABLE Callbackup.dbo.Subset_backup

IF OBJECT_ID('Callbackup.dbo.Calllog_backup','U') IS NOT NULL
DROP TABLE Callbackup.dbo.Calllog_backup

IF OBJECT_ID('Callbackup.dbo.Detail_backup','U') IS NOT NULL
DROP TABLE Callbackup.dbo.Detail_backup

IF OBJECT_ID('Callbackup.dbo.Asgnmnt_backup','U') IS NOT NULL
DROP TABLE Callbackup.dbo.Asgnmnt_backup

IF OBJECT_ID('Callbackup.dbo.Journal_backup','U') IS NOT NULL
DROP TABLE Callbackup.dbo.Journal_backup

GO

--If the table exists already it gets "Dropped" and the select into will re-create the table

select *
into Callbackup.dbo.Profile_backup
From SupportDesk.dbo.Profile

select *
into Callbackup.dbo.Subset_backup
From SupportDesk.dbo.Subset

select *
into Callbackup.dbo.Calllog_backup
From SupportDesk.dbo.Calllog

select *
into Callbackup.dbo.Detail_backup
From SupportDesk.dbo.Detail

select *
into Callbackup.dbo.Asgnmnt_backup
From SupportDesk.dbo.Asgnmnt

select *
into Callbackup.dbo.Journal_backup
From SupportDesk.dbo.Journal

++++++++++++++++++++++++++++++++++++++++++++++

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-21 : 08:11:58

Looks like you're good to go! You created an entire discussion all by yourself :)

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources