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 |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2009-07-09 : 12:03:17
|
| Sorry. I didn't know how to formulate this problem; not enough space in the Subject line.What I'd like to do is cycle through a list of the names of tables and essentially do something like this:SET @Table='AdventureWorks'SELECT * FROM @TableMust I use the EXEC command to do this? What about if I want to do something like this:SELECT TOP 1 @ID=ID From @TableThank you! |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-07-09 : 12:06:11
|
| Ya u must make use of Dynamic SQL i.e (Exec())Exec ('SELECT TOP 1 '+@ID+'=ID From '+ @Table)Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
 |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2009-07-09 : 14:32:14
|
| Thank you! That will do it. Yea.Follow-up to that question. I'm doing an insert statement using dynamic sql. I'm copying a set of data from one table to another, from two separate databases. The tables in the two databases are EXACTLY the same. I'm just trying to copy a specific subset of data from one table to the other. The problem I'm running into is I'm using this type of format:INSERT Database1.dbo.Table1SELECT * FROM Database2.dbo.Table1 Where Type='Banana'The table in both databases has an ID column with IDENTITY(1,1). I don't want to copy the ID from one table to the other. Is there a way to copy all but the ID column without specifying fields? I'm trying to automate this process and that would be really disappointing if I had to specify specific columns in each table. Then cycling through a list of the names of tables would be useless. Any help would be appreciated. Thanks. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-07-09 : 14:59:25
|
quote:
Harry Callahan: I know what you're thinking. "Did he fire six shots or only five?" Well, to tell you the truth, in all this excitement I kind of lost track myself. But being as this is a .44 Magnum, the most powerful handgun in the world, and would blow your head clean off, you've got to ask yourself one question: Do I feel lucky? Well, do ya, punk?
ummmmm....lose the dynamic SQlBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-07-11 : 08:29:55
|
| You are re-inventing replication. Read the manuals. SQL Server can do all of this for you as part of what you get with the license, including strategies to deal with identity columns etc. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2009-07-19 : 20:27:14
|
The answer is "No"... you will need to specify the fields. You can use replication if you want but that would certainly be a bit of overkill for a one-off task if that's what it is.If you have a bazillion columns in the tables, you can write a query to write the transfer query using Information_Schema views as a source of column names.--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! ""RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row" |
|
|
|
|
|
|
|
|
querying table in t-sql using varchar variable