| Author |
Topic |
|
RaghaSM
Yak Posting Veteran
52 Posts |
 Posted - 2008-06-03 : 15:15:53
|
| Hi all,I have 2 tables one temporry table and another permenant table. Both got the same column and same datatypes. The fields it has got are:AgencyId,PositionId,LevelID,A,B,C,UpdateddateThe primary key is set of Agencyid,Positionid,Levelid,and UpdateddateNow I want to move the data from Temporary table to Permennat table one agency at a time. To be more specific , when i selcte the agency. I want that agency details from temporary table to be transferred to the permenant table , and the same data deleted from the Temporary table.( there will around 20-30 rows for each agency that need to be transferred)How to write a query for this? Please Help. Your help will be very much appreciated.Thanks & Regards,RaghaPlease help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-03 : 15:20:32
|
| INSERT INTO PermanentTable(Column1, Column2, ...)SELECT Column1, Column2, ...FROM #TempTableDELETE FROM #TempTableTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
RaghaSM
Yak Posting Veteran
52 Posts |
Posted - 2008-06-03 : 15:44:03
|
| Thanks for your reply. But I am a bit concered and have a question.Is that all even i get more than one record from select statement? Can i insert all of them with this into permenant table?Somebody suggested using a cursor .. but I dont knwo to use cursors. If this is this simple. I will defintely implement this. Thanks again. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-03 : 15:48:05
|
| The code I posted will copy all data from #TempTable and put them into PermanentTable. I don't see any reason to use a cursor. Whoever suggested that should be ignored.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
RaghaSM
Yak Posting Veteran
52 Posts |
Posted - 2008-06-03 : 16:13:07
|
| Thanks alot for your help.And for clarifying my doubts.Regards,Ragha |
|
|
|
RaghaSM
Yak Posting Veteran
52 Posts |
Posted - 2008-06-04 : 15:15:56
|
| Hi,Can you suggest me how should I update he Permenant table from Temporary Table data for the same above scenario (instead of Insert) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-04 : 15:16:51
|
| What would you like to update?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
RaghaSM
Yak Posting Veteran
52 Posts |
Posted - 2008-06-04 : 15:20:49
|
| Thanks for your quick response.My situation is like this... Everymonth the user submits the data .. which will internally transfer the data from temp table to permenant table.But if the same user submit the data agian before a month is completed then I want to update the data exsiting in the permenant table with the data avaialable in the temporary table. How should i do this?I am new to SQL server and doesnt have exposure in all the areas. Please help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-04 : 15:22:53
|
| But what would you like to update? What stays constant between the two tables? The primary key columns? Do you want to update all of the non-key columns?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
RaghaSM
Yak Posting Veteran
52 Posts |
Posted - 2008-06-04 : 15:24:54
|
| I am sorry.. I havent answered your question correctly. I want the update the A,B,C columns along with the updateddate column in the permenant table.Primary key for the permenant table is the combination of Agencyid,positionid,levelid and Updateddate |
|
|
|
|
Data moving from one table to another table.