Insert records into SQL table from Select Statement

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

LVL 1
cheryl9063Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
vdr1620Connect With a Mentor Commented:
you don't need to specify the braces..Its just

Insert into TableName
SELECT * From TableName ......
0
 
jindalankushCommented:
Insert INTO CTS.CORE.tblStatusTracking
 ( Select st.* from CTS.CORE.tblStatusTracking st
      join ##tblCon2 cc on st.nStatusTrackingID = cc.nStatusTrackingID)
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
vdr1620Commented:
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
 
cheryl9063Author Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Use the syntax which I have provided as your contains the above mistake..
0
 
cheryl9063Author Commented:
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
 
vdr1620Commented:
No problem.. i was just curious :)
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
All Courses

From novice to tech pro — start learning today.