Insert based off 2 table checks

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

SQL Server 2008 Forums

Transact-SQL (2008)

Insert based off 2 table checks

Author

Topic

Vanpire
Starting Member

7 Posts

Posted - 2013-04-14 : 18:56:04

I've been struggling with this for a while now - could use some advice.

We have a situation within MDS whereby when table1 gets a new entry (lets say "code) then it must check if the entry exists in table2, and if not update it. normally we would use domain based attributes for this however the following is the workflow we need:

Table1 code update

Check table 3 to see if code is a specific gender code

Check if code(s) already exist in table 2, if not:

If is "kids" then insert code from table 3 (multiple codes if kids eg sku1sizeS, sku2sizeM, etc)

If is not Kids then insert code from table1

I've been toying with the idea of using temp tables to do this step by step, but some help would be awesome.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-14 : 19:04:04

If you can post some sample data along with the table schema and results that you are looking for, that would make it easier for someone to respond. If you need help in formulating it in an easily consumable manner, take a look at this article: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Vanpire
Starting Member

7 Posts

Posted - 2013-04-14 : 19:05:44

Thanks yes - I'm just writing quick overview of the schema.

Vanpire
Starting Member

7 Posts

Posted - 2013-04-14 : 19:35:57

I have removed the insert and just kept it as a select to make it a bit simpler - I have also left the table names as making them all t1, t2 etc made it hard to read.

[ code] Select distinct

(Case
when sr.Gender_Name in ('Kids','Unisex Kids','Girls','Boys') then sd.Name
else scr.Code end)as [Code]

from
mdm.StyleColorRegister scr
inner JOIN MDM.StyleRegister sr on scr.Style_Code = sr.Code
inner JOIN mdm.SKUDetail sd on scr.Code =sd.StyleColour_Code
where NOT EXISTS (select * from mdm.TariffCodes tc
where scr.code=tc.code) -- tariffcodes table is final insert table

-- sd.name example data : 100118D34L, 100118D34M, 100118D34S, 100118D34XS (one for each size, usually 5)

-- scr.code example data : 100116J8610 (only ever one)

[ /code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-15 : 02:25:39

which table you want to update? If you want to do an upsert (update + insert) based on existence check in another table, use MERGE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Vanpire
Starting Member

7 Posts

Posted - 2013-04-15 : 17:25:03

Thanks - the reason it wasn't working was that I didn't check to see if sd.name existed in the tariff table.

It's sorted now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-16 : 02:12:19

welcome

glad that you got it sorted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources