October 19, 2012 at 12:33 pm
Some time ago I asked how to import images into SQL Server. At that time I was using a new table to insert images (Renner_Foto) and now I want to update an existing table. Has anyone any ideas to do this, because I cannot get it figured out (still a newbie I suppose). To start with: the code to insert into a new table was the following:
USE [WielerDatabaseSQL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- we checken eerst of de tabel al is gevuld. Zo ja, dan dienen de records
-- te worden gewist om foutmeldingen te voorkomen (dubbele records)
-- hiervoor gebruiken we 1 in plaats van * (testen op 1 record is sneller)
IF EXISTS (SELECT 1 FROM Renner_Foto)
BEGIN
DELETE Renner_Foto
END
-- we maken een tijdelijke tabel om de image-files in te kunnen lezen
-- we stoppen de benodigde SQL in de variabele InsertCMD
-- check of tijdelijke tabel al bestaat
IF OBJECT_ID('tempdb..#images','U') IS NOT NULL
DROP TABLE #images
-- we stoppen de benodigde SQL in een variabele genaamd InsertCMD
CREATE TABLE #images (files VARCHAR(500) NULL, InsertCMD
AS 'INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) '
+ ' SELECT ' + LEFT(files, PATINDEX('%[^0-9]%', files) - 1) + ' AS ImageID, '
+ '''' + files + ''' As ImageName, '
+ 'BulkColumn FROM OPENROWSET( Bulk '
+ '''D:\MijnDocumenten\DWCoureurLocaleData\Irfanview_20112011\' + files + ''','
+ ' SINGLE_BLOB) AS BLOB '
)
-- voer de images in in de tijdelijke tabel
INSERT INTO #images (files)
EXEC xp_cmdshell 'dir / B /O "D:\MijnDocumenten\DWCoureurLocaleData\Irfanview_20112011\"'
-- gooi alle files eruit, die NULL zijn of geen cijferpatroon 0-9 hebben
DELETE FROM #images WHERE files IS NULL OR PATINDEX('%[^0-9]%', files) = 0
-- nu gaan we de images in de tabel Renner_Foto invoeren
DECLARE @cmd VARCHAR(MAX)
DECLARE file_cursor CURSOR
FOR SELECT InsertCMD
FROM #images
ORDER BY files
OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @cmd
-- loop voor de invoer
WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT @cmd
EXEC (@cmd)
FETCH NEXT FROM file_cursor INTO @cmd
END
CLOSE file_cursor
DEALLOCATE file_cursor
-- SELECT * FROM dbo.Renner_Foto
If there is more information needed to answer the question, let me know.
Grz,
Robert
October 20, 2012 at 3:54 am
Unfortunately there are no replies on my post. There is one advantage though: I was obliged to create a solution myself. The code above was mainly made by Ninja and was pointed to insert a new row with an image to a table (first temporary (#images) and second fixed (Renner_Foto)).
With the necessary problems I succeeded in rebuilding it to an update statement pointed to insert an image in an existing row of the table (Athletes). We also use the temporary table #images again and present a loop. In that way more pictures canb be read into the database with one chuck of code.
Then the code:
USE [OlympicDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- we checken eerst of de tabel al is gevuld. Zo ja, dan dienen de records
-- te worden gewist om foutmeldingen te voorkomen (dubbele records)
-- hiervoor gebruiken we 1 in plaats van * (testen op 1 record is sneller)
--we gaan er vanuit dat er alleen nieuwe records worden ingevoerd
--IF EXISTS (SELECT 1 FROM Renner_Foto)
--BEGIN
--DELETE Renner_Foto
--END
-- we maken een tijdelijke tabel om de image-files in te kunnen lezen
-- we stoppen de benodigde SQL in de variabele InsertCMD
-- check of tijdelijke tabel al bestaat
IF OBJECT_ID('tempdb..#images','U') IS NOT NULL
DROP TABLE #images
-- define table
-- necessary SQL in variable 'UpdateCMD'
CREATE TABLE #images (files VARCHAR(500) NULL, UpdateCMD
AS 'UPDATE Athletes'
+ ' SET Picture ='
+ ' (SELECT BulkColumn FROM OPENROWSET( Bulk '
+ '''D:\MijnDocumenten\Athletes_Images\' + files + ''','
+ ' SINGLE_BLOB) AS BLOB) '
+ ' WHERE Athlete_ID = ' + LEFT(files, PATINDEX('%[^0-9]%', files) - 1))
-- voer de images in in de tijdelijke tabel
--SET IDENTITY_INSERT Athletes ON
INSERT INTO #images (files)
EXEC xp_cmdshell 'dir / B /O "D:\MijnDocumenten\Athletes_Images\"'
-- gooi alle files eruit, die NULL zijn of geen cijferpatroon 0-9 hebben
DELETE FROM #images WHERE files IS NULL OR PATINDEX('%[^0-9]%', files) = 0
-- nu gaan we de images in de tabel Renner_Foto invoeren
DECLARE @cmd VARCHAR(MAX)
DECLARE file_cursor CURSOR
FOR SELECT UpdateCMD
FROM #images
ORDER BY files
OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @cmd
-- loop voor de invoer
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print de SQL-code zoals geproduceerd door InsertCmd
PRINT @cmd
EXEC (@cmd)
FETCH NEXT FROM file_cursor INTO @cmd
END
CLOSE file_cursor
DEALLOCATE file_cursor
--SET IDENTITY_INSERT Athletes OFF
GO
SELECT * FROM dbo.Athletes
GO
Perhaps someone can use this approach to update an bulk/image field in existing rows in a table.
Grz,
Robert
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply