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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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
Aneesh RetnakaranDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.