copying some db columns into another db table (within the same db)

Would like to copy some db columns into another db table.

Currently have been using stored procedures for inserting and updating db records.

How is this done please?

Thank you in advance for your time and efforts with this enquiry.
amillyardAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
ok, In the above example , instead of insertin, say we want to perform an UPDATE

UPDATE T
SET Age = s.age
FROM Target t
INNER JOIN Source s  ON T.ID  = s.ID  ---
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
are those on different  dbs , if so use this


insert into urTargetDB..TargetTable (Columns to be inserted)
SELECT (ColumnsTobe inserted)
FROM urSourceTable


else if both are on same db

insert into TargetTable (Columns to be inserted)
SELECT (ColumnsTobe inserted)
FROM urSourceTable

both has to be run on the source db


0
 
amillyardAuthor Commented:
aneeshattingal:

(using the same db)

I am assuming that I just add a WHERE statement if wanting a specific table to be copied.

What about target db table -- i.e. where  "tableID=123"

from specific table 'a' to specific table 'b'
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Aneesh RetnakaranDatabase AdministratorCommented:
>I am assuming that I just add a WHERE statement if wanting a specific table to be copied.

WHERE clause actually filters the data to be inserted, say for example the sourcetable with name 'Source' has the structure ( id, name, age )  and you just need to copy the columns ID and Age onto the 'destinationTable. then your query will be something like this

insert into Destination (id,Age)
select id, age
from Source

Now if you want to copy those records having age > 10 , your query will looks like

insert into Destination (id,Age)
select id, age
from Source
where Age > 10
0
 
amillyardAuthor Commented:
ok, cool -- understand better now.

and, copying into a speciific destination table?  (i.e. tableID='123')

as above appears to be the next table onwards -- ie. if copying 5 table records, then these are added onto the end as it were -- what about if the copy is updating a destination table content?  (or partially updating some of the content columns)
0
 
amillyardAuthor Commented:
aneeshattingal:  many thanks :-)
0
 
amillyardAuthor Commented:
aneeshattingal: can I use the above format in a stored procedure?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Sure

CREATE PROC updateAge
as
SET NOCOUNT ON
UPDATE T
SET Age = s.age
FROM Target t
INNER JOIN Source s  ON T.ID  = s.ID
GO
0
All Courses

From novice to tech pro — start learning today.