| Author |
Topic |
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
 Posted - 2009-01-14 : 09:31:34
|
| hi all. need help again Code:INSERT INTO Harvest.dbo.BankNote ( BankAccountID , UserID , BankNote , BankNoteDate ) SELECT ba.BankAccountID , (SELECT TOP 1 um.UserID FROM Harvest.dbo.UserMap WHERE um.SystemUser = 'ronanhealy') -- specifi user for imports AS UserID , cb.Notes , CONVERT(datetime,CONVERT(char,GETDATE(),101),101) -- Set to no hour so we can identify FROM Harvest.dbo.BankAccount ba INNER JOIN [HARVEST-SRV-1\MAES].Maes_harvest.dbo.Cash_Balances cb ON ba.BankNumber = cb.Account_No AND ba.CurrencyID = cb.Currency now what i need to do is a delete where is will we will flush out all prior imported ones and insert all available from the other db. |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-01-14 : 09:56:30
|
| Are you saying you want to find rows which already exist which INSERT would create; delete them then do the INSERT? |
 |
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2009-01-14 : 10:00:39
|
| ya its going to be update on a regular basic so i was asked to come up with a delete for it first |
|
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2009-01-14 : 10:03:22
|
| this is how it was put to meso to simplify everything, the next thing to do is to put a delete of proir imported ones first, that way we will keep CIMA(one app) in synch with MAES(the other app), and deleting prevous ones is a lot easier than insert, update and delete Simply put, we will flush out all prior imported ones and insert all available from MAES, each tiem we want to do an update |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-14 : 10:04:56
|
| then shouldnt a DELETE FROM table be enough? |
|
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2009-01-14 : 10:08:02
|
| thats what i was thinkin but i just wanted another option in case it was something else |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-14 : 10:09:50
|
quote:
Originally posted by rjhe22
thats what i was thinkin but i just wanted another option in case it was something else
provided your table contains only previously imported values then delete from yourtable is enough |
|
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2009-01-14 : 10:11:55
|
| ya it does the banknote table has just what i imported using the top query |
|
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2009-01-15 : 05:10:29
|
| im not actually going to delete them all just the ones that are updated from the other app so what im trying to do is delete where userID is the same subquery as used in the insert,(how do i use that in my delete) and the Notes date has datepart hour and datepart minute = 0 |
|
|
|
|
delete sql