Solved

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

Posted on 2011-02-28
9
801 Views
Last Modified: 2012-05-11
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
Comment
Question by:Lime_Sweden
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 

Author Comment

by:Lime_Sweden
ID: 35004708
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35005401
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 100 total points
ID: 35005513
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Lime_Sweden
ID: 35005705
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 35005747
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
 

Author Comment

by:Lime_Sweden
ID: 35005799
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
 

Author Comment

by:Lime_Sweden
ID: 35005810
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35005873
>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
 

Author Comment

by:Lime_Sweden
ID: 35006338
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question