'importing' a previously written .sql

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 2000 Forums

SQL Server Development (2000)

'importing' a previously written .sql

Author

Topic

jmzrbnsn
Starting Member

4 Posts

Posted - 2005-12-03 : 20:15:33

Here we go -- what we've got is a create database 'script' written in
Server's SQL query analyzer which tears down and rebuilds a new version
of the data base,
no problem there, but what we would like to have is a seperate .sql
file for each table which are called from the breakdown and build up
script (as it makes it easier to change the properties of each table --
no having to trawl through the query to find the relevent table)
so instead of having:
IF EXISTS (SELECT * FROM sysobjects
WHERE id = OBJECT_ID(N'[dbo].[tblBLIS_Curriculum_Balance]')
AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].[tblBLIS_Curriculum_Balance]
GO
CREATE TABLE [tblBLIS_Curriculum_Balance] (
[id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
CONSTRAINT [PK_tblBLIS_CirriculumBalance] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

we could have something like :

IF EXISTS (SELECT * FROM sysobjects
WHERE id = OBJECT_ID(N'[dbo].[tblBLIS_Curriculum_Balance]')
AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].[tblBLIS_Curriculum_Balance]
GO
~~CALL~~ tblBLIS_Curriculum_Balance.sql
GO

where tblBLIS_Curriculum_Balance.sql is the create table script and
~~CALL~~ is the function we're looking for...

Would you have any idea?
Thanks

insert 'clever' quote here...

cfederl
Starting Member

26 Posts

Posted - 2005-12-03 : 22:01:22

If you are using Enterprise Manager to generate the scripts, this is very easy.
After selecting "All Tasks" ==> "Generate SQL Scripts", pick the "Options" tab.
In the lower right, there is an option to create one file or one file per objects.
If you select "one file per object", the generated files will NOT have an extention of SQL but instead will be TAB for table, PRC for procedure and VIW for views.

Carl Federl

jmzrbnsn
Starting Member

4 Posts

Posted - 2005-12-03 : 22:37:32

I may have mis-directed you with my wording -- I don't wish to write a script if I can help it -- insteadI wish to run within the query analyser and call upon a previously written and saved query - rather than having to rewrite the query every time...
but thanks --
james

insert 'clever' quote here...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-03 : 23:59:03

You can use isql or osql to execute the previously saved .sql script from the command prompt.

-----------------
[KH]

jmzrbnsn
Starting Member

4 Posts

Posted - 2006-02-05 : 17:38:12

Yes , that is what I ended up doing ,writing a batch script etc, thanks -- it worked nicely :)

insert 'clever' quote here...

Kristen
Test

22859 Posts

Posted - 2006-02-06 : 01:32:44

I think cfederl was meaning that you could use Enterprise Manager as a one-time-deal to convert your existing single script into a one-file-per-object script. Run you script to make a new database, then script it into separate files using E.M.

And then use OSQL as khtan suggested to run the scripts

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources