Wednesday, March 28, 2012

How to create nested sql scripts

Hello,

I want to be able to create the tables in a database from multiple scripts.

I have a separate sql script for each table and I want to make one master sql script that call all separate table scripts.

I searched for hours now in the documentation and the internet and I can't find a simple solution. I hope somebody here can help me. I just want to run a sql script from another sql script...

Bernard Dijkstra

This is a solution:

1. I made in c:\ the file master.sql that contain


EXEC xp_cmdshell 'sqlcmd -i "c:\child1.sql"';

EXEC xp_cmdshell 'sqlcmd -i "c:\child2.sql"';

2. child1.sql contain : select 'This is script 1'

child2.sql contain : select 'This is script 2'

3.if i run in command:c:>sqlcmd -i "c:\master.sql" the output is :


--
--
--


-

This is script 1

NULL

(1 rows affected)

NULL

output

--
--
--


-

This is script 2

NULL

(1 rows affected)

NULL


C:\>

hth

so, the ideea is to use sqlcmd to run scripts

|||

Thanks for your idea, but I think it is no solution for me.

So there is no simple solution that also can be used in the Query Analyzer and other tools. :-(

|||

For SQL 2000 i think you can replace sqlcmd (that it is for 2005) with isql or osql; xp_cmdshell is in 2000 too.

Try it, don't be angry !

hth

|||

This still won't work for me, since I want to be able to run the script in the Query Analyzer since that's the tool our customers use to run SQL scripts.

I want them to select the database in the Query Analyzer, then load a script and run it.

With this solution this won't work (easily). So I have to put everything in one script, what will be a problem with version management on object level...

I'm not angry, but I'm disappointed...

Thanks for your help anyway|||I think you can do :

exec("use YourDB")
go
EXEC xp_cmdshell 'sqlcmd -i "c:\master.sql"';

|||

I tried this and it seems that the context isn't preserved.

I will write a simple program that will generate a master script that can be used to create the database...

No comments:

Post a Comment