Mark Breen
marklbreen at gmail.com
Sun Apr 4 02:58:59 CDT 2010
Hello John, [I am probably the king of] Not probably John, you are the KING. after re-reading your original post,I am sorry for waffling on, I was, in fact, not answering your question at all. And as I see now, was telling you stuff that you could already write a book on. While bowing out gracefully, can I just add, - just in case - when you do somedatabase.sometable you need to write it with the scheme as well, so usually, that is either of the following somedatabase..sometable or somedatabase.dbo.sometable of course if you use another schema, you would have to specify that. Can I also mention that you can also link servers, and when you do, then you can write queries such as select * from customers C inner join ServerBig.somedatabase.dbo.someorderstable O on C.Id = O.CustomerId or of course you could do a Insert into ServerBig.somedatabase.dbo.someorderstable ........... Hope to help, sorry for the irrelevant other stuff. And, happy Easter Sunday. Mark On 3 April 2010 13:16, jwcolby <jwcolby at colbyconsulting.com> wrote: > Mark, > > I am probably the king of dynamic SQL. I have dozens of stored procedures > where I dynamically build > SQL to do everything from build tables with varying field names to > populating said table, to > building indexes, to BCP in / out. > > In fact it is one of those applications where I am hitting the wall. My > client asks for a set of > records WHERE (some long list of field compares). That long list of field > compares changes from > order to order. So I build a table (on-the-fly) where there is a base part > (which never changes) > and a dynamic part (where the select fields change for each order). > > The way that I figured out to do this was to build a little table that will > hold the names of the > fields in my select query (the changing part). I grab the field list and > pop it into this little > table. I then open that field list and bring it into a cursor. Using the > cursor I compare each > field name with the list of fields in the base table (some will be the > same) and any field not in > the base table gets added to the table using a modify table. > > When I am done iterating the cursor, my base table now has each and every > field in the selection > query and is ready to accept the data coming from the selection query. > > I then have to do the same kind of process again to build up an INSERT INTO > query which takes the > data coming from the query and insert into the order table. > > So I use a cursor in these two specific places. The cursors are small (1 > to 10 field names) and are > used to build dynamic SQL to build a custom table and then insert data into > that custom table. > > I THINK I can get around the whole "cursor in another database" issue by > selecting the data into a > tableset in memory using the WITH syntax. I just haven't gotten around to > trying that yet. > > The bigger picture is that I have built up these stored procedures and they > are contained inside of > a template database. I copy the template to an order database, then > execute the SPs from there. I > am trying to move all of those SPs in the template / order db out to a > master database that holds > all of my SPs. Mostly I have succeeded, but I have a handful of obstinate > issues where that > SPECIFIC sp has to remain in the template / order database for one reason > or another, always > revolving around having to execute inside of the db being modified. > > I'm getting there though. For someone who is not a SQL Server DBA, I am > doing all right. > > John W. Colby > www.ColbyConsulting.com > > > Mark Breen wrote: > > Hello John, > > > > I can only answer half this query. > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >