Solved

SQL Insert Into a specified Value where condition exists

Posted on 2011-09-30
6
397 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

In this article I will describe the Detach & Attach 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now