Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lotek
Starting Member
42 Posts |
 Posted - 2005-09-30 : 15:45:51
|
| I have a table where each row has an sql statement. I want to exec each one but without using a cursor.I could coalesce them into a variable but their sum is much larger than 8000 characters. Is there any way to do something like exec( SELECT COALESCE etc FROM table) ?Thanks-Matt |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-09-30 : 15:55:58
|
| I've never had to use a cursor, but this is one instance with it seems to make sense.I'd like to know what this application does and why storing SQL statements in a table made sense. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-09-30 : 16:08:14
|
| bcp out into a file and use osql?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
lotek
Starting Member
42 Posts |
Posted - 2005-09-30 : 23:23:38
|
| Its a little bit complex, but here goes. If you have other ideas im all ears.Background:-I have approx 10 linked databases. Each have approx 30 tables and 50 fields.-All have the same table structure.-I am using the linked servers primarily to populate one database. (This happens each night.)-I am only transferring a subset of each table, not everything.-Users must have the ability to add a linked database and SQL must be able to transfer data from it immediatly, without anyone writing script.My solution was to:-Create a table with all the table names in it (each database has the same table schema).-Create another table that specifies which tables from which databases should be used to populate the central database including any additional where criteria. -Create a function that compiles an insert/select/where statement based on the table's fields.-Call the function in a view to recieve the proper sql statement for each table that needs to be queried for data.-Run a cursor over the view to execute each sql statement. (Approx 100 rows) |
|
|
|
lotek
Starting Member
42 Posts |
Posted - 2005-09-30 : 23:26:11
|
| Would BCP/OSQL have a large performance impact in my situation? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-10-01 : 08:57:44
|
| No, if anything it will probably be the fastest solution overall. |
|
|
|
|
|
|
EXEC on each row without a cursor?