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 |
|
cornall
Posting Yak Master
148 Posts |
 Posted - 2008-09-16 : 06:03:41
|
| Hi,I want to copy rows of a table. The problem I have is that my tables can change so I want my stored proc to be able to copy the rows regardless of the table def.I can do this no problem withINSERT INTO table SELECT * FROM table WHERE id = 123However I want to set some of the values to differnet valuese.g.id = 1, forkey = 123, a = 'abc', b = 'abc', c = 'abc'Again I can do this no problem usingINSERT INTO table (forkey,a,b,c) (SELECT 124,a,b,c FROM table WHERE id = 123)You can see above I am changing the value of forkey and letting id set itself as an auto seed.Now my problem is that this insert command works until I add a d column to my table then it breaks.So what I really want to say is PSUDO code INSERT INTO table id=auto, forkey = 124 SELECT others from table.Hope this makes some sense.Cheers D |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-16 : 06:09:42
|
| i think you need dynamic sql here. As your table definition is changing dynamically, you need to first get column info of table and then use it in insert. |
 |
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2008-09-16 : 06:30:35
|
| I thought that might be the case just wondered if there was a clever select * excluding ... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-16 : 06:39:48
|
quote:
Originally posted by cornall
I thought that might be the case just wondered if there was a clever select * excluding ...
nope if table names and structure are changing |
|
|
|
|
|
|
|
|
Insert into using select