?
Solved

SQL Insert Into a specified Value where condition exists

Posted on 2011-09-30
6
Medium Priority
?
407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 22

Accepted Solution

by:
8080_Diver earned 400 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 400 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 100 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

752 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