• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 807
  • Last Modified:

T-SQL: Moving data from one IMAGE column to another column in another table.

Hey.
In a project I'm involved in i need to migrate data between two identical databases used by a data system.

I'm however having difficulties moving data between two IMAGE columns, located in two identical tables, in two different databases on a SQL 2005 server.

The IMAGE column (named [data]) can contain data for jpg, pdf, doc among basically any other filetype.

After reading quite a bit on the IMAGE column type I'm realising this is not easiest datatype to deal with but unfortunatly i cant change that.

So my thought for moving this column was to create an SSIS package which called a stored procedure for every row in the table that i want to move. I have included this stored procedure below. The procedure was just coded to run on my test copy of one of the databases so its really reading and writing from the same table.

Now, onto the problem..
This method works great for images such as jpeg's, but it does not work at all if the row contains for example a .txt. The data that being moved for texts does not even seem to be from the right place. When I moved a txt using this method i could open the file but the moved file contained xml and unreadable characters instead of the original text.

Anybody have any ideas as how to do this?



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE dbo.csp_moveDocumentFromDatabasetoThis
( 
		 @@idoldfile as int
		,@@iduser as int = 1
		,@@fileextension as nvarchar(32)
		,@@filetype as int
		,@@compressed as int = 0
		,@idnewFile INT OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO lime_sammanslagning_destination.dbo.[file]				/* EDIT HERE, Destination  */
			([filetype], [status], iduser, fileextension, [compressed])
		VALUES 
			(@@filetype, 0, @@iduser, @@fileextension, @@compressed)

	Select @idnewFile = SCOPE_IDENTITY()
		
	UPDATE lime_sammanslagning_destination.dbo.[file]					/* EDIT HERE, Destination  */
		SET [data] = (	Select [data] 
						From lime_sammanslagning_destination.dbo.[file] /* EDIT HERE, Source */
						Where idfile = @@idoldfile )
	Where idfile = @idnewFile

END
GO

Open in new window

0
Lime_Sweden
Asked:
Lime_Sweden
  • 5
  • 3
2 Solutions
 
Lime_SwedenAuthor Commented:
I've also tried converting the datacolumn to a varbinary before moving it as some forums claimed that this would solve the problem.. but it dident.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what errors do you run into, using varbinary ?
note: your posts has @@ for some of the variables, is that a mistype? it should be @ only ...
0
 
LowfatspreadCommented:
have you considered just using replication to move the data across?

what compatability levels are the databases operating at? are they the same...
same instance / different instances?

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Lime_SwedenAuthor Commented:
Been playing around with this some more now and ive gotten it to work, not quite sure what the problem was but i just went through all the SSIS package steps and checked everything.. Dident change anything but now it works...

@angellll Ive been told to use @@ for all input paramaters as the way it *should* be, and single @ for all internal variables, not sure why besides "its standard".

@Lowfatspread would replication work if i have to keep all the original data in the destination table?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, here is the tech doc for identifiers:
http://msdn.microsoft.com/en-us/library/ms175874%28v=SQL.90%29.aspx

Rules for Regular Identifiers
The rules for the format of regular identifiers depend on the database compatibility level. This level can be set by using sp_dbcmptlevel. When the compatibility level is 90, the following rules apply:
1. The first character must be one of the following:
          * A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.
          * The underscore (_), at sign (@), or number sign (#).
            Certain symbols at the beginning of an identifier have special meaning in SQL Server. A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object. An identifier that starts with a number sign denotes a temporary table or procedure. An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.
            Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@.


so, while one can use @@ for variables, the second @ actually is part of the name, and has no special meaning at all.
looks like a naming convention I would really NOT suggest. but it does not harm.

so, back to the problem: what is the error you get?
with IMAGE, indeed, you cannot just "copy" with INSERT/SELECT, with VARBINARY though, it should work
0
 
Lime_SwedenAuthor Commented:
The weird part is that i never got any errors

And that now after going through everything it is actually working, Im pretty sure I havent changed anything from when i asked the question and was getting a weird copy of the file so im quite stumped as to why it actually works now.

This is working:
SET [data] = (Select [data] From lime_sammanslagning_destination.dbo.[file]  Where idfile =@@idoldfile)


0
 
Lime_SwedenAuthor Commented:
My worst worry now is that its copying the pointer and that the file is not being copied and im going to be in deep **** once the source table is dropped.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>My worst worry now is that its copying the pointer
with varbinary: no worries.
with image: I never tried such things :)

but  you can test, can't you, with 2 test tables?!
0
 
Lime_SwedenAuthor Commented:
Yeah just tested it, created two test databases and copied a row, then deleted source row and can still retrieve the file.
So no such problems :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now