SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Insert a new record when value does not exist
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

sql_server_dba
Posting Yak Master

160 Posts

Posted - 08/18/2014 :  20:18:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/19/2014 :  07:53:32  Show Profile  Reply with Quote

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)
Go to Top of Page

sql_server_dba
Posting Yak Master

160 Posts

Posted - 08/19/2014 :  11:44:39  Show Profile  Reply with Quote
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.

Edited by - sql_server_dba on 08/19/2014 11:59:21
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/19/2014 :  14:01:11  Show Profile  Reply with Quote
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

160 Posts

Posted - 08/19/2014 :  14:19:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/20/2014 :  08:15:05  Show Profile  Reply with Quote
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

160 Posts

Posted - 08/20/2014 :  11:48:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/20/2014 :  13:31:26  Show Profile  Reply with Quote
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

160 Posts

Posted - 08/20/2014 :  17:55:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/20/2014 :  18:41:13  Show Profile  Reply with Quote
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.

Edited by - gbritton on 08/20/2014 18:42:11
Go to Top of Page

sql_server_dba
Posting Yak Master

160 Posts

Posted - 08/21/2014 :  18:40:27  Show Profile  Reply with Quote
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')

Edited by - sql_server_dba on 08/21/2014 18:46:39
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/21/2014 :  18:52:13  Show Profile  Reply with Quote
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

160 Posts

Posted - 08/21/2014 :  19:00:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/21/2014 :  19:38:07  Show Profile  Reply with Quote
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

160 Posts

Posted - 08/21/2014 :  19:55:33  Show Profile  Reply with Quote
I don't want to use SSIS package.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/21/2014 :  20:17:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/22/2014 :  07:49:55  Show Profile  Reply with Quote
I should mention option 4: OPENROWSET
Go to Top of Page

sql_server_dba
Posting Yak Master

160 Posts

Posted - 08/22/2014 :  18:04:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1116 Posts

Posted - 08/22/2014 :  18:10:39  Show Profile  Reply with Quote
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

160 Posts

Posted - 08/22/2014 :  18:25:10  Show Profile  Reply with Quote
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

160 Posts

Posted - 08/27/2014 :  19:14:15  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000