2

I have a table which looks like this:

firstname    lastname      address      city
    a          b           (c,d,e)      (f,g,h)

I want to make a series of inserts like this:

insert (a,b,c,f)
insert (a,b,c,g)
insert (a,b,c,h)
insert (a,b,d,f)
insert (a,b,d,g)
insert (a,b,e,h)
....

What is the best way to do that in SQL Server without using multiple cursors?

Andriy M
  • 76,112
  • 17
  • 94
  • 154
user3334778
  • 33
  • 4
  • That table and those insert clauses don't really match -- and there is no cursor anywhere – James Z Jun 21 '15 at 07:42
  • 1
    Your question is not very clear. what exactly do you want to do? – Zohar Peled Jun 21 '15 at 07:44
  • i should split address and city by a comma and make an insert of all combinations – user3334778 Jun 21 '15 at 07:49
  • Storing multiple different data on the same row is usually not a good idea. Better would be to have a separate address table that would have one address per row and it would have person_id or something like that to link to the names – James Z Jun 21 '15 at 09:04

2 Answers2

6

Simply use cross apply with your table and some string splitting udf.
You can find the best one for you in this article.

I've used for this demonstration the SplitStrings_Moden function from the article I've linked to:

CREATE FUNCTION dbo.SplitStrings_Moden
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
    FROM cteStart s;

Once you have the splitting function, all you have to do is select the values from your table and use cross apply with this function for each comma delimited column you have:

-- INSERT INTO MyTable(firstname, lastname, address, city)
SELECT firstname, lastname, a.item, b.item
FROM MyTable
cross apply dbo.SplitStrings_Moden([address], ',') a
cross apply dbo.SplitStrings_Moden(city, ',') b

See fiddle here

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Which udf is newer/better yours or [this](http://stackoverflow.com/a/29961649/859154) ? – Royi Namir Jun 21 '15 at 08:47
  • @RoyiNamir I think they are almost the same (note that the split function in my answer is called `SplitStrings_Moden`, the same guy that wrote the split function in wewesthemenace's answer). Read the article I've linked to (or just look at the graphs and go to the Conclusion paragraph, apparently, Adam Machanic's CLR function gives the best performance in almost every case. – Zohar Peled Jun 21 '15 at 08:55
  • Except "the same guy" doesn't really like it when his code is changed to the point that it starts performing poorly while people still call it "a Moden function". Have you read the comments under the SQLPerformance article you've linked in your answer, Jeff Moden's comments in particular? Apparently his original function isn't really supposed to work with `max` types. – Andriy M Jun 21 '15 at 09:08
  • Other than that, the idea of using a split function makes perfect sense if the input is indeed provided in the form of CSV strings. – Andriy M Jun 21 '15 at 09:18
  • @AndriyM The exact splitting function is not the main issue in my answer. the main issue is that using a splitting function with cross apply gives a dynamic, pure t-sql solution to the OP's question. I admit I didn't read the comments to the article, but since the splitting function is not the main issue here, I don't think it's that relevant to this answer. If it will make you, Jeff, or anyone happy, I will happily edit my answer to change the split function to a different one. – Zohar Peled Jun 21 '15 at 09:19
  • For my part, it was no more than a heads-up that the function's author may not like it, given the extended discussion under the article. I agree, though, that the main point here is to use a split function and you've provided a source with a selection of them all right. – Andriy M Jun 21 '15 at 09:34
  • @AndriyM Thanks for pointing that out - I didn't notice it. BTW , do you know a split UDF ( which does work with `max`) ? – Royi Namir Jun 21 '15 at 18:18
  • @RoyiNamir: The [article we've been discussing](http://sqlperformance.com/2012/07/t-sql-queries/split-strings) offers (and actually tests) several methods implemented as functions with a `max` argument. – Andriy M Jun 21 '15 at 18:48
  • @RoyiNamir wasn't that what you are asking for? do you need a split function that does work with `max` or one that doesn't? – Zohar Peled Jun 22 '15 at 05:04
3

You could create temp tables and generate an insert into ... select from a cartesian join:

create table #address(address varchar(1) not null)

insert into #address
values ('c'), ('d'), ('e')

create table #city(city varchar(1) not null)

insert into #city
values ('f'), ('g'), ('h')

insert into my_table
select 'a', 'b', address, city
from #address, #city

http://sqlfiddle.com/#!6/82b9a/2

Yosef Weiner
  • 5,432
  • 1
  • 24
  • 37