putting names of objects to control-flow loop creating objects

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

fireball - 27 Dec 2006 17:03 GMT

please help newbie

I need to create a lot of objects the same type (let's say: schemas)
I wish to use paramerized block in loop to do so.
- how to put names of my objects to such control-flow?

belss you for help

Reply to this Message

David Portas - 27 Dec 2006 19:49 GMT

> please help newbie
>
[quoted text clipped - 3 lines]
>
> belss you for help

Firstly the obvious question: Why? Where are the names from these
objects coming from? If you can write a query to extract the names then
you could just use Query Analyzer or Management Studio to paste those
names into an editable script and then run the script directly. That
way there is no need for a loop.

If you must do it programmatically then you'll have to do something
with dynamic SQL. See:

http://sommarskog.se/dynamic_sql.html

Personally I'd say that if you have so many schemas that you need a
loop to create them then you definitely have too many schemas... or you
are using them in a highly unconventional manner.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Reply to this Message

fireball - 28 Dec 2006 15:08 GMT

Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> napisal w

> Where are the names from these objects coming from

from text file I got

> if you have so many schemas that you need a loop

first idea is to build scripts in engineer maneer (reusing code blocks - not
to copy them - so managing any changes is better)

second reason - I got my analysis data model in RaRose, which doesn't really
support automatization of creating data model. I use creating
quasi-sqlserver scripts, so I have object names and so. It's not really
large amount of objects (a few schemas, about 100 tables) - but it does
change any time.
Sorry if it sounds a bit werid :-) I'm a newbie

third idea was to create that way descriptions to my obiects (tables,
attributes) in a loop (having descriptions in xls file, for example)

All hints will be appeciated.

ps:

> then you definitely have too many schemas...

you are definitely right.

> are using them in a highly unconventional manner.

like, let's say - to make some perfiormance tests/statistics? (I really
don't do so :-))

Reply to this Message

Erland Sommarskog - 28 Dec 2006 22:33 GMT

> Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> napisal
>> Where are the names from these objects coming from
>
> from text file I got

But text file is not SQL, but you have to transform it to SQL?

Doing this from SQL is not really fun. If you are on SQL 2005, you
could do this through the CLR, but you would still go through hoops.
Do this from a client application: Perl, VBscript or whatever your
favourite may be.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

fireball - 29 Dec 2006 09:22 GMT

Uzytkownik "Erland Sommarskog" <esquel@sommarskog.se> napisal w wiadomosci

> But text file is not SQL

well, the point is to put it into script.sql any way which I will be able to
fetch it into my loop

Reply to this Message

Erland Sommarskog - 29 Dec 2006 09:53 GMT

> Uzytkownik "Erland Sommarskog" <esquel@sommarskog.se> napisal w wiadomosci
>
>> But text file is not SQL
>
> well, the point is to put it into script.sql any way which I will be
> able to fetch it into my loop

Without having seen your file, it's difficult to tell, but it does not sound
as if trying to read it from SQL is a very good idea. You probably much
better off doing this in a client language.

Signature

Reply to this Message