?
Solved

Insert records into SQL table from Select Statement

Posted on 2010-08-25
9
Medium Priority
?
466 Views
Last Modified: 2012-05-10
Please see my code below.. I want to insert all the records from my subquery into the table.. How do I do this? There are a lot of fields.. Like 20 or something so I'm trying to avoid having to list all of the fields out..
Insert INTO CTS.CORE.tblStatusTracking
 ( Select * from CTS.CORE.tblStatusTracking st
	join ##tblCon2 cc on st.nStatusTrackingID = cc.nStatusTrackingID)

Open in new window

0
Comment
Question by:cheryl9063
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 4

Expert Comment

by:jindalankush
ID: 33522374
Insert INTO CTS.CORE.tblStatusTracking
 ( Select st.* from CTS.CORE.tblStatusTracking st
      join ##tblCon2 cc on st.nStatusTrackingID = cc.nStatusTrackingID)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33522412
For INSERT into statements, you need to specify Columns list in INSERT INTO as well as SELECT clause like this to get it work:

Insert INTO CTS.CORE.tblStatusTracking (col1, col2)
Select col1, col2
from CTS.CORE.tblStatusTracking st
join ##tblCon2 cc on st.nStatusTrackingID = cc.nStatusTrackingID
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33522418
If you same number of columns in the result set and in the destination Table in the same order, then you will not need to mention any columns specifically.... But if that's not the case which i definetly see is not the case here because the select query produces all the columns from CTS.CORE.tblStatusTracking and temp table..  

I also see that you are trying to insert values into the Same table.. simply duplicating the values..May i know why?

if you just want to insert all columns from one Table ..For Example you want to insert all values from CTS.CORE.tblStatusTracking St only, then Specify St.* in your select statement .

Insert INTO CTS.CORE.tblStatusTracking
 Select St.* from CTS.CORE.tblStatusTracking st
      join ##tblCon2 cc on st.nStatusTrackingID = cc.nStatusTrackingID
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:cheryl9063
ID: 33522434
I'm getting this error message:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Select'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33522490
Use the syntax which I have provided as your contains the above mistake..
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 2000 total points
ID: 33522571
you don't need to specify the braces..Its just

Insert into TableName
SELECT * From TableName ......
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 33523122
Thanks! The reson I have to re insert the record is becuase of multiple triggers and procs with tons of code talking back and forth to Oracle and other systems.. It's complicated and I did not design it:) Thanks for your help!!
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33523140
No problem.. i was just curious :)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33528674
cheryl9063,

Is it the same I mentioned very earlier in my comments http:#a33522412 and iterated you to follow that in the next comment http:#a33522490.
Would have been fair if you have split the points across..
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question