August 19, 2012 at 2:36 pm
In my stored procedure, I create a temp table from table A. Then I modify data on the temp table. I wan to insert the data from temp table to table A again but I don't want to specify column names like " Insert into tableA (column x, column y, column z) select columnx, columny, columnz from #temptable"
I would like to do is "Insert into tableA select * from #temptable" but it does not allow me since tableA has idendity column.
My main concern: In the future, if we add a new column to tableA then " Insert into tableA (column x, column y, column z) select columnx, columny, columnz from #temptable" will not insert the new column value in the newly inserted record.
Any suggestions?
Thanks guys.
August 19, 2012 at 3:56 pm
I would like to do is "Insert into tableA select * from #temptable" but it does not allow me since tableA has identity column
When creating #temptable why do you include in #temptable the identity column from tableA ? Is it required by processing that you have not included in your posting ?
It would assist those who wish to help you if you posted your tableA definition and the definition of #temptable.
August 19, 2012 at 5:23 pm
kaushal_shah26 (8/19/2012)
In my stored procedure, I create a temp table from table A. Then I modify data on the temp table. I wan to insert the data from temp table to table A again but I don't want to specify column names like " Insert into tableA (column x, column y, column z) select columnx, columny, columnz from #temptable"I would like to do is "Insert into tableA select * from #temptable" but it does not allow me since tableA has idendity column.
My main concern: In the future, if we add a new column to tableA then " Insert into tableA (column x, column y, column z) select columnx, columny, columnz from #temptable" will not insert the new column value in the newly inserted record.
If you really (really, really, really) want to avoid the future issue of a new column been added to table A you can always build a dynamic sql statement that includes ALL the columns (minus the indentity column) on-the-fly.
Something like the query below should return the list of columns needed for your dynamic statement...
select name from syscolumns where id=object_id('<table_name>') and name not = '<identity_column_name>'
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 19, 2012 at 7:58 pm
kaushal_shah26 (8/19/2012)
My main concern: In the future, if we add a new column to tableA then " Insert into tableA (column x, column y, column z) select columnx, columny, columnz from #temptable" will not insert the new column value in the newly inserted record.
Any suggestions?
Thanks guys.
I would prefer changing the procedure , rather than making it dynamic ; I really am not sure why are you so worried about the new column addition. does it happen frequently in your framework and is it automated ?
well, just check whether SELECT INTO works for you or not.
select * into #temp from test
[Note : In my opinion * is for testing purpose, not for development .]
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply