Reinserting certain records of a table in to the same table

Hi

How can I reinsert certain records of a table in to the same table and change only one column? This is story : I have a table that user enters daily records in it , most of these record are same as records of yesterday. So I want to reinsert them and let user to edit them if needed.

I have a other question too , there are 2 tables that are related to table above and I have to reinsert the related records too for example there are 4 records in second table that related to the fist row of the first table .

I can do all of it with asp.net using loops and connecting and disconnecting to database per insert but it doesn't seems to be so wisely , I rather do it with a stored procedure .

Thank you in advance

This ought to work:

insert into table (...columns...) select ...columns... from table where ....

Assumes you have an identity column that is NOT in the list of fields (ie, let sql server handle that for you)

I don't understand your second question, ie, how is it different than the first?

|||

Thank you David ,

Abut my first question, there is a column that I have to change it's value , It concerns abut date of report how do I handle that ?I gues it must be something like this :
insert into table (...columns...) ,[TodayReportID] select ...columns... from table where .... ? @.TodayReportIDFor better understanding see the example of question 2My second question :Assume these are table above columns :
? [Table1ID] ,[ column1], [ column2], [TodayReportID]
1 AAAA BBBB 1
2 CCCC DDDD 1
3 EEEE FFFF 1
And there is an other table I name it Table 2 and [Table1ID] is a foreign key in it related to table 1 so all records below are related to 2^nd row of table one .
? [Table2ID] ,[Table1ID], [ column2]
1 2 GGG
2 2 HHH
3 2 KKK
Now assume I reinsert second row of table 1 it will be something like this :
? [Table1ID] ,[ column1], [ column2], [TodayReportID]
…
4 CCCC DDDD 2
Now I have to reinsert related row of Table2 too :
? [Table2ID] ,[Table1ID], [ column2]
4 4 GGG
5 4 HHH
6 4 KKK

Thank very much

|||

u can use trigger ... while inserting and data to a certain table it will then automatically call your trigger and update your second table.

/* */

in your table use data time field to separate your distinct date data.

|||Thank you ,But I still didn't get my answer of first question about extra column that I have to insert it manually (by a parameter ). And I'm not familiar with trigger yet so can you give a an example how to use a trigger to perform this task ? Thank you again

|||I don't think triggers was the best option, I need something like loop so I can select some records and from Table1 and reinsert records for each row.|||

If you want to change the date when the record is created it's easy, just supply the new value in the select list that's getting the original data, eg, this example puts current date into column 3

insert into table1 (col1, col2, col3, col4)
select col1, col2,getdate(), col4) from table1

I don't see any use for triggers in this example, btw

|||

thank you ,

I did this and it works :

Create PROCEDURE [dbo].[proc_DailyReport_CopyInformation](@.ReportIDDECIMAL,@.ReportNewDECIMAL)ASBEGINSET NOCOUNT ONDECLARE @.ErrintINSERT INTO DailyReport_Activity (ReportID,ActivityDesc,hajm,vahedID,Tozihat,WBS) SELECT ReportID=(@.ReportNew),ActivityDesc,hajm,vahedID,Tozihat,WBS From DailyReport_Activity where ReportID = @.ReportIDSET @.Err = @.@.ErrorRETURN @.ErrEND

but I didn't find out my secont question yet please help .

|||

I don't really understand your second question -- can you elaborate? How is it different than your first question, aren't you still just inerting records based on existing records?

|||The problem is I don't know how can I make loop so based on the records I reinserted in the first table I reinsert records in the second table .

Or I need reinsert related records to second table while I'm inserting in the first table .