| Author |
New to SQL, - help - Copying a column from one table to another
|
|
| Dino Buljubasic 2005-07-29, 11:23 am |
| Hi,
I am new to sql and have a problem that I need quick help.
I need to copy data from a column in one table (from backup) to the
same column in another table. Basically like this:
TABLE BACKUP TABLE TO COPY TO
colID title date colID title date
I need to copy date column data from TABLE BACKUP to date column in
TABLE TO COPY TO using a query. I tried something like
INSERT INTO TABLE_TO_COPY_TO (date) SELECT date FROM TABLE_BACKUP
But this would not work because title field does not allow nulls.
Thank you
_dino_
| |
| David Portas 2005-07-29, 11:23 am |
| Here's a guess at what you want, assuming that Title is unique in both
tables:
UPDATE Target
SET date =
(SELECT date
FROM Source
WHERE source.title = target.title)
WHERE EXISTS
(SELECT *
FROM Source
WHERE source.title = target.title) ;
Make sure you take a backup or try it out on a test copy of your data first.
If you need more help then please refer to the following article which
explains the best way to post your problem here.
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--
| |
| Dino Buljubasic 2005-07-29, 11:23 am |
| Hi David,
Thank you very much. This solved my problem and I learned a lot.
I had one more question. How would I have to change this code in
order to do the same but from tables in different databases on the
same server and also on different servers.
Would it be enougt just to prefix the table names with database names
(and server names)?
Thank you,
_dino_
On Fri, 29 Jul 2005 17:00:55 +0100, "David Portas"
< REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote:
>Here's a guess at what you want, assuming that Title is unique in both
>tables:
>
>UPDATE Target
> SET date =
> (SELECT date
> FROM Source
> WHERE source.title = target.title)
> WHERE EXISTS
> (SELECT *
> FROM Source
> WHERE source.title = target.title) ;
>
>Make sure you take a backup or try it out on a test copy of your data first.
>
>If you need more help then please refer to the following article which
>explains the best way to post your problem here.
>http://www.aspfaq.com/etiquette.asp?id=5006
>
>--
>David Portas
>SQL Server MVP
| |
| David Portas 2005-07-29, 1:23 pm |
| See the following topic in Books Online:
http://msdn.microsoft.com/library/d....asp?frame=true
You'll want something like:
UPDATE Server1.dbo.Target
SET date =
(SELECT date
FROM Server2.dbo.Source
WHERE source.title = target.title)
WHERE EXISTS
(SELECT *
FROM Server2.dbo.Source
WHERE source.title = target.title) ;
assuming Server1 and Server2 are linked servers.
--
David Portas
SQL Server MVP
--
| |
| Dino Buljubasic 2005-07-29, 1:23 pm |
| Thank you. I really appreciate your help.
Dino
On Fri, 29 Jul 2005 18:05:47 +0100, "David Portas"
< REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote:
>See the following topic in Books Online:
>http://msdn.microsoft.com/library/d....asp?frame=true
>
>You'll want something like:
>
>UPDATE Server1.dbo.Target
> SET date =
> (SELECT date
> FROM Server2.dbo.Source
> WHERE source.title = target.title)
> WHERE EXISTS
> (SELECT *
> FROM Server2.dbo.Source
> WHERE source.title = target.title) ;
>
>assuming Server1 and Server2 are linked servers.
>
>--
>David Portas
>SQL Server MVP
|
|
|
|