February 18, 2010 at 1:01 pm
declare @li_max_rows int;
declare @li_row_nbr int;
create table #a_table
(
table_id int,
table_nm varchar(50)
);
insert #a_table (table_id, table_nm)
select row_number() over (order by ta.name) as table_id, ta.name
from sys.schemas sc,
sys.tables ta,
sys.columns co
where sc.name = 'myschema' and
ta.schema_id = sc.schema_id and
co.object_id = ta.object_id and
co.is_identity = 1
order by ta.name;
set @li_max_rows = (select max(table_id) from #a_table);
set @li_row_nbr = 1;
while @li_row_nbr <= @li_max_rows
begin
select table_id, table_nm from #a_table where table_id = @li_row_nbr;
set @li_row_nbr = @li_row_nbr + 1;
end;
My question is from within the while loop, how do I reference columns table_id and table_nm? With Oracle, I simple select them into local variables, and reference those variables. But with SQL Server, I can't do that. Help please....
February 18, 2010 at 1:46 pm
I hope that these help:
http://www.dbforums.com/microsoft-sql-server/992581-select-data-temp-table-sql.html
http://vijaybalajithecitizen.blogspot.com/2008/08/table-variable-and-temp-tables-in-sql.html
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 18, 2010 at 2:40 pm
Your postings helped, and mission accomplished. Turns out my syntax on the assignment was incorrect. I was trying to do this:
set @li_selected_row_nbr = select table_id from #a_table where table_id = @li_row_nbr;
when I should have been doing this:
select @li_selected_row_nbr = table_id from #a_table where table_id = @li_row_nbr;
The technique is simple when you're not trying to mix Oracle syntax with SQL Server. My bad.
Thanks for the tips!
February 18, 2010 at 8:29 pm
What's the rest of the query? I want to see why you're using a loop.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2010 at 3:48 pm
To Jeff's question as to why...
The purpose of the stored procedure is to generate a script to functionally copy table content between databases within the same instance. This is outside SQL Server's database copy capabilities; SQL Server cannot distinguish the functional purpose of a table within a business plan. For example, some tables within a schema are "operational", some are "reference", some may be considered application "configuration", etc. In the absence of a rigorous naming standard to distinguish table types at the functional level, it can be difficult to use a broad-brushed approach to copying tables. Hence the copy script. The copy script is generated by two stored procedures which read selected system tables and uses the table definitions found there to generate a named-query style of copy script, including accounting for those tables that have identity columns and those that don't.
An in-memory table is populated with those tables that are to be included in the generated script for the copy operation. The "generate tables" procedure passes a table name to a subordinate procedure to generate the columns for that table. Within the generated/written script, an "insert-select" statement is created using a named query approach. The target table is truncated, reseeded if it has an identity, the insert identity property is turned on to enable inserting explicit identity values, a named insert-into command is written to actually copy the data, the insert identity property is turned off after the insert is completed, and the table is re-seeded with a dbcc command, reflecting the max value of the identity column.
There are 2 stored procedures:
pr_generate_tables
pr_generate_columns
Procedure pr_generate_columns is called by pr_generate_tables with a table name. It constructs the insert-select command using the named-query syntax, which is required when identity values are going to be included and preserved during a copy operation. Procedure pr_generate_tables accepts a number of parameters on invocation: the copy type: whole database, operational, configuration, or single-table; the source database name, the source database schema, the target database name, and the target schema. It is a beautiful thing to watch. For a very small database comprised of 91 tables and 1,639 columns, the procedures take appx 2.5 minutes to generate a comprehensive whole-database copy script, and the script itself executes in appx 35 seconds for a very small database comprised of appx 450,000 rows. And I, as the DBA, do not have to maintain anything relative to functional database copying. I simply generate a copy script out of the system tables, and it's always 100% accurate all the time. Now that the heavy lifting has been completed (the procedures written and tested), the developers think I'm a DBA god because I can perform all kinds of database copying, anytime they need it. And it's relatively "instantaneous." WE both know I'm just a lazy DBA who's willing to work his a-- off for a short period of time and reap the rewards continuously.
I hope I've answered your question.
February 20, 2010 at 9:55 pm
OK, given all that, ... Why the Loop? You can do everything that you mentioned above, but generate set-oriented SQL, instead of scalar & loop-based SQL. I know because I have done it many times myself.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 3:33 am
It does seem like an unusual way to copy stuff around - replication springs to mind as an alternative.
Nevertheless, given that the code exists, and you just need a way to call the existing stored procedures, I can see why you would use a loop here. You probably have better things to do than re-write the existing process just to avoid a loop 😀
That said, I do want to post a modified version of your existing code. This version uses the more modern, and recommended, JOIN syntax, and uses data in the AdventureWorks sample database:
WITH TablesWithIdentity
AS (
SELECT table_id = ROW_NUMBER() OVER (ORDER BY TA.name),
table_name = TA.name
FROM AdventureWorks.sys.schemas SC
JOIN AdventureWorks.sys.tables TA
ON TA.[schema_id] = SC.[schema_id]
JOIN AdventureWorks.sys.columns CO
ON CO.[object_id] = TA.[object_id]
WHERE SC.name = 'dbo'
AND CO.is_identity = 1
)
SELECT TWI.table_id,
TWI.table_name
FROM TablesWithIdentity TWI
ORDER BY
TWI.table_name ASC;
Paul
February 21, 2010 at 12:00 pm
Paul White (2/21/2010)
It does seem like an unusual way to copy stuff around - replication springs to mind as an alternative...
While I don't know why the OP does it this way, I can say why I have used custom techniques like this in the past:
The problem with most of the built-in tools is that they are relatively inflexible, or more often, "flexible" in only one way. They only copy what they copy, no more and less only in certain ways.
The "whole database" tools (backup, Copy DB, Replication Snapshot, etc.) copy everything, with no options to control content, especially only copying certain data.
The "per object" tools (replication, Migration Wizard, Export Wizard, SSIS) have some flexibility as to what objects & data are copied, but at an increasing complexity cost. Usually they keep their own copy lists, so when something is added to the source/template DB, the package/tool usually has to be laboriously re-generated.
Most of all, none of these tools have good "Targeting/Retargeting" facilities. These allow you to create a package/kit/tool at one point in time, and then at a later time invoke it to create an actual database, but taking as installation/copy parameters:
-- The Target Server Name
-- The Target DB Name
-- internal Schema Name substitutions
-- Related Linked-Server substitutions
-- Related DB Name substitutions
-- Target DB self-identification values/data
-- Consistent (but scattered) metadata changes
The first two can be done by most (but not all) tools. Virtually nothing will do the Schema name changes automatically.
The "Related" server/dbname parameters have to do with the fact that many times these databases must have a relationship with other servers and/or local databases, whose names are different for different installations. I don't know of any tools that can handle this automatically.
Target self-identification values has to do with the need for a "copied" database to be distinguished from all other copies, particularly in cases where their distributed contents will be all pulled together in a central location. Needing separate Development, Test and Production environments is a good example of this. This can often be handled with a post-installation script, but not always, and these scripts are hard to integrate with the copy tools.
As for the "metadata" changes, sometimes the Template-DB cannot be used "as-is" but must have it's metadata and other definitions subtly altered for the production-instance. A good example of this are replicated DBs themselves. Imagine that you want to create distributed data-collection databases on a number of remote servers that will also all be replicated into a Central database. If you think about it, while this is a reasonable need, it is a beast of numerous tricky little details, that cannot be the same as the Template DB (because unlike the copies, it is not a "real" collector), but also must have some things different from the other collector-copies, both for source-identity purposes, and for local configuration needs.
Now although all of this could be done with SSIS/BIDS packages, it's not automated and could be just as much development effort as SQL procs, few DBAs know it well enough to do something this sophisticated, and even then I am not convinced that it would be better (or even necessarily as good) as SQL scripts & procs.
A PowerShell script (for SQL 2008+) on the other hand probably could do this as well as or better than anything else, however, even less DBAs know it well enough to do something like this (I am not even close).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 11:08 pm
Barry,
Primarily, I was responding to your post that seemed to suggest that rewriting the process to remove the loop was somehow worthwhile.
Replication has many features (e.g. parameterized row filters) which can implement very complex requirements, but I don't intend to get into a deep discussion about it. My point was an aside really - replication was just a thought that occurred to me.
Paul
February 22, 2010 at 1:47 am
Paul White (2/21/2010)
Primarily, I was responding to your post that seemed to suggest that rewriting the process to remove the loop was somehow worthwhile.
Oops. Never mind. 😀 Seriously though, I also used to use cursors in my generator's code, before I learned better techniques.
Replication has many features (e.g. parameterized row filters) which can implement very complex requirements, but I don't intend to get into a deep discussion about it. My point was an aside really - replication was just a thought that occurred to me.
No worries. I just seem inordinately wordy the last couple of days. It feels like I am obsessively over-explaining and justifying everything. It's really wearing me out too...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2010 at 2:04 am
Hey Barry,
Good point about the cursors. I had to use some a little while back, and felt dirty afterward 😉
Don't ease up on the verbosity for my sake - I always enjoy reading your comments; even where I disagree with them 🙂
Paul
February 23, 2010 at 7:09 pm
Wow! Great discussion, everyone! I chose to write a couple of stored procedures because replication was not an option and would not meet my needs. "Crazy Eights" seems to understand the "why" behind what I did better than most; most DBAs don't have the need to copy data at this low of a functional level. We infrequently become this close to an application, including me, preferring to support structure only and not data outside of crash recovery.
And yeah, I'm aware of JOINS and their arguably superior approach to combining data between tables. Sometimes they're faster, sometimes they're not. I am disappointed at SQL Server's ability to quickly pull DDL out of the system views. Its performance isn't terrible, but I wouldn't call it robust. Sad to say, both IBM's DB2 and Oracle's Oracle outperform SQL Server in this regard - hands down. In-memory tables in SQL Server are 50% faster than using cursors, but SQL Server can only drool when it trys to keep up with the big boys. I'm not trying to start a war here, though I suspect my comments will. But I've been a DBA for 27 years and have done some impressive things with all 3 engines. But from my perspective, using seat-of-the-pants real world measurements, DB2 and Oracle are still in a performance league of their own, even over 64-bit SS. Gotta say though, Microsoft is definitely moving in the right direction.
Happy databasing!!!! I'm letting go of this thread now.
Nick
February 23, 2010 at 7:55 pm
bercea.nick (2/23/2010)
... I am disappointed at SQL Server's ability to quickly pull DDL out of the system views. Its performance isn't terrible, but I wouldn't call it robust. Sad to say, both IBM's DB2 and Oracle's Oracle outperform SQL Server in this regard - hands down. In-memory tables in SQL Server are 50% faster than using cursors, but SQL Server can only drool when it trys to keep up with the big boys. I'm not trying to start a war here, though I suspect my comments will. But I've been a DBA for 27 years and have done some impressive things with all 3 engines. But from my perspective, using seat-of-the-pants real world measurements, DB2 and Oracle are still in a performance league of their own, even over 64-bit SS. Gotta say though, Microsoft is definitely moving in the right direction.
Heh, well I'll try not to respond with live ammo here... 🙂
What I will say is that this has not been my experience. I have had no trouble keeping SQL Server up to speed with Oracle and DB2 platforms, and often even beating them.
As for the Systems tables stuff, I would really love it if you could give us a simple example of this that we could tune for you to test and compare to those other platforms. Because my stuff usually runs a LOT faster than a Cursor. For instance although I obviously cannot be sure its an apples to apples comparison, at the sizes that you were referencing for code generation of 2.5 minutes in your previous post I typically get closer to 30-40 seconds, and I'm still on 32 bit SS (though you do have to do special handling for table defs, they're a problem).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply