Thursday, May 14, 2009

How to upload/modify more than one BLOB column in single statement

Recently, I have been visited our client who has one table with two columns defined as VARBINARY(MAX)to store the images. How can we insert/upade these columns with single statements? I have not used blobs too much ,so I ended up with the following

CREATE TABLE Blobs (id INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,b1 VARBINARY(MAX),b2 VARBINARY(MAX))

---Single INSERT statement

INSERT INTO Blobs (b1,b2)
(SELECT *,(SELECT * FROM
Openrowset(Bulk 'N:\OnePicture.JPG', SINGLE_BLOB) AS d1)
FROM
Openrowset(Bulk 'N:\AnotherPicture.JPG', SINGLE_BLOB) AS d2)

--Update

UPDATE Blobs SET b1=(SELECT * FROM
Openrowset(Bulk 'N:\AnotherPicture.JPG', SINGLE_BLOB) AS d2),
b2=(SELECT * FROM
Openrowset(Bulk 'N:\OnePicture.JPG', SINGLE_BLOB) AS d2)
---WHERE condition put here

33 comments:

vedro said...

Hello

I am writting back to your post about my problem that I have described in your post: Scheduling database backup in SQL 2005 Express Edition

I am desperate and I totaly need your help. I just cannot put the commands into one piece of code. Could you please write me a script that will do the job I have written in the upper post.
I would be very gratefull.

Thank you,
Vedran Budinski

Uri Dimant said...

Vedro
Can you post the script you working for? Did my suggestion help you?

vedro said...

Hello

The plan with the databases is:
I have a database GK.dbo (the original databese) and I have created a "backUP" database GK_2009.dbo (it is created from the original database. so it has the same structure)
Every year a new "backUP" database will be created as a part of regular anual service, so there is no need to automaticaly create a new "backUP" database.
Like I have mentioned, the GK.dbo database has 2 tables: "Podatki" (in my own language, in english: Data) and "Alarmi" (in my own language, in english: Alarms).
Table "Podatki" needs to be copied to "backUP" database once monthly. After that it has to be "truncated".
Table "Alarmi" needs to be copied to "backUP" database once yearly. After that it has to be "truncated".
So in generaly, there is no true backUP process, there is just table copying to another identical database.

So now, after a little thinking I decided that I want to create 2 stored procedures, one for copying & truncating for the table "Podatki" and one for the table "Alarmi".
The whole scheduling will be performed from SCADA, so infact I need 2 scripts, that can be uses also for windows scheduled tasks to run those two stored procedures.

Stored procedure for table "Podatki":

CREATE PROCEDURE usp_BackupTablePodatki
SELECT * INTO GK_2009.dbo.Podatki FROM GK.dbo.Podatki
TRUNCATE table Podatki --this needs to be done in the GK.dbo database


Stored procedure for table "Alarmi":

CREATE PROCEDURE usp_BackupTableAlarmi
SELECT * INTO GK_2009.dbo.Alarmi FROM GK.dbo.Alarmi
TRUNCATE table Alarmi --this needs to be done in the GK.dbo database

So, I came with my stored procedures to here...
Now, I am in the dark. Could you please edit those two stored procedures so they would work? Can it be possible to veriffy that the data was successfuly copied before the table is truncated?
Also, how would a script for a scheduled task look like?

I would be very gratefull for your help.

Regards,
Vedran Budinski

Uri Dimant said...

Hi
What does 'would work' mean? Do you get the error? I would create (for month table to be backed up) permanent tables like
GK_2009_05 (May)
GK_2009_06 (June)
and then INSERT INTO .. FROM . Please use BEGIN TRY to catch errors to validate the data.

Please read the following
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27

Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29

vedro said...

Hello

