inserting multiple entries as CSV / array?

[craigey]

Hi,

I'm trying to insert some values into a SQL server 2005 db & I'm aware that it doesn't support arrays. However it would make my life a lot easier if it did. I'm inserting some translated words into the language table, but was hoping I could have a list of the translations & a list of the id's & import these using a stored procedure or insert statement.

declare @locale as nvarchar(5)
declare @LOCname as nvarchar(25)
set @locale = 'de_DE'

set @ENGname = 'Performance'
SET @LOCname = 'Performance in german'
INSERT INTO translation  (Id, Name, Locale, Category) SELECT itemid,@LOCName,@locale,'item' from items where itemname = @engname
UPDATE tl SET tl.name = @LOCName FROM items it INNER JOIN translation tl ON CAST(it.itemid AS NVARCHAR(5))= tl.id WHERE tl.locale = @locale AND it.itemname = @ENGName

set @ENGname = 'Users'
SET @LOCname = 'users in german'
INSERT INTO translation  (Id, Name, Locale, Category) SELECT itemid,@LOCName,@locale,'item' from items where itemname = @engname
UPDATE tl SET tl.name = @LOCName FROM items it INNER JOIN translation tl ON CAST(it.itemid AS NVARCHAR(5))= tl.id WHERE tl.locale = @locale AND it.itemname = @ENGName


DECLARE @folderid AS NVARCHAR(25)
set @folderid = 'home' --for folders we use ID not the english name
SET @LOCname = 'home in german'
INSERT INTO translation (Id, Name, Locale, Category) VALUES (@folderid,@LOCname,@locale,'folder')
UPDATE translation SET name = @LOCname WHERE locale = @locale AND id = @folderid

I was hoping not to have a mass of these insert statements & just have a list of the translations (LOCname or folderid & German), which I could parse to a stored procedure.

I hope I've made myself clear in my explanation. If not I'll attempt to rephrase.

Thanks,

Craig

 

[philhege]

Post your schema and some sample data, including a sample of the list/array you refer to, and someone could get started on it.

-------++NO CARRIER++-------

 

[craigey]

Sorry for the delay in responding, but have been caught up with other projects.

The items table looks like:

itemid, name, url

1, door, [URL unfurl="true"]www.something[/URL]
2, handle, [URL unfurl="true"]www.something[/URL]
3, glass, [URL unfurl="true"]www.something[/URL]
4, house, [URL unfurl="true"]www.something[/URL]
5, brick, [URL unfurl="true"]www.something[/URL]
6, lighting, [URL unfurl="true"]www.something[/URL]

The folder table (similar to items, but contains categories rather than individual items)

folderid, foldername, displayorder
commercial, commercial A-G, 1
commercial, commercial H-N, 2
commercial, commercial O-Z, 3
office, offices, 15

The translation table looks something like:

Id, Name, Locale, Category

with entries of:

1, porte, FR_FR, item
2, poignée, FR_FR, item
3, de verre, FR_FR, item
4, Maison, FR_FR, item
5, de briques, FR_FR, item
6, l'éclairage, FR_FR, item

I'm trying to insert some german translations into the table by specifying the English version of the word that's contained in the items table.
So insert 'german word for door' where locale = 'EN_GB' and name = 'english word for door' use the same id number & Category as the English entry in the items table.
For the folder items i would use the folderid rather then the itemid.

I need to do this for multiple entries & would like to have something like a stored procedure that will accept two parameters (the english & the german words). If it could process a list of words, that would be fantastic.

i.e. SP_translate door,handle,glass,house,brick,lighting Tür,Griff,Glas,Haus,Ziegel,Beleuchtung

Although the translation is static. (The German word for brick will always be Ziegel), the Id's will be different across different sites & it's possible that people may have added extra categories, which is why i need to use the item id that is in the items table.


I hope that clears things up a bit.

 

[philhege]

What is the foreign key for the folder table?

-------++NO CARRIER++-------