Insert Statement Based on Select Statement with Multiple Columns

Question

I need to create a static "copy" of values from one table and store them in another.

The best way I know how to do this is to get the values from the db to my application, loop through them and concatenate multiple INSERT statements, then execute the query.

Is there a way to do this without the round trip between application and database?

I was hoping something like this would work:

INSERT INTO dbo.StudentProject 
VALUES (SELECT StudentID, ProjectID 
        FROM dbo.StudentProjectSimulation
        WHERE SimulationID = 1)

but it doesn't.

(everything is an int so no worries there)

msdn.microsoft.com/en-us/library/dd776381.aspx

Igor Borisenko
– Igor Borisenko

2011-12-08 04:55:01 +00:00
Commented Dec 8, 2011 at 4:55 — Igor Borisenko
– Igor Borisenko, Commented Dec 8, 2011 at 4:55

Igor Borisenko · Accepted Answer · 2011-12-08 05:05:57Z

2

You're very close:

INSERT INTO dbo.StudentProject 
SELECT StudentID, ProjectID 
FROM dbo.StudentProjectSimulation
WHERE SimulationID = 1

edited Dec 8, 2011 at 5:05

Igor Borisenko

3,8663 gold badges38 silver badges53 bronze badges

answered Dec 8, 2011 at 0:22

Jake Feasel

17k5 gold badges55 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HLGEM Over a year ago

It is is poor practice to ever do an insert without defining the columns you want the insert to affect. If the table changes your code breaks. @BZN_DBer has the answer that should have been accepted.

Jake Feasel Over a year ago

I agree, but I was simply making as few changes to the OP's query as was needed to answer his question.

Raihan · Accepted Answer · 2011-12-08 00:22:58Z

2

Just drop values from the insert into clause.

Insert Into dbo.StudentProject 
Select StudentID, ProjectID  
From dbo.StudentProjectSimulation 
Where SimulationID = 1;

answered Dec 8, 2011 at 0:22

Raihan

10.5k5 gold badges31 silver badges47 bronze badges

1 Comment

MJB Over a year ago

i liked your formatting better, though not much different than the others.

Igor Borisenko · Accepted Answer · 2011-12-08 05:06:01Z

2

Alternatively to explicitly define the columns to insert into use the following where you substitute Student_Col and ProjectID_Col for appropriate column names in dbo.StudentProject

Insert Into dbo.StudentProject 
  (
    Student_Col,
    ProjectID_Col
  )
Select 
  StudentID, 
  ProjectID 
From dbo.StudentProjectSimulation 
Where SimulationID = 1

edited Dec 8, 2011 at 5:06

Igor Borisenko

3,8663 gold badges38 silver badges53 bronze badges

answered Dec 8, 2011 at 0:34

Glenn

1561 silver badge14 bronze badges

Collectives™ on Stack Overflow

Insert Statement Based on Select Statement with Multiple Columns

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related