1

I'm currently working on a stored procedure in T-SQL on SQL Server 2012. I need to merge 2 tables without an Id. The Id will be created on the insert into the first table. My problem is kind of tricky, thus maybe you can help me :)

In the stored procedure, the import table looks the following way:

CREATE TYPE [MySchema].[Target] AS TABLE
(
    IsPrivate BIT,
    IsPublic BIT,
    CountryId VARCHAR(100)
);
GO

@TARGETS MySchema.Target READONLY

Some possible values for the import @Targets:

IsPrivate | IsPublic | CountryId   |
----------+----------+-------------+
1         |   0      |   CA,FR     | 
0         |   1      |   US,GB     |

Desired output: these @Targets I need to split up into 2 tables, Target and Country:

  1. Create new entries in Target :

    TargetId | IsPrivate | IsPublic |
---------+-----------+----------+
23       |   1       |   1      |  
24       |   0       |   0      |

  2. Split up the CountryId into it's own table, Country and add the TargetId:

    Id | TargetId | CountryId |
---+----------+-----------+
 1 |   23     |   CA      | 
 2 |   23     |   FR      |
 3 |   24     |   US      |
 4 |   24     |   GB      |

My current query looks like this:

CREATE TABLE #tmpTarget (TargetId INT, CountryId VARCHAR(100));

INSERT INTO [MySchema].[Target]([IsPrivate], [IsPublic])
OUTPUT inserted.TargetId, CountryId INTO #tmpTarget
    SELECT IsPrivate, IsPublic
    FROM @TARGETS

Of course this query doesn't work. I'm currently thinking of how to solve this issue. Do you have some ideas or useful tips for me on how to solve this problem?

Thanks a lot! :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TimHorton
  • 865
  • 3
  • 13
  • 33
  • 3
    Never, ever store data as comma separated items. It will only cause you lots of trouble. – jarlh Apr 27 '17 at 09:11
  • Thanks, I'm only sending it over to SQL server as a comma separated item... – TimHorton Apr 27 '17 at 09:12
  • Is this import happens on a regular bases, or is it just a one time thing? – Zohar Peled Apr 27 '17 at 09:12
  • Can you control the data before it reaches sql server? – Zohar Peled Apr 27 '17 at 09:13
  • it's regular; each time i create a new target... – TimHorton Apr 27 '17 at 09:13
  • Yes, I can control the data... I'm using Dapper in C# to parse my object into a table valued parameter – TimHorton Apr 27 '17 at 09:14
  • 1
    Wouldn't it be much easier to send your data from C# in multiple rows rather than this comma separated strings? – Shnugo Apr 27 '17 at 09:20
  • hmm u mean, a "temporary common Id" for the country and the target table? could be an idea though ! – TimHorton Apr 27 '17 at 09:22
  • Then read [this SO post.](http://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage) It's the best way I know of to insert multiple records to multiple tables. btw, the map can simply be a table variable, you don't have to define a table type for it. – Zohar Peled Apr 27 '17 at 09:33
  • 1
    This might help you for country column 1) http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows 2) http://stackoverflow.com/questions/13873701/convert-comma-separated-column-value-to-rows – Rajesh Apr 27 '17 at 09:34

0 Answers0