Solved

Insert records into SQL table from Select Statement

Posted on 2010-08-25
9
460 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 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