Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Code Generation: Using SQL to Generate SQL and Other Code


Code Generation: Using SQL to Generate SQL and Other Code

Author
Message
Haidong Ji
Haidong Ji
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 60
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/codegenerationusingsqltogeneratesqlandothercode.asp



morpheuz
morpheuz
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 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
Olivier Van Rode-198661
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 8

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
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307

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.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Haidong Ji
Haidong Ji
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 60
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
mario febbraio
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1

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
Olivier Van Rode-198661
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 8

Cool man. -w solves all my problems.

Finally an adequate way to backup my years of hard work


Haidong Ji
Haidong Ji
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 60
Very good Olivier, glad it worked out for you.

Haidong



Terry Grignon
Terry Grignon
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 107

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
Haidong Ji
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 60
Excellent! I haven't really tried it in Microsoft SQL Server 2005 yet. Now I learned something from your example :-)



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search