| 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 exampleINSERT 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, Locationfrom ##SourceTable swhere not exists ( select 1 from ##TargetTable t where t.ItemNumber = s.ItemNumber and t.Cost = s.Cost and t.Location = s.Location)[/code] |
 |
|
|
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. |
|
|
|
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)? |
|
|
|
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. |
|
|
|
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 |
|
|
|
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. |
|
|
|
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) |
|
|
|
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. |
|
|
|
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. |
|
|
|
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') |
|
|
|
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 |
|
|
|
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. |
|
|
|
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! |
|
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2014-08-21 : 19:55:33
|
| I don't want to use SSIS package. |
|
|
|
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! |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-22 : 07:49:55
|
| I should mention option 4: OPENROWSET |
|
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
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. |
|
|
|
Next Page
|
Insert a new record when value does not exist