removing a record and putting in back

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

removing a record and putting in back

Author

Topic

Pasi
Posting Yak Master

166 Posts

Posted - 2014-04-04 : 17:16:23

Hi ,

is there a way to temporarily remove a record and then putting it back again? in my case I have lots of test patient with last)name ='test' and first_name='test'. I want to remove these test patient temporarily , do my calculation and them put them back.

The table is called "person" and all these test patients are in there including real patient under their last-name, first_name.

Appreciate any help!
Pasi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-04 : 17:18:29

Sure you can move them to another table, but I would just exclude them via the appropriate where clause.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-04-04 : 17:20:29

OK thanks. how do I do this? what's the query? never done this before!
Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-04 : 17:22:53

Just exclude them during your calculations:

select .......
from yourtable
where last_name = 'test' and first_name = 'test'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-04-04 : 17:26:16

NO I have to remove them for dB first , it does not work that way. I have to remove them, then I run another query to calculate and then I want to put them back. I have tried excluding them but it does not work. The calculation is very complex. its about 1400 line of codes!
I just need to remove all these test patient from dB first , run calculations and then put them back.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-04 : 17:45:54

select * into #temp from yourtable where last_name = 'test' and first_name = 'test'
delete from yourtable where last_name = 'test' and first_name = 'test'
...
insert into yourtable select * from #temp
drop table #temp

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-04-04 : 18:24:48

Thanks Tara!

Pasi
Posting Yak Master

166 Posts

Posted - 2014-04-04 : 18:33:03

when I try to inset back I get this error:

insert into person select * from #temp
drop table #temp

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-04 : 18:48:59

Provide a column list instead and exclude that timestamp column. INSERT INTO YourTable(ColumnName1, ColumnName2, ...) SELECT ColumnName1, ColumnName2... FROM #temp

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-04-04 : 19:54:29

there are 117 columns, cant type all that. how do I put the default into timestamp?
Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-05 : 00:43:39

You need to explicitly list them out. You can use SSMS to help you script it via the right click menu on the table.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-04-07 : 11:48:32

Not sure what you mean by SSMS? Never done this before?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-07 : 13:16:55

SSMS = SQL Server Management Studio

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2014-04-07 : 22:02:01

Does your complex calculation update the database? If not, just execute the DELETE statement then ROLLBACK when you're done.

Pasi
Posting Yak Master

166 Posts

Posted - 2014-04-08 : 11:39:43

Thanks lotz,
Since I haven't done this before can you give me a hint or help on how to do this? is this correct:

Begin Tran T1
Delete last_name from person where last_name like '%test%'

rollback Tran T1

quote:
Originally posted by LoztInSpace

Does your complex calculation update the database? If not, just execute the DELETE statement then ROLLBACK when you're done.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2014-04-08 : 20:30:19

Pretty much. Don't forget to do the actual work!
Begin Tran T1
Delete last_name from person where last_name like '%test%' ;

<Do whatever it is you need to do>

rollback Tran T1

of course if your process needs to update the database then it will rollback those changes too so this might not work. I can't say if it will or will not suit you - just a suggestion/pointer of something to try.
Good luck.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources