Copying one text column to another

Hi All
I have to copy text data from a table in one database to a table in a
different database, both table reside on the same server.
I tried two options which didn't work (see code below), and will be more
than gratefull for any help.
Thanks
Elie Grouchko
***************************************
I tried the following (didn't work):
UPDATE database2.dbo.table2
SET textcolumn2 = (SELECT database1.dbo.textcolumn1 FROM table1 WHERE
tablekey1 = @.tablekey1)
WHERE tablekey2 = @.tablekey2
***************************************
I also tried (also didn't work):
Select @.textcolumn1ptr = TextPtr(textcolumn1) FROM database1.dbo.table1
WHERE tablekey1 = @.tablekey1
Select @.textcolumn2ptr = TextPtr(textcolumn2) FROM database2.dbo.table2
WHERE tablekey2 = @.tablekey2
UpdateText
database2.dbo.table2.textcolumn2 @.textcolumn2ptr
Null
0
database1.dbo.table1.textcolumn1 @.textcolumn1ptrElie,
I think this will do what you want:
update database2.dbo.table2 set
textcolumn2 = T1.textcolumn1
from database1.dbo.table1 as T1
where T1.tablekey1 = database2.dbo.table2.tablekey2
and database2.dbo.table2.tablekey2 = @.tablekey2
Steve Kass
Drew University
Elie Grouchko wrote:

>Hi All
>I have to copy text data from a table in one database to a table in a
>different database, both table reside on the same server.
>I tried two options which didn't work (see code below), and will be more
>than gratefull for any help.
>Thanks
>Elie Grouchko
>***************************************
>I tried the following (didn't work):
> UPDATE database2.dbo.table2
> SET textcolumn2 = (SELECT database1.dbo.textcolumn1 FROM table1 WHERE
>tablekey1 = @.tablekey1)
> WHERE tablekey2 = @.tablekey2
>***************************************
>I also tried (also didn't work):
> Select @.textcolumn1ptr = TextPtr(textcolumn1) FROM database1.dbo.table1
> WHERE tablekey1 = @.tablekey1
> Select @.textcolumn2ptr = TextPtr(textcolumn2) FROM database2.dbo.table2
> WHERE tablekey2 = @.tablekey2
> UpdateText
> database2.dbo.table2.textcolumn2 @.textcolumn2ptr
> Null
> 0
> database1.dbo.table1.textcolumn1 @.textcolumn1ptr
>
>|||Great, it works
Thanks
:)
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23V4SREbIFHA.2740@.TK2MSFTNGP12.phx.gbl...
> Elie,
> I think this will do what you want:
> update database2.dbo.table2 set
> textcolumn2 = T1.textcolumn1
> from database1.dbo.table1 as T1
> where T1.tablekey1 = database2.dbo.table2.tablekey2
> and database2.dbo.table2.tablekey2 = @.tablekey2
> Steve Kass
> Drew University
> Elie Grouchko wrote:
>sql

copying data and structure from one database to another

Hi all!

I have an application that needs to copy the database structure from
one database to another without using the "Generate SQL Script"
function in Enterprise Manager. I'd like to do this from within a
stored procedure. Can someone recommend the best approach for this?
I've seen references to using SQL-DMO from a stored procedure using the
sp_OA* procs in other postings to this group but was wondering if there
was an easier way? Can I use bcp and then use xp_cmdshell from within
my stored procedure? It's not clear to me from the documentation
whether bcp copies both structure and data or just data? Is there a
better way?

Thanks in advance for any help!
Karen[posted and mailed, posted and mailed]

(kjphipps_377@.hotmail.com) writes:
> I have an application that needs to copy the database structure from
> one database to another without using the "Generate SQL Script"
> function in Enterprise Manager. I'd like to do this from within a
> stored procedure. Can someone recommend the best approach for this?
> I've seen references to using SQL-DMO from a stored procedure using the
> sp_OA* procs in other postings to this group but was wondering if there
> was an easier way? Can I use bcp and then use xp_cmdshell from within
> my stored procedure? It's not clear to me from the documentation
> whether bcp copies both structure and data or just data? Is there a
> better way?

bcp copies only the data.

If you absolutely must copy table definitions and all from a stored
procedure, you are in for a painful exercise. I'd guess that DMO is
the way to go. You could read the system tables and construct SQL
from there, but that would be even more difficult. Particularly if
you need to take in regard that a stored procedure could extend over
more than 4000 characters.

But overall, I would recommend you to review the requirements. T-SQL
is simply not the right tool do this. If you absolutely must fire
a stored procedure, I would recommend writing a program in Perl,
VBscript or whatever, and call that program from xp_cmdshell. But it
goes without saying that it would be better to run this from the
application directly.

Also when running from an application, DMO may be the best pick. I
don't have any experience of DMO myself, so I don't know for sure
whether there is any built-in scripting facilities, but I would
expect there to be.

The general for creating database, is to keep code under source
control, and build the database from the version-controlled scripts.

To copy the data, bcp would still be necessary, but that's the easy
part of it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Copyind data from one database table to other

hi all
i have two databases on two different machines.
both databses r having same names.
i want to copy data from the table in other database to table in databse on my machine .
how can i do this.
i will be very thankful to receive help.Take a look at DTS in sql book online. That's the easiest to start with.