1

I have a Windows SQL Server database with a many-to-many relationship between some articles and categories, the relation is made by an ArticleCategorie table with two columns => [ArticleID] - [CategorieID].

The problem now is that I have a csv file which look like this (*picture) with two columns article id and article tags (let's say the article id is 5; and that it has 4 different categories which has respectively id 1,4,6 and 7) so here is how the CSV looks like => 

   |ArticleID----|Article Categories---|
   |5------------|1,4,6,7--------------|

*Picture :

enter image description here

The best way I found was to manually add to the table all data (articles + categories) like this :

http://oi40.tinypic.com/2h4x2s6.jpg

So this is above the final result that I want. Unfortunately I have more than seven hundreds articles, and can't find any faster way to import them, any solutions ? How can I import my data in a fast way? Maybe I could go for a better database design ?

Ydhem
  • 928
  • 2
  • 14
  • 36
  • You could use one of the various [Split](http://stackoverflow.com/a/16993267/1699210) routines that can be found on this side – bummi Jul 28 '13 at 07:30

1 Answers1

0

Its not an elegant solution but it works. Import the data from the csv file to a temporary table using openrowset or import data. Then run the following code

declare @aid varchar(88)
declare @cid varchar(88)
declare @cur as cursor
SET @cur = CURSOR FOR(SELECT * FROM temp)
open @cur
FETCH NEXT FROM @cur INTO @aid,@cid
while @@FETCH_STATUS=0
BEGIN
insert into article select @aid,value from dbo.Split(',',@cid)
FETCH NEXT FROM @cur INTO @aid,@cid
END
CLOSE @cur
DEALLOCATE @cur

Here is the code for the split() function

CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(5120))
RETURNS table
AS
RETURN (
WITH pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT
pn + 1,
stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 5120 END) AS Value
FROM pieces
)
Ejaz
  • 145
  • 1
  • 10