Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

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

0
cheryl9063
Asked:
cheryl9063
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
vdr1620Commented:
you don't need to specify the braces..Its just

Insert into TableName
SELECT * From TableName ......
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now