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

amillyard
amillyard used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
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


Author

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'
AneeshDatabase Consultant
Top Expert 2009

Commented:
>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
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Author

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)
Database Consultant
Top Expert 2009
Commented:
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  ---

Author

Commented:
aneeshattingal:  many thanks :-)

Author

Commented:
aneeshattingal: can I use the above format in a stored procedure?
AneeshDatabase Consultant
Top Expert 2009

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial