September 26, 2011 at 6:14 am
I have the following script :
insert into GODWIN_SalesOrderBOMs
(
GODWIN_SalesOrderBOMs.SalesOrder
,GODWIN_SalesOrderBOMs.SalesOrderLine
,GODWIN_SalesOrderBOMs.DispatchNote
,GODWIN_SalesOrderBOMs.DispatchNoteLine
,GODWIN_SalesOrderBOMs.MStockCode
,GODWIN_SalesOrderBOMs.ITTStockCode
,GODWIN_SalesOrderBOMs.MStockDes
,GODWIN_SalesOrderBOMs.MOrderQty
,GODWIN_SalesOrderBOMs.MQtyToDispatch
,GODWIN_SalesOrderBOMs.MUnitCost
,GODWIN_SalesOrderBOMs.MPrice
,GODWIN_SalesOrderBOMs.MProductClass
,GODWIN_SalesOrderBOMs.MLineShipDate
,GODWIN_SalesOrderBOMs.BOMTopLevel
,GODWIN_SalesOrderBOMs.BOMLevel
,GODWIN_SalesOrderBOMs.BOMAssembly
,GODWIN_SalesOrderBOMs.BOMComponent
,GODWIN_SalesOrderBOMs.BOMQtyPer
,GODWIN_SalesOrderBOMs.BOMRoute
)
select
[SalesOrder]
,[SalesOrderLine]
,[DispatchNote]
,[DispatchNoteLine]
,[MStockCode]
,AlternateKey1
,[MStockDes]
,[MOrderQty]
,[MQtyToDispatch]
,[MUnitCost]
,[MPrice]
,[MProductClass]
,[MLineShipDate]
,TopLevel as [BOMTopLevel]
,Level as [BOMLevel]
,Assembly as [BOMAssembly]
,Component as [BOMComponent]
,QtyPer as [BOMQtyPer]
,Route as [BOMRoute]
from MdnDetail
left outer join BOMTable
on MStockCode = TopLevel
left outer join InvMaster
on MStockCode = StockCode
Where DispatchStatus = '9' and LineType = '1'
I have been looking into the MERGE function within SQL 2008 - But due to the compatibility settings - MERGE is not allowed - so have been directed to using the INSERT into DESTINATION function ( scripts )
Just the one question - I have read the MSDN article and just wanted to check what the potential damage could be to an ERP system that is acessing the Database.
I need to create a procedure that appends records to a table only with the MERGE WHEN NOT MATCHED to TARGET options. Obviously using the compatibility level will allow me to do this - BUT - I do not want to break anything at the same time.
Is there an alternative "2005" way of doing a merge statement without the merge statement ?
Basically I want to update a "Snapshot" table at point of Despatch and that only appends records that have not already been added. I suppose an INSERT into which Record = NULL ? loop ?
Thanks in advance
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 26, 2011 at 6:23 am
The usual way to do that in 2000/2005 is to join to the target table in your Select From and set up that query to exclude any rows that already exist. A Left Outer join is the most common way to do it. In 2005, you might be able to set up an "Except" type query, but that depends on all columns being the same, so a Left Outer Join is usually easier and better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 26, 2011 at 6:39 am
Absolutely 100% perfect -
Just added the target table in the select and added the table.alias - added when target is null
bobs your uncle !
Thank you very much for your direction.
Surprisingly simple solution to a complex problem !
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 27, 2011 at 6:22 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 27, 2011 at 11:01 am
Steve,
There are 2 other methods to do this. I suggest you read this article by MVP Gail Shaw to become fully rounded on the subject:
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
Todd Fifield
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply