Sql Code generation tools

[helpful sql]

Hi all,
Are there any good Sql code generation tools out there in the market? If
not can you please give me tips or sample code for creating one?

I need to automate code generation for data integration. Here is what I
repeatedly need to do...

We have a table called CONTSUPP in our Sql Server database. All of our
clients have the same database structure so they all have the CONTSUPP
table. I need to create different kinds of views on this table for different
clients. I also need to generate Instead of Triggers on these views. Then I
need to write stored procedures to import data from another source table
into my views. The structure of the source table can vary by clients.

Thanks in advance...

[Piotr Dobrowolski]

helpful sql napisa³(a):

Hi all,
Are there any good Sql code generation tools out there in the market? If
not can you please give me tips or sample code for creating one?

I need to automate code generation for data integration. Here is what I
repeatedly need to do...

We have a table called CONTSUPP in our Sql Server database. All of our
clients have the same database structure so they all have the CONTSUPP
table. I need to create different kinds of views on this table for different
clients. I also need to generate Instead of Triggers on these views. Then I
need to write stored procedures to import data from another source table
into my views. The structure of the source table can vary by clients.

Thanks in advance...

[PD] Try MyGeneration - it's free and you can edit templates so that
they will suit your needs. BTW are you sure that it's a good idea to
have different table structure for every client? I don't know your
product so I'm not saying that it's wrong but in many cases it's better
to create more general table structure, let's say something like this:
maintable - id, name, address, etc
attributestable - id, maintableid, attributekind, attributevalue
It can save you some work, but of course has some disadvantages (mainly
speed :( )

--
Piotr Dobrowolski
Piotr.Dobrowolski_usun_gmail.com

[nightwatch77]

Piotr Dobrowolski wrote:

helpful sql napisa³(a):

Hi all,
Are there any good Sql code generation tools out there in the
market? If not can you please give me tips or sample code for creating
one?

I need to automate code generation for data integration. Here is what
I repeatedly need to do...

We have a table called CONTSUPP in our Sql Server database. All of our
clients have the same database structure so they all have the CONTSUPP
table. I need to create different kinds of views on this table for
different clients. I also need to generate Instead of Triggers on
these views. Then I need to write stored procedures to import data
from another source table into my views. The structure of the source
table can vary by clients.

Thanks in advance...

[PD] Try MyGeneration - it's free and you can edit templates so that
they will suit your needs. BTW are you sure that it's a good idea to
have different table structure for every client? I don't know your
product so I'm not saying that it's wrong but in many cases it's better
to create more general table structure, let's say something like this:
maintable - id, name, address, etc
attributestable - id, maintableid, attributekind, attributevalue
It can save you some work, but of course has some disadvantages (mainly
speed :( )

Well, Piotr, are you sure you haven't gone too far in generalization?
You have invented an universal relational database structure capable of
storing any data in a form of name-value pairs. Have you thought about
its usability and performance? Non-trivial queries will be a nightmare -
too complicated to write and very slow to execute, and there will be no
control on database structure, data types, constraints etc.

RG

[Cor Ligthert [MVP]]

Helpful,

Will you please be helpfull to us next time and do not more than 3 or 4
crosspost to relevant newsgroups. Now there are at least 3 which are in my
opinion nonrelevant.

Cor

[Piotr Dobrowolski]

nightwatch77 napisa³(a):

Piotr Dobrowolski wrote:

helpful sql napisa³(a):

Hi all,
Are there any good Sql code generation tools out there in the
market? If not can you please give me tips or sample code for
creating one?

I need to automate code generation for data integration. Here is what
I repeatedly need to do...

We have a table called CONTSUPP in our Sql Server database. All of
our clients have the same database structure so they all have the
CONTSUPP table. I need to create different kinds of views on this
table for different clients. I also need to generate Instead of
Triggers on these views. Then I need to write stored procedures to
import data from another source table into my views. The structure of
the source table can vary by clients.

Thanks in advance...

[PD] Try MyGeneration - it's free and you can edit templates so that
they will suit your needs. BTW are you sure that it's a good idea to
have different table structure for every client? I don't know your
product so I'm not saying that it's wrong but in many cases it's
better to create more general table structure, let's say something
like this:
maintable - id, name, address, etc
attributestable - id, maintableid, attributekind, attributevalue
It can save you some work, but of course has some disadvantages
(mainly speed :( )

Well, Piotr, are you sure you haven't gone too far in generalization?
You have invented an universal relational database structure capable of
storing any data in a form of name-value pairs. Have you thought about
its usability and performance? Non-trivial queries will be a nightmare -
too complicated to write and very slow to execute, and there will be no
control on database structure, data types, constraints etc.

[PD] I defenitely recommend using "standard" columns for data you have
in every deployment. I agree that there is absolutely no sense to
reinvent relational database :)
But, if you have a problem described above it's good (in some cases) to
use attributes stored in external table. Please note that solution I
described above is very simplified - in your design you can have
additional table for atrribute types and names and you can use couple of
tables for storing atributes depending of their type (like
intattributes, varcharattributes etc.). This solution has some
advantages - you can write triggers and reports once and use them in all
of your deployments, version upgrade is much easier etc.
As I wrote before - I think that the main disadvantage of this solution
is performance. But if you can have small performance overhead (I don't
think that if you have let's say 2-3 additional attributes it would slow
your query a lot - it's just a simple join :) ) this solution can be
applied succesfully. Actually I have worked in a team creating biggest
ERP system on polish market and we have used this solution to allow
customer to describe most of the objects in the database with custom
attributes - it was a great success, one of the most widely accepted and
used feature and the performance was acceptable.

--
Piotr Dobrowolski
Piotr.Dobrowolski_usun_gmail.com

[Jim Underwood]

Most database folks would be dead set against this approach. If you do a
search on OTLT or One True Lookup Table you will get a lot of reasons why.
Personally I think it has some benefits in large apps with many customers,
but you should at least hear the arguments against it before going that
route.

"Piotr Dobrowolski" <Pi*********************@gmail.com> wrote in message
news:e3**********@inews.gazeta.pl...

nightwatch77 napisa³(a):

Piotr Dobrowolski wrote:

helpful sql napisa³(a):

Hi all,
Are there any good Sql code generation tools out there in the
market? If not can you please give me tips or sample code for
creating one?

I need to automate code generation for data integration. Here is what
I repeatedly need to do...

We have a table called CONTSUPP in our Sql Server database. All of
our clients have the same database structure so they all have the
CONTSUPP table. I need to create different kinds of views on this
table for different clients. I also need to generate Instead of
Triggers on these views. Then I need to write stored procedures to
import data from another source table into my views. The structure of
the source table can vary by clients.

Thanks in advance...

[PD] Try MyGeneration - it's free and you can edit templates so that
they will suit your needs. BTW are you sure that it's a good idea to
have different table structure for every client? I don't know your
product so I'm not saying that it's wrong but in many cases it's
better to create more general table structure, let's say something
like this:
maintable - id, name, address, etc
attributestable - id, maintableid, attributekind, attributevalue
It can save you some work, but of course has some disadvantages
(mainly speed :( )

Well, Piotr, are you sure you haven't gone too far in generalization?
You have invented an universal relational database structure capable of
storing any data in a form of name-value pairs. Have you thought about
its usability and performance? Non-trivial queries will be a nightmare -
too complicated to write and very slow to execute, and there will be no
control on database structure, data types, constraints etc.

[PD] I defenitely recommend using "standard" columns for data you have
in every deployment. I agree that there is absolutely no sense to
reinvent relational database :)
But, if you have a problem described above it's good (in some cases) to
use attributes stored in external table. Please note that solution I
described above is very simplified - in your design you can have
additional table for atrribute types and names and you can use couple of
tables for storing atributes depending of their type (like
intattributes, varcharattributes etc.). This solution has some
advantages - you can write triggers and reports once and use them in all
of your deployments, version upgrade is much easier etc.
As I wrote before - I think that the main disadvantage of this solution
is performance. But if you can have small performance overhead (I don't
think that if you have let's say 2-3 additional attributes it would slow
your query a lot - it's just a simple join :) ) this solution can be
applied succesfully. Actually I have worked in a team creating biggest
ERP system on polish market and we have used this solution to allow
customer to describe most of the objects in the database with custom
attributes - it was a great success, one of the most widely accepted and
used feature and the performance was acceptable.

--
Piotr Dobrowolski
Piotr.Dobrowolski_usun_gmail.com

[helpful sql]

Thanks all.
I don't have any control over the CONTSUPP table's structure either - we
are just a reseller of this product. Our clients use the CONTUPP table to
store different kinds of data. That is the reason why I need to create
different views on this table for different clients. Many of our clients
want to import data into the CONTSUPP table from their other data sources -
they can be anything. So I always end up changing my scripts every time I
have to diploy them for a new client.

"Piotr Dobrowolski" <Pi*********************@gmail.com> wrote in message
news:e3**********@inews.gazeta.pl...

helpful sql napisa³(a):

Hi all,
Are there any good Sql code generation tools out there in the market?
If not can you please give me tips or sample code for creating one?

I need to automate code generation for data integration. Here is what I
repeatedly need to do...

We have a table called CONTSUPP in our Sql Server database. All of our
clients have the same database structure so they all have the CONTSUPP
table. I need to create different kinds of views on this table for
different clients. I also need to generate Instead of Triggers on these
views. Then I need to write stored procedures to import data from another
source table into my views. The structure of the source table can vary by
clients.

Thanks in advance...

[PD] Try MyGeneration - it's free and you can edit templates so that they
will suit your needs. BTW are you sure that it's a good idea to have
different table structure for every client? I don't know your product so
I'm not saying that it's wrong but in many cases it's better to create
more general table structure, let's say something like this:
maintable - id, name, address, etc
attributestable - id, maintableid, attributekind, attributevalue
It can save you some work, but of course has some disadvantages (mainly
speed :( )

--
Piotr Dobrowolski
Piotr.Dobrowolski_usun_gmail.com

[helpful sql]

I can be helpfull to you but you weren't helpfull to me in any way, were
you?

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:e0**************@TK2MSFTNGP04.phx.gbl...

Helpful,

Will you please be helpfull to us next time and do not more than 3 or 4
crosspost to relevant newsgroups. Now there are at least 3 which are in my
opinion nonrelevant.

Cor

[Göran Andersson]

Actually he was. He informed you on a way to use the newsgroups that
will irritate less people and give you better chances to get help in the
future.

helpful sql wrote:

I can be helpfull to you but you weren't helpfull to me in any way, were
you?

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:e0**************@TK2MSFTNGP04.phx.gbl...

Helpful,

Will you please be helpfull to us next time and do not more than 3 or 4
crosspost to relevant newsgroups. Now there are at least 3 which are in my
opinion nonrelevant.

Cor

[Raymond D'Anjou]

From experience I know that the more you try to help someone become less
irritable, the more irritable he/she becomes.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

Actually he was. He informed you on a way to use the newsgroups that will
irritate less people and give you better chances to get help in the
future.

helpful sql wrote:

I can be helpfull to you but you weren't helpfull to me in any way, were
you?

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:e0**************@TK2MSFTNGP04.phx.gbl...

Helpful,

Will you please be helpfull to us next time and do not more than 3 or 4
crosspost to relevant newsgroups. Now there are at least 3 which are in
my opinion nonrelevant.

Cor

[Göran Andersson]

If a person truly is irreversibly irritating, then that person will
hopefully go and be that elsewhere. I believe though that most people
that behave badly on the net are just inexperienced, and may better
themselves if they just allow themselves to listen and learn.

Raymond D'Anjou wrote:

From experience I know that the more you try to help someone become less
irritable, the more irritable he/she becomes.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

Actually he was. He informed you on a way to use the newsgroups that will
irritate less people and give you better chances to get help in the
future.

helpful sql wrote:

I can be helpfull to you but you weren't helpfull to me in any way, were
you?

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:e0**************@TK2MSFTNGP04.phx.gbl...
Helpful,

Will you please be helpfull to us next time and do not more than 3 or 4
crosspost to relevant newsgroups. Now there are at least 3 which are in
my opinion nonrelevant.

Cor

[someone]

Wow, I didn't even get answers to my original questions so quickly. Looks
like a lot of people here are more interested in such off-subject
discussions. I never get irritated even after getting such bad replies to my
simple question. The people who got irritated need to learn how to manage
their anger.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

If a person truly is irreversibly irritating, then that person will
hopefully go and be that elsewhere. I believe though that most people that
behave badly on the net are just inexperienced, and may better themselves
if they just allow themselves to listen and learn.

Raymond D'Anjou wrote:

From experience I know that the more you try to help someone become less
irritable, the more irritable he/she becomes.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

Actually he was. He informed you on a way to use the newsgroups that
will irritate less people and give you better chances to get help in the
future.

helpful sql wrote:
I can be helpfull to you but you weren't helpfull to me in any way,
were you?

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:e0**************@TK2MSFTNGP04.phx.gbl...
> Helpful,
>
> Will you please be helpfull to us next time and do not more than 3 or
> 4 crosspost to relevant newsgroups. Now there are at least 3 which are
> in my opinion nonrelevant.
>
> Cor
>

[CR]

At the end of the day on Friday, I am far more interested in the posts where
a cross-poster is being told off, than things that actually require thought.
However, noone actually told the OP off, just reminded in an actually very
polite way, and OP got snippish. Had this been usenet, then things would
have gotten exciting! OP: I would listen to Cor, I have followed his
technical advice with success many times.
Thinking of cross-posting, why would you ask a sql question to a .net group.
Many .net programmers are fortunate enough to not have to be sql experts,
they specialize in their development language, and leave the sql to the
dba's. I wish I could sometimes!!

"someone" <no****@stopspam.com> wrote in message
news:eh**************@TK2MSFTNGP02.phx.gbl...

Wow, I didn't even get answers to my original questions so quickly. Looks
like a lot of people here are more interested in such off-subject
discussions. I never get irritated even after getting such bad replies to
my simple question. The people who got irritated need to learn how to
manage their anger.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

If a person truly is irreversibly irritating, then that person will
hopefully go and be that elsewhere. I believe though that most people
that behave badly on the net are just inexperienced, and may better
themselves if they just allow themselves to listen and learn.

Raymond D'Anjou wrote:

From experience I know that the more you try to help someone become less
irritable, the more irritable he/she becomes.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
Actually he was. He informed you on a way to use the newsgroups that
will irritate less people and give you better chances to get help in
the future.

helpful sql wrote:
> I can be helpfull to you but you weren't helpfull to me in any way,
> were you?
>
> "Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
> news:e0**************@TK2MSFTNGP04.phx.gbl...
>> Helpful,
>>
>> Will you please be helpfull to us next time and do not more than 3 or
>> 4 crosspost to relevant newsgroups. Now there are at least 3 which
>> are in my opinion nonrelevant.
>>
>> Cor
>>

[Piotr Dobrowolski]

Jim Underwood napisa³(a):

Most database folks would be dead set against this approach. If you do a
search on OTLT or One True Lookup Table you will get a lot of reasons why.
Personally I think it has some benefits in large apps with many customers,
but you should at least hear the arguments against it before going that
route.

[PD] I totally agree - my point was just to give another point of view,
potentialy useful in this situation. BTW I don't like the idea of
keeping _all_ data in one big table - just the part varying from
customer to customer...

"Piotr Dobrowolski" <Pi*********************@gmail.com> wrote in message
news:e3**********@inews.gazeta.pl...

nightwatch77 napisa³(a):

Piotr Dobrowolski wrote:
helpful sql napisa³(a):

> Hi all,
> Are there any good Sql code generation tools out there in the
> market? If not can you please give me tips or sample code for
> creating one?
>
> I need to automate code generation for data integration. Here is what
> I repeatedly need to do...
>
> We have a table called CONTSUPP in our Sql Server database. All of
> our clients have the same database structure so they all have the
> CONTSUPP table. I need to create different kinds of views on this
> table for different clients. I also need to generate Instead of
> Triggers on these views. Then I need to write stored procedures to
> import data from another source table into my views. The structure of
> the source table can vary by clients.
>
> Thanks in advance...
>
[PD] Try MyGeneration - it's free and you can edit templates so that
they will suit your needs. BTW are you sure that it's a good idea to
have different table structure for every client? I don't know your
product so I'm not saying that it's wrong but in many cases it's
better to create more general table structure, let's say something
like this:
maintable - id, name, address, etc
attributestable - id, maintableid, attributekind, attributevalue
It can save you some work, but of course has some disadvantages
(mainly speed :( )

Well, Piotr, are you sure you haven't gone too far in generalization?
You have invented an universal relational database structure capable of
storing any data in a form of name-value pairs. Have you thought about
its usability and performance? Non-trivial queries will be a nightmare -
too complicated to write and very slow to execute, and there will be no
control on database structure, data types, constraints etc.

[PD] I defenitely recommend using "standard" columns for data you have
in every deployment. I agree that there is absolutely no sense to
reinvent relational database :)
But, if you have a problem described above it's good (in some cases) to
use attributes stored in external table. Please note that solution I
described above is very simplified - in your design you can have
additional table for atrribute types and names and you can use couple of
tables for storing atributes depending of their type (like
intattributes, varcharattributes etc.). This solution has some
advantages - you can write triggers and reports once and use them in all
of your deployments, version upgrade is much easier etc.
As I wrote before - I think that the main disadvantage of this solution
is performance. But if you can have small performance overhead (I don't
think that if you have let's say 2-3 additional attributes it would slow
your query a lot - it's just a simple join :) ) this solution can be
applied succesfully. Actually I have worked in a team creating biggest
ERP system on polish market and we have used this solution to allow
customer to describe most of the objects in the database with custom
attributes - it was a great success, one of the most widely accepted and
used feature and the performance was acceptable.

--
Piotr Dobrowolski
Piotr.Dobrowolski_usun_gmail.com

[someone]

I specialize in both .Net and Sql. I can program C# and VB.Net programs as
well as Sql scripts. Read my question again. I said if yhere is no
automation tool avaible I would like to program it myself. That is why I
posted it in .Net groups. I thought someone may have written a program like
that before. I still stand by the fact that he wasn't helpful to me and the
other two people who replied later were rude.

"CR" <[e-mail]> wrote in message
news:e1*************@TK2MSFTNGP02.phx.gbl...

At the end of the day on Friday, I am far more interested in the posts
where a cross-poster is being told off, than things that actually require
thought. However, noone actually told the OP off, just reminded in an
actually very polite way, and OP got snippish. Had this been usenet, then
things would have gotten exciting! OP: I would listen to Cor, I have
followed his technical advice with success many times.
Thinking of cross-posting, why would you ask a sql question to a .net
group. Many .net programmers are fortunate enough to not have to be sql
experts, they specialize in their development language, and leave the sql
to the dba's. I wish I could sometimes!!

"someone" <no****@stopspam.com> wrote in message
news:eh**************@TK2MSFTNGP02.phx.gbl...

Wow, I didn't even get answers to my original questions so quickly. Looks
like a lot of people here are more interested in such off-subject
discussions. I never get irritated even after getting such bad replies to
my simple question. The people who got irritated need to learn how to
manage their anger.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

If a person truly is irreversibly irritating, then that person will
hopefully go and be that elsewhere. I believe though that most people
that behave badly on the net are just inexperienced, and may better
themselves if they just allow themselves to listen and learn.

Raymond D'Anjou wrote:
From experience I know that the more you try to help someone become
less irritable, the more irritable he/she becomes.

"Göran Andersson" <gu***@guffa.com> wrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
> Actually he was. He informed you on a way to use the newsgroups that
> will irritate less people and give you better chances to get help in
> the future.
>
> helpful sql wrote:
>> I can be helpfull to you but you weren't helpfull to me in any way,
>> were you?
>>
>> "Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
>> news:e0**************@TK2MSFTNGP04.phx.gbl...
>>> Helpful,
>>>
>>> Will you please be helpfull to us next time and do not more than 3
>>> or 4 crosspost to relevant newsgroups. Now there are at least 3
>>> which are in my opinion nonrelevant.
>>>
>>> Cor
>>>