?
Solved

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

Posted on 2011-02-28
9
Medium Priority
?
802 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 400 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 1600 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

762 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