how to insert values from a stored procedure to one table

Question

0.00/5 (No votes)

See more:

i am new to sql.i wrote on stored procedure to display a particular fields from my table.these displayed fields need to be inserted to a new table.how this can be done.???
this is my stored procedure.these will display the following fields as mentioned in SP.i need to insert these fields to another new table...how this can be done?

SQL

ALTER Procedure [dbo].[SP_EMPLOYEEDETAILS] 
AS 
BEGIN
SELECT distinct 
            userid,
            fullname,
            lastname,
            firstname,
            localjobtitle,
            employmentstatusid,
            dateofjoining,
            dateofexit,
            siteid,
            email,
            topfunctionid,
            active,
            exitingthecompany,
            lmsroleid,
            locallanguagejobtitle
       FROM 
            tblUserProfile,
            tblreportingto ,
            tblCountry

[EDIT]Code tags added - LOSMAC[/EDIT]

Posted 10-May-12 1:36am

amaljosep

Updated 10-May-12 1:41am

Maciej Los

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2012-05-10T05:03:00

Solution 3

First of all, your SELECT statement is not optimal! Why? Because of many reasons...

Before you start "programming" queries, you need to get a basic knowlegde about SQL.

Here you got a basic list of msdn articles:
SQL for beginners[^]
T-SQL Challenges for beginners[^]
JOIN hints[^]
QUERY hints[^]
TABLE hints[^]
INSERT[^]
STORED PROCEDURE[^]
And something extra:
TRANSACTIONS[^]
SQL Server Transactions and Error Handling[^]

Posted 10-May-12 5:03am

Maciej Los

Comments

Wendelius 10-May-12 16:25pm

Good links. 5

Maciej Los 10-May-12 16:39pm

Thank you ;)

Sandeep Mewara 10-May-12 23:06pm

My 5!

Maciej Los 11-May-12 1:25am

Thank you ;)

Wendelius · Answer 2 · 2012-05-10T09:52:00

Solution 4

To clarify the situation with your SELECT statement. Because you haven't defined any joins in the select statement, you're actually performing a cartesian product. This means that all the rows from all the three tables will be matched with all rows.

For example, if the tables have 3 rows each, your result set is 9 rows. Most likely this isn't what you're after.

Please refer to SQL Joins[^]

Posted 10-May-12 9:52am

Wendelius

Comments

Maciej Los 10-May-12 16:01pm

I was trying to tell exactly the same thing... ;)

Wendelius 10-May-12 16:25pm

Yes, I noticed. Just wanted to clarify it a little bit. :)

Sandeep Mewara 10-May-12 23:07pm

my 5!

Wendelius 11-May-12 0:54am

Thanks :)

Raj kumar.C · Answer 3 · 2012-05-10T01:48:00

Solution 1

Hi try this

SQL

insert into newtablename(field1,field2..)
select distinct userid,
            fullname,
            lastname,
            firstname,from tblUserProfile

Posted 10-May-12 1:48am

Raj kumar.C

uspatel · Answer 4 · 2012-05-10T01:53:00

try this query as

SQL

SELECT distinct 
            userid,
            fullname,
            lastname,
            firstname,
            localjobtitle,
            employmentstatusid,
            dateofjoining,
            dateofexit,
            siteid,
            email,
            topfunctionid,
            active,
            exitingthecompany,
            lmsroleid,
            locallanguagejobtitle into TestTable 
       FROM 
            tblUserProfile,
            tblreportingto ,
            tblCountry