Solved

Insert records into SQL table from Select Statement

Posted on 2010-08-25
9
463 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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 500 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

724 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