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
Subscribe to:
Post Comments (Atom)
17 comments:
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
Vedro
Can you post the script you working for? Did my suggestion help you?
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
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
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
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;
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
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?
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
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
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
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
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
Great news,I'm glad you solved the problem and thank you for warm words
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
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
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
تلبيس اميرة الربيع
Post a Comment