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 2012 Forums
 Transact-SQL (2012)
 Insert a new record when value does not exist

Author  Topic 

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-08-18 : 20:18:45
Consider a business in multiple locations and If a product exists in one place and not in other then i need it as 2 records in target. below is an example...

CREATE TABLE ##SourceTable(ItemNumber INT, Cost DECIMAL(9,2), Location VARCHAR(8))
INSERT INTO ##SourceTable VALUES(111, 20.00, 'NewYork')


Target table should look like....
CREATE TABLE ##TargetTable(ItemNumber INT, Cost DECIMAL(9,2), Location VARCHAR(8))
INSERT INTO ##TargetTable VALUES(111, 20.00, 'NewYork')
INSERT INTO ##TargetTable VALUES(111, 20.00, 'California')


But if two different values exists, then i need the output of the actual value that exists...below is an example

INSERT INTO ##SourceTable VALUES(222, 50.00, 'NewYork')
INSERT INTO ##SourceTable VALUES(222, 25.00, 'California')


Target table should look like
INSERT INTO ##TargetTable VALUES(222, 50.00, 'NewYork')
INSERT INTO ##TargetTable VALUES(222, 25.00, 'California')

Can someone please help? Thanks!!!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-19 : 07:53:32
[code]
insert into ##TargetTable(ItemNumber, Cost, Location)
select ItemNumber, Cost, Location
from ##SourceTable s
where not exists (
select 1 from ##TargetTable t
where t.ItemNumber = s.ItemNumber
and t.Cost = s.Cost
and t.Location = s.Location)
[/code]
Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-08-19 : 11:44:39
Thanks gbritton. it has million records in the source table and the target table is on a completely different server. We do not want to use linked server...this process will make it complicated.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-19 : 14:01:11
Well, you have to lookup source records in the target table to find duplicates. If you don't want to use a linked server, what's your plan?

BTW, what else are you not telling us about your use case (you didn't mention different servers before)?
Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-08-19 : 14:19:23
my plan is to use SSIS. To Push two records from source to target when a value does not exist by Location and leave the data as it is when the data exists in both locations. Only thing i am confused is how can i send the data as two records when i have only one record in source.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-20 : 08:15:05
SSIS? You should have said so in the first place (or posted in the SSIS forum).

For your problem I'd use the Slowly Changing Dimension transformation. If you're not familiar with it, read up on it. It takes an input table and compares it, column by column, to a reference table, then gives you the option of what to do when records are not matched: 1. Insert new rows into the reference table; 2. Update rows that have changed.

Here's the reference: http://msdn.microsoft.com/en-us/library/ms141715.aspx
Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-08-20 : 11:48:37
thanks for the link...i said SSIS because i will be using it only to move the data after the SELECT statement(Complete SQL statement will be in T-SQL). When there is only one record on sourcee...there is no way with SSIS to push two records.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-20 : 13:31:26
quote:
Originally posted by sql_server_dba

thanks for the link...i said SSIS because i will be using it only to move the data after the SELECT statement(Complete SQL statement will be in T-SQL). When there is only one record on sourcee...there is no way with SSIS to push two records.



Yes there is! (multiple ways, in fact) Using the SCD transform, it compares incoming records to the existing table and has an output connector for rows to be inserted, which can be 0, 1, 2, ... oo per existing record (well, infinity might be a problem)
Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-08-20 : 17:55:31
There may be cases but my case would cause some problems with it. Specially when we are talking about more than million records, better to use a T-SQL instead of SSIS.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-20 : 18:41:13
What? I gave you a tsql solution before you said you wanted to do it with ssis! I'm getting confused.

BTW a million rows is not a problem. It'll just take longer.
Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-08-21 : 18:40:27
That transact SQL that is provided need to compare target table and source table. But i do not want to compare data with target table because i may need to use linked servers at that point. Is there any other way that we can do this?

If it helps..there is another source table which consists of all the locations that ItemNumber exists...

CREATE TABLE ##SourceTable1(ItemNumber INT, Location VARCHAR(8))
INSERT INTO ##SourceTable VALUES(111, 'NewYork')
INSERT INTO ##SourceTable VALUES(111, 'California')
INSERT INTO ##SourceTable VALUES(222, 'NewYork')
INSERT INTO ##SourceTable VALUES(222, 'California')
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 18:52:13
quote:
Originally posted by sql_server_dba

That transact SQL that is provided need to compare target table and source table. But i do not want to compare data with target table because i may need to use linked servers at that point. Is there any other way that we can do this?

If it helps..there is another source table which consists of all the locations that ItemNumber exists...

CREATE TABLE ##SourceTable1(ItemNumber INT, Location VARCHAR(8))
INSERT INTO ##SourceTable VALUES(111, 'NewYork')
INSERT INTO ##SourceTable VALUES(111, 'California')
INSERT INTO ##SourceTable VALUES(222, 'NewYork')
INSERT INTO ##SourceTable VALUES(222, 'California')
T



You could ftp tables between sites and do the update as in my first example. Otherwise, use ssis
Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-08-21 : 19:00:03
in the example which i have given...there is only one column..but there can be many columns and it will be hard to compare each and every field. I thought there can be a way to SELECT the records directly from source.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 19:38:07
That's what the ssis slowly changing dimension transform is for. It does just that!
Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-08-21 : 19:55:33
I don't want to use SSIS package.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 20:17:17
You Don't want to use ssis even though it is custom made for your situation and you don't want to use SQL via a linked server. That leaves the ftp option. Any other method would only be much worse.

Good luck!
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-22 : 07:49:55
I should mention option 4: OPENROWSET
Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-08-22 : 18:04:16
As i mentioned..i do not want to use linked server and SSIS. There is a way that we can do this but i just can't get to it. I will let you know once i write the T-SQL.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-22 : 18:10:39
If you want to do it in SQL, openrowset is the only option left.

BTW, you haven't stated why you don't want to use SSIS. I'm curious.
Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-08-22 : 18:25:10
Actually there is already a process in production which is using T-SQL to send the data when there are records at Location level. I just need to add additional record when a new location gets entered into #SourceTable1 for the same ItemNumber with out any changes to #SourceTable.
Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-08-27 : 19:14:15
below steps can be used.........

-- Get all the multi location ItemNumbers
-- Get the list of multi location Item numbers those have amount in multiple locations.
-- Now pull item numbers that does not exist in 2nd statement.

all these statements are done on SOURCE server. No comparision with TARGET. No LINKED SERVERS.
Go to Top of Page
    Next Page

- Advertisement -