SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 removing a record and putting in back
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pasi
Posting Yak Master

143 Posts

Posted - 04/04/2014 :  17:16:23  Show Profile  Reply with Quote
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

USA
36594 Posts

Posted - 04/04/2014 :  17:18:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Pasi
Posting Yak Master

143 Posts

Posted - 04/04/2014 :  17:20:29  Show Profile  Reply with Quote
OK thanks. how do I do this? what's the query? never done this before!
Thanks!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36594 Posts

Posted - 04/04/2014 :  17:22:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Pasi
Posting Yak Master

143 Posts

Posted - 04/04/2014 :  17:26:16  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36594 Posts

Posted - 04/04/2014 :  17:45:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Pasi
Posting Yak Master

143 Posts

Posted - 04/04/2014 :  18:24:48  Show Profile  Reply with Quote
Thanks Tara!
Go to Top of Page

Pasi
Posting Yak Master

143 Posts

Posted - 04/04/2014 :  18:33:03  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36594 Posts

Posted - 04/04/2014 :  18:48:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Pasi
Posting Yak Master

143 Posts

Posted - 04/04/2014 :  19:54:29  Show Profile  Reply with Quote
there are 117 columns, cant type all that. how do I put the default into timestamp?
Thanks.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36594 Posts

Posted - 04/05/2014 :  00:43:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Pasi
Posting Yak Master

143 Posts

Posted - 04/07/2014 :  11:48:32  Show Profile  Reply with Quote
Not sure what you mean by SSMS? Never done this before?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36594 Posts

Posted - 04/07/2014 :  13:16:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
SSMS = SQL Server Management Studio

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 04/07/2014 :  22:02:01  Show Profile  Reply with Quote
Does your complex calculation update the database? If not, just execute the DELETE statement then ROLLBACK when you're done.
Go to Top of Page

Pasi
Posting Yak Master

143 Posts

Posted - 04/08/2014 :  11:39:43  Show Profile  Reply with Quote
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.

Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 04/08/2014 :  20:30:19  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000