Code Generation: Using SQL to Generate SQL and Other Code

Question

Code Generation: Using SQL to Generate SQL and Other Code

Haidong Ji

SSCrazy

Points: 2453
More actions
February 7, 2005 at 4:37 pm

#164465

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/codegenerationusingsqltogeneratesqlandothercode.asp

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

morpheuz SSC-Addicted Points: 454 More actions · Answer 1

Excellent article. I have tinkered for quite some time with a QA alternative, and the thing that has bugged me most is the generation of SQL statements. This article has given me a great leg up.

"See how deep the rabbit hole goes..."

Olivier Van Rode-198661 SSChasing Mays Points: 655 More actions · Answer 2

Cool. Especially the way to automate BCP. Very useful as a backup tool.

However, I have a problem with this bcp feature itself : I have a database which holds many French words, spelled with the nice é, è, ê, û aso characters.

When I bcp them out, and then re-bcp them in, they are converted to silly other things , due to the non-unicode-compliance of wordpad flat text documents . Any idea how I could conserve the code page stored in nvarchar fields, in a bcp file?

Maybe, a pro like you guys knows an answer here ?

Tatsu SSCertifiable Points: 7824 More actions · Answer 3

Great article. I love code generation myself and started writing an article on it but it sucked so I have abandoned it. Maybe I will pick it up again sometime. I was going to focus more on tools like CodeSmith and why people would want to use code generators. Maybe someday...

Olivier: I have a script for generating insert statements for a given table and there might be others on the site and just posted it (should be available within 48 hours). The script is called Script Table Data to Insert Statements and is in the Maintenance and Misc. categories. It's not bcp but it has been very useful to me.

[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog

Haidong Ji SSCrazy Points: 2453 More actions · Answer 4

Olivier,

Thanks for your compliment. I'm glad you like this article.

In addition to -c -q -S switches in my code, try adding -w in both bcp scripts. -w is the unicode switch. With that switch, BCP will perform the bulk copy operation using Unicode characters. It uses nchar as the storage type. Hopefully that will take care of accented letters like é, è, ê, û. According to Books on Line, -w will not work for SQL Server 6.5.

All systems I have are in American English collation/code page, therefore I couldn't test that for you. I am curious to know if that solves your problem.

Haidong

mario febbraio Grasshopper Points: 21 More actions · Answer 5

I read a lot of brilliant comments but using system table (SYSOBJECTS and other) is not the better choice if one is working on SQL server 2000 or 2005 where we have the information_schema views that offer the same behaviour giving us a good level of abstraction from the "implementation" into db.

I don't know how it works on other DB but I think there are similar stuffs.

Any way we are talking about common technique used to “generate code”. For instance, for MS SQL server, is available an object model form .NET version 2.0 more flexible and powerful. It’s true that it’s important having a good programming skills.

I don’t the article is good but it doesn’t express new idea or, for me, the better way to do this kind of operation.

Thanks

Marittiello

Olivier Van Rode-198661 SSChasing Mays Points: 655 More actions · Answer 6

Cool man. -w solves all my problems.

Finally an adequate way to backup my years of hard work

Haidong Ji SSCrazy Points: 2453 More actions · Answer 7

Very good Olivier, glad it worked out for you.

Haidong

Terry Grignon SSC Eights! Points: 840 More actions · Answer 8

I very much enjoyed the article and think it will be very useful in my developement work. To switch to using catalog views in SQL Server 2005 isn't difficult at all.

For the first example on getting counts you just have to make a small change from sysobjects to sys.objects and "type" instead of "xtype" like this:

select 'select ' + char(39) + name + char(39) + ' as dbtable, ' +

'count(*) as rows from [' + name + ']'

from sys.objects where type = 'u' and name not in ('dtproperties')

order by name

Thanks for the article!

Haidong Ji SSCrazy Points: 2453 More actions · Answer 9

Excellent! I haven't really tried it in Microsoft SQL Server 2005 yet. Now I learned something from your example 🙂