What I mean with "would work" that I do not know how to write a working stored procedure script that would work :(
Not that I do not try, just that this is for me totaly new and I am stuck at that what I have written as a stored procedure in my upper post.
Also the time is not any more on my side.
That is why I am asking you for to help me.
The idea with permanent tables is also a good idea. But if it is easier to copy to one single table, than also is ok.

I have read the links for "Automating Database maintenance in SQL 2005 Express Edition Part I and II".
ExpressMaint works but I do not need a true database backUP (.bak file), but to copy table from one table from another. Ok, this is to somehow a backup but not in the "true way".
I have also looked at expressmaint TSQL code, but do not know how to apply/possibly modify for my needs.

I would be very gratefull for your help.

regards,
Vedran Budinski

Uri Dimant said...

Your code looks OK. I would add a validation for if the INSERT failed, rollabck all transaction

CREATE PROCEDURE pp
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
BEGIN TRY
....
....
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE();
END CATCH;

vedro said...

Hello

Thank you for your help.
So now I have a Stored procedure:

CREATE PROCEDURE usp_BackupTableAlarmi
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
BEGIN TRY
SELECT * INTO GK_2009.dbo.Alarmi FROM GK.dbo.Alarmi
TRUNCATE table Alarmi
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE();
END CATCH;

Just to make sure: I have chosen SELECT * INTO ...FROM command.
Would the INSERT INTO .. FROM command also work?

I have loaded the script: >sqlcmd -S .\SQLExpress -i c:\alarmi.sql

No errors were there (just when I wanted to load it again I got this message: Msg 2714, Level 16, State 3, Server LOLEK\SQLEXPRESS, Procedure usp_BackupTableA
larmi, Line 13
There is already an object named 'usp_BackupTableAlarmi' in the database.

So I presume, that the script is loaded.
But nothing is done. The table from the first database is as it is, and in the database GK_2009, the table is empty.
What have I forgotten to do?

Regards,
Vedran Budinski

Uri Dimant said...

Hi
SELECT * INTO... and INSERT INTO are different statements. First is createad a table , the second assumes that the table is already exists.
I woulfd go with SELECT * INTO but checked before the table is existed

IF OBJECT ('tbl') IS NULL
SELECT * INTO tbl FROM.....


Can you show me does the alarmi.sql
look like?

vedro said...

Hello

So this is the difference with thise two statemnets.
I have already a table in witch the data will be copied - I created a database with the same table structure.

my alarmi.sql looks like this:

CREATE PROCEDURE usp_BackupTableAlarmi
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
BEGIN TRY
SELECT * INTO GK_2009.dbo.Alarmi FROM GK.dbo.Alarmi
TRUNCATE table Alarmi
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE();
END CATCH;


One more question (hope not it is not a dumb one). How does the script know that it has to truncate the table from the database GK? (and not from GK_2009).
The statement is TRUNCATE table Alarmi. But it is not defined from witch database.

Regards,
Vedran Budinski

Uri Dimant said...

No, you alarm.sql should look like
usp_BackupTableAlarmi

/*
How does the script know that it has to truncate the table from the database GK? (and not from GK_2009
*/

Where does this sp locate? If it is located in GK that's no problem, it it is not you add

TRUNCATE table GK..Alarmi

vedro said...

Hello

Hmmm, then I think I got it a little bit wrong with my understanding of stored procedures script.

I have "played" a little bit with SQL Server Management Studio Express and maybe got it the right way.
Please correct me if any of the further steps are wrong:

1.) Open the SQL Server Management Studio Express --> connect to the SQL server
2.)In the "Object explorer" chose the wanted database (in my case: GK)
3.)expand the "tree" for the selected database.
4.)expand the "Programmability" branch
5.) right-click on the "Stored procedures", click "New stored procedure..."
6.)than the Template generated from Template Explorer using...
witch opens the following template

================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Author,,Name
-- Create date: Create Date,,
-- Description: Description,,
-- =============================================
CREATE PROCEDURE Procedure_Name, sysname, ProcedureName
-- Add the parameters for the stored procedure here
@Param1, sysname, @p1 Datatype_For_Param1, , int = Default_Value_For_Param1, , 0,
@Param2, sysname, @p Datatype_For_Param2, , int = Default_Value_For_Param2, , 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT @Param1, sysname, @p1, @Param2, sysname, @p2
END
GO

So I presume that I have to insert the stored procedure in here.
Now, there are segments in that template in witch the parts of the code have to be inserted. For example: "-- Insert statements for procedure here"
I have to stick to that? Or can I delete allthe contents of this template and just insert my code for the stored procedure?

7.)When I am finished, I have to save this stored procedure. For example backup_alarmi.sql . I am offered to save it to My Documents\SQL Server Management Studio Express\Projects
I presume that this location is OK.

8.)I crate alarmi.sql (I use New text document and than I save it as .sql file), witch contains only: usp_BackupTableAlarmi

And in finish, I execute: qlcmd -S .\SQLExpress -i c:\alarmi.sql
And that wil execute the stored procedure witch was created in the SQL Server Management Studio and saved into backup_alarmi.sql.

Is this the way this will work?
Now, I know that I am starting to be anoying, but I want to understand how this will work, so I will have some knowledge more after this project. And you helped me allot with understanding.

Regards,
Vedran Budinski

vedro said...

Hello

I have figured out how to execute the stored procedure :)

Now I have an issue:
When I execute the stored procedure: sqlcmd -S .\SQLExpress -i c:\alarmi.sql

I get this from the cmd:
C:\>sqlcmd -S .\SQLExpress -i c:\alarmi.sql

(0 rows affected)

(0 rows affected)
There is already an object named 'Alarmi' in the database.

This message occours when there is a table Alarmi in the GK_2009 database

If I delete/rename the table Alarmi, I get the following message:

C:\>sqlcmd -S .\SQLExpress -i c:\alarmi.sql

(2798 rows affected)

(2798 rows affected)
Cannot find the object "Alarmi" because it does not exist or you do not have permissions.

The stored procedure in GK database is:

USE GK;
GO
CREATE PROCEDURE usp_BackupTableAlarmi
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
BEGIN TRY
SELECT * INTO GK_2009.dbo.Alarmi FROM GK.dbo.Alarmi
TRUNCATE table Alarmi
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE();
END CATCH;


What could be the problem?

Regards,
Vedran Budinski

vedro said...

Hello

I am happy :)
I have solved the issue.
I had to edit permissions and, SELECT statements - I have defined rows in the table and the script works like a charm :)

Thank you allot for all of your help. I will keep you blog in my bookmarks. I hope I was not a terrible nag and hope that I can possibly expect some help in the future.

If the road ever takes you/ and your familiy to our little contry - Slovenia, don`t hesitate to contact me. I can show you the little green piece of Europe, as our country is :)

Again, thank allot!

Regards,
Vedran Budinski

Uri Dimant said...

Great news,I'm glad you solved the problem and thank you for warm words

products said...

China Wholesale has been described as the world’s factory. This phenomenom is typified by the rise ofbusiness. Incredible range of products available with China Wholesalers “Low Price and High Quality” not only reaches directly to their target clients worldwide but also ensures that wholesale from china from China means margins you cannot find elsewhere and buy products wholesaleChina Wholesale will skyroket your profits.wedding dressescheap naruto cosplayanime cosplay

products said...

Women’s nike tn Shox Rivalry est le modèle féminin le plus tendance de baskets pour le sport. tn chaussuresConcernant la semelle :spyder jacketsCheap Brand Jeans Shop - True Religion Jeans cheap nike shox & Puma Shoes Online- tn nike,Diesel Jeans le caoutchouc extérieur, l’EVA intermédiaire Levis Jeanset le textile intérieur s’associent pour attribuer à la.ed hardy shirts pretty fitCharlestoncheap columbia jackets. turned a pair of double plays to do the trick.Lacoste Polo Shirts, puma basket, Burberry Polo Shirts.wholesale Lacoste polo shirts and cheap polo shirtswith great price.Thank you so much!!cheap polo shirts men'ssweate,gillette mach3 razor bladesfor men.As for Cheap Evisu JeansCheap Armani Jeanspolo shirtsPuma shoes

Replica Watches said...

29047126483369175 I play dofus Replica Watches for one year, I Replica Rolex Watches want to get some Replica Watch kamas to buy Replica Chanel Watches item for my character. So, I search "Replica Swiss Watches" on google and found many website. As Exact Replica Graham Watch the tips from the forum, I just review the Swiss Replica Watches websites and choose some Replica Montblanc Watches quality sites to Replica Cartier Watches compare the price, and go to their Replica Breguet Watches online support to make Replica Breitling Watches the test. And Last Chaos Gold I decide to use Replica BRM Watch at the end. And Tag Heuer Replica Watch that is the Replica IWC Watch beginning..

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!

rscredits said...

If a job is worth doing it is worth doing well.What runescape gold you share here just prove this saying, i like your thought here, rs gold which is special and informative.

This topic is pretty good and the writer's views are buy runescape gold unique and not so hard to understand.I like it very much, and by the cheap rs gold way I want to share some interesting and very useful websites to you:www.rsorder.com! Iam sure you can find out funny things there.

chinatour.com said...

Such a nice post, We loved it a great deal!You can learn more: China tour packages | China travel packages | China Travel Agency

love supra said...

Thanks. I always enjoy reading your posts - they are always humorous and intelligent.You can learn more:China tours | China tour packages | China travel packages

love thomass said...

Welcome to the leading online Mandarin school that makes you fluent in a short time -- all from the comfort of your home or office!

I am a Chinese teacher,you can learn more about Free Chinese language info: Mandarin Chinese teacher | Online Chinese teachers | Native chinese teacher

Learn Chinese language on hanbridgemandarin said...

The best place to learn Chinese online is in China. However, we understand that it isn't always possible to move here to learn Chinese language. The next best thing is to study with our experienced teachers in a virtual classroom. Online students enjoy the same excellent way of mandarin online lessons and custom designed courseware that we provide for our face to face clients.

Dagdgsd Dffbd said...

Nice article, thanks for the information. It's very complete information. I will bookmark for next reference
jaring futsal | jaring golf | jaring pengaman proyek |
jaring pengaman bangunan | jaring pengaman gedung
http://www.jual-jaring.blogspot.com/
http://www.agen-jaring.blogspot.com/
http://www.pancasamudera-safetynet.blogspot.com/
http://www.toko-jaring.blogspot.com/
http://www.pusat-jaring.blogspot.com/
http://jualjaringpengaman.blogspot.com/
https://pancasamudera.wordpress.com/
https://pasangjaringfutsal.wordpress.com/
https://jualtambangmurah.wordpress.com/
https://tokojaring.wordpress.com/
https://jualjaringfutsal.wordpress.com/
https://jaringfutsal.wordpress.com/

Chang bui thi said...

Hello, just wanted to say, I loved this article. It was practical.
Keep on posting!
Signature:
i like play games friv online and play games2girls Download baixar facebook

thị hậu nguyễn said...

Thank you very much for your post, it makes us have more and more discs in our life, So kind for you, I also hope you will make more and more excellent post and let’s more and more talk, thank you very much, dear.
happy wheels
super mario bros
pacman
agario

حسام داود said...

levelشركة تسليك مجارى بالرياض
شركة تنظيف بالرياض
شركة تنظيف شقق بالرياض
شركة تنظيف منازل بالرياض
شركة تنظيف خزنات بالرياض
شركة مكافحة حشرات بالرياض
شركة رش مبيدات بالرياض
شركة تخزين اثاث بالرياض
شركة تنظيف مجالس بالرياض
شركة تنظيف فلل بالرياض

Jenny Daring said...


Fireboy and Watergirl arrived again to the temple in the forest. 2 players together can help them to find their way out.
happy wheels | strike force heroes | tank trouble 2 | fireboy and watergirl |fireboyandwatergirl | fireboy and watergirl 2 | goodgame empire | slitherio | Tank trouble | happy wheels | Strike Force Heroes
Log in to your account or sign up to create a new account
gmail sign in | create a Gmail account

yoob games said...

Thank you very much for your post, it makes us have more and more discs in our life, So kind for you, I also hope you will make more and more excellent post and let’s more and more talk, thank you very much, dear.
العاب
العاب طبخ
al3ab banat
تلبيس اميرة الربيع


Alice Denny said...

The blog or and best that is extremely useful to keep I can share the ideas. Age Of War 2
Big Farm | Slitherio | Tank Trouble
Of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
Happy Wheels | Goodgeme Empire | Slither.io

Jordan Ovilia said...

You go to our Web page you can play online games for free.
Our Web page selection is the biggest collection so you can play entirely for free
gun mayhem | age of war
learn to fly | happy wheels game
tank trouble

Surfers Subway said...

I found lots of interesting information here.Great work. i like this blog
Subway Surfers
Subway Surf
Subway Surfers download

hường lê said...

Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.

Discover our website bounty of free online games now!
Our website has the biggest collection of free online games. Totally new games are added every day!

age of war 2| gold Miner 2| unfair Mario 2 | cubefield 2 |tanki Online 2