stored prodedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored prodedure

Author

Topic

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2007-01-08 : 05:20:55

hi,

i want to create one stored procedure.in that i want to retreive datas from one table and i want to insert those retreived records into another table,please can anybody show some sample procedure

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-01-08 : 05:42:06

insert into tableb
select cola1, cola2, cola3, .... from tabla

however, going by the number of basic very questions you are generating at the moment, I would advise that you invest some time reading either your course notes (as these questions look like homework) or Books-Online or invest in some "training". Your progress will be hindered by getting piecemeal reponses and may be best served by brushing up on the basic skills via a better educator that the internet.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-08 : 05:42:23

If you have destination table created,

 Insert into DestTbl(col1, col2, ...)
 Select col1, col2, ... from SourceTbl

Otherwise,

 Select col1, col2, ... 
 into DestTbl
 from SourceTbl
 Where 1=0

 Insert into DestTbl(col1, col2, ...)
 Select col1, col2, ... from SourceTbl

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2007-01-08 : 06:07:20

hi harsh_athalye,AndrewMurphy sorry for the inconvenience,what i asked is for example i am getting 10 rows of records using some subqueries and i want to insert those recors in some other table

for example

if i use select @name =name from emp where id =10
insert into emp2 values(@name)
in this i can easily insert the value.but if i use like this
if i use select @name =name from emp where id in(select id from emp10)
so this query fetches multiple rows so how to insert all the rows in another table.this is my requirement,can u please give me idea with example please

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-01-08 : 13:00:51

quote:
Originally posted by born2acheive

hi harsh_athalye,AndrewMurphy sorry for the inconvenience,what i asked is for example i am getting 10 rows of records using some subqueries and i want to insert those recors in some other table

for example

if i use select @name =name from emp where id =10
insert into emp2 values(@name)
in this i can easily insert the value.but if i use like this
if i use select @name =name from emp where id in(select id from emp10)
so this query fetches multiple rows so how to insert all the rows in another table.this is my requirement,can u please give me idea with example please

Harsh's method doesn't have this issue. Did you try the code?

-ec

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 13:08:08

Beacuse this query selects ALL id's from emp10 table!!!

select @name = name from emp where id in (select id from emp10)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources