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 .

regular expressions in transact sql

hi guys,

i need some help tp write code in order to search the string ( regular expressions) in t- sql.

e.g.

when a user enters [A-Z] it means any alphabet from 'A' to 'Z'...

similarly [0-9] means any digit.

the problem is: when user enters [0-6] and the string received contains digit 5 it should return true but if it contains 7 it should return false.

so how do i read the [A-Z] as a range of characters in t-sql?

hi,

In sql server it is not exactly regular expression, it is called wild card pattern. in other words it is simplified reqular expression,

as of now SQL Server Like operator only work with following operators

% - Zero or any number of chars

_ - Single Char

[] - Single Char in given range

- Single Char not in given range

if you want to utilize the exact regular expression on your query then the best solution will be CLR Functions.(SQL Server 2005).

For fixed validation (only numbers & only alphabets) i achived the following function,

Create Function dbo.IsMatching(@.Value as varchar(1000), @.Pattern as varchar(100))
returns bit as
Begin
Declare @.Len as int;
Declare @.SearchPattern as varchar(8000);
Declare @.Result as Int;

Select @.Len = Len(@.Value);

While @.Len>0
Begin
Select @.SearchPattern = Isnull(@.SearchPattern,'') + @.Pattern;
Select @.Len = @.Len -1;
End
Select @.Result = Case When @.Value Like @.SearchPattern Then 1 Else 0 End;
Return @.Result;
End

Go

select dbo.IsMatching('SQLServer','[A-Z]') as Result

Result : 1

select dbo.IsMatching('SQL Server','[A-Z]') as Result

Result : 0 --Space on String

select dbo.IsMatching('SQL Server','[A-Z ]') as Result

Result : 1 --Space added on Pattern

select dbo.IsMatching('12453','[1-5]') as Result

Result : 1

select dbo.IsMatching('12463','[1-5]') as Result

Result : 0

|||

thanks mani,

got the [A-Z] and [^a-z] concept.

my other requirements are to match zero or more characters and to match one or more characters.

e.g. T*he should match he, the, tthe, ttttthe.. etc.

and t+ho should match tho, thho, thhhhhhho, thhhhhhhhhhhhhho.. etc.

the above operators i have used in VC++,

do they work in t-sql too?

|||If you need regular expression and your platform is sqlserver 2005 you can use a CLR strored procedure. If you need help on this post a question on the .net framework inside sql server forum
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=86&SiteID=1|||

Yes.. if you want to find the repeated chars you can use the following condtions....

columnname not like '%aaa%'
and columnname not like '%bbb%'
and columnname not like '%ccc%'
and columnname not like '%ddd%'
and columnname not like '%eee%'
and columnname not like '%fff%'
and columnname not like '%ggg%'
and columnname not like '%hhh%'
and columnname not like '%iiii%'
and columnname not like '%jjj%'
and columnname not like '%kkk%'
and columnname not like '%lll%'
and columnname not like '%mmm%'
and columnname not like '%nnn%'
and columnname not like '%ooo%'
and columnname not like '%ppp%'
and columnname not like '%qqq%'
and columnname not like '%rrr%'
and columnname not like '%sss%'
and columnname not like '%ttt%'
and columnname not like '%uuu%'
and columnname not like '%vvv%'
and columnname not like '%www%'
and columnname not like '%xxx%'
and columnname not like '%yyy%'
and columnname not like '%zzz%'

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=996813&SiteID=1

If you want to utilize the exact regular expression as i said earlier you can go for CLR Functions..