Solved

SQL Insert Into a specified Value where condition exists

Posted on 2011-09-30
6
401 Views
Last Modified: 2012-05-12
I am trying to insert records for where a condition exists and doesn't copy from the previous table.  Basically I have a custom student table, and I want to add a number to the values field on a specified attribute where they have enrollment in a specified calendar.
From the books and knowledge bases I read, it has to copy a value like 21 from one table to another.

INSERT INTO [sandbox].[dbo].[CustomStudent]
           ([value])
values (21)
where CustomStudent.attributeID = '278' and enrollment.calendarid ='62'
INSERT INTO [hopewell_sandbox].[dbo].[CustomStudent]
           ([value])
values (21)
where CustomStudent.attributeID = '278' and enrollment.calendarid ='62'

Open in new window

0
Comment
Question by:Emi975
6 Comments
 
LVL 22

Accepted Solution

by:
8080_Diver earned 100 total points
ID: 36893087
What other columns exist in the CustomStudent table?  Where do you expect the values for those columns to come from?  If you don't select them from somewhere or specify them, then you are, in effect, inserting NULL into those columns.
0
 

Author Comment

by:Emi975
ID: 36893313
customid,personid,enrollmentid,attributeid,value,date are the fields in this table.
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 100 total points
ID: 36893418
Depending on the default settings, e.g. date defaulting to GETDATE(), you may be able to leave some of those out.  (By the way, date is a really bad choice for a column name because it is a Reserved Word.  You should change that to, for instance, DateLastUpdated.)

However, if you don't supply anything in your SELECT statement, then you wil, in effect, be inserting NULL into those columns . . . which may or may not work.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 25 total points
ID: 36893503
is this coursework/homework?

first some definitions

Insert -  add new rows to a table
Update - amend data stored in columns on existing table rows.

two basic forms for the insert statement

insert into tablename
 (list of column names for which data will be supplied)
 Values (data items for a single row matching the order of the column list)

or

Insert into tablename
(list of column names for which data will be supplied)
Select a mixture of columns from tables used in the select statement and data values
FROM Tablename
Where ....


please be more specific about what you are attempting to do

0
 
LVL 8

Expert Comment

by:Leo Torres
ID: 36893540

Dont have enough info about tables but there has to be some thing that joins the 2 tables

the c. and e. would point to columns that exists in there respective tables they are aliases
Should look like this
INSERT INTO [hopewell_sandbox].[dbo].[CustomStudent]
Select c.'Your value', e.'Your Value',e.'Your Value'
from CustomStudent c
	inner Join enrollment e 
	on e.id = c.id
where CustomStudent.attributeID = '278' and enrollment.calendarid ='62'

Open in new window

0
 
LVL 8

Expert Comment

by:Leo Torres
ID: 36893555
For got to mention you must specify what columns your inserting into if not you must enter a value in every column in the same order they appear in the table
INSERT INTO [hopewell_sandbox].[dbo].[CustomStudent](column1,column2,colmn3)
Select c.'Your value', e.'Your Value',e.'Your Value'
from CustomStudent c
	inner Join enrollment e 
	on e.id = c.id
where CustomStudent.attributeID = '278' and enrollment.calendarid ='62'

Open in new window

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

785 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