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 KizerSQL Server MVP since 2007http://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 yourtablewhere last_name = 'test' and first_name = 'test'Tara KizerSQL Server MVP since 2007http://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 #tempdrop table #tempTara KizerSQL Server MVP since 2007http://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 #tempdrop table #tempCannot 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 #tempTara KizerSQL Server MVP since 2007http://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 KizerSQL Server MVP since 2007http://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 StudioTara KizerSQL Server MVP since 2007http://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 T1Delete last_name from person where last_name like '%test%' rollback Tran T1quote: 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 T1Delete last_name from person where last_name like '%test%' ;<Do whatever it is you need to do>rollback Tran T1of 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. |
|
|
|