Multiple Row Update/Insert
Jun 29, 2005
Hi,I have a a table that is primarily a linking table that contains values for categories indid int Indicator ID indtype int Indicator Type can be either 0 or 1catflagnum int Category Flag Number the number of the Category catflagvalue int Cat Flag Value The Value for that category this table can then be updated from a web form.The Question I have is that can I do this in one statement or do I have to do it one at a time i.e The Data set could look something like indid = 3 'This value will be the same for all rows indtype = 0 'This will be the Same for all rows Catflagnum = 1 'This value will change Catflagvalue = 1 'This value will change indid = 3 'This value will be the same for all rows indtype = 0 'This will be the Same for all rows Catflagnum = 2 'This value will change Catflagvalue = 3 'This value will change indid = 3 'This value will be the same for all rows indtype = 0 'This will be the Same for all rows Catflagnum = 3 'This value will change Catflagvalue = 1 'This value will change indid = 3 'This value will be the same for all rows indtype = 0 'This will be the Same for all rows Catflagnum = 4 'This value will change Catflagvalue = 5 'This value will change A further complication is that in the table an entry may not be in that table for the category so we would possibly have to check if the record exist or do an insertI am stumped on this one?
View 5 Replies
ADVERTISEMENT
Feb 25, 2015
I need to update multiple columns in a table with multiple condition.
For example, this is my Query
update Table1
set weight= d.weight,
stateweight=d.stateweight,
overallweight=d.overallweight
from
(select * from table2)d
where table1.state=d.state and
table1.month=d.month and
table1.year=d.year
If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column
I can't write an update query for each condition separately because its a huge select
View 7 Replies
View Related
Aug 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
Mar 18, 2005
hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
View 2 Replies
View Related
Apr 16, 2015
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
View 9 Replies
View Related
Mar 1, 2007
Hello
I am building a survey application.
I have 8 questions.
Textbox - Call reference
Dropdownmenu - choose Support method
Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
View 9 Replies
View Related
Sep 3, 2014
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies
View Related
Feb 15, 2008
I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?
Here is XML file:
Code Snippet
<ReferenceFiles>
<File>
<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>
<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>
Thanks.
View 1 Replies
View Related
Nov 14, 2007
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View 6 Replies
View Related
Apr 11, 2008
Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
View 3 Replies
View Related
Mar 1, 2004
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
View 10 Replies
View Related
Oct 23, 2014
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
View 1 Replies
View Related
Oct 19, 2005
UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?
View 1 Replies
View Related
Feb 15, 2008
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before
View 14 Replies
View Related
Jul 23, 2005
Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.
View 4 Replies
View Related
Mar 1, 2007
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
View 2 Replies
View Related
Jul 20, 2005
I'm having an Employee table with a Salary field. How can we increate thesalary of the employees with following conditions:1) salary between 1000 and 10000 : increase 25%2) salary between 10000 and 20000 : increase 15%3) salary between 20000 and 30000 : increase 5%Surely you can create a cursor to solve this. But the question is, Is itpossible to solve this in a single query, if no what is most optimizedway?
View 4 Replies
View Related
Oct 30, 2007
Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen
View 5 Replies
View Related
Jun 9, 2008
Hi everyone, ok so heres my problem. I'm currently writting some scrips to query an simple inventory database. heres what i'm trying to do.
I have a .asp webpage that has a query on it that can dispaly everything in the inventory... old and new in a big html table.
The idea is to not actualy delete anything because i would still like to have a record of it's existance but at the same time i dont want it to clutter my "current" inventory i guess it would call it.
I added another column named deleted which is set to the Yes/No and TRUE/FALSE data types, i also put in a check box at the end of every row on the webpage, the idea here is that if a box is checked it will record that row's id via address bar/Get method and then when i hit submit it will run the update query for those rows checked
i changed my inventory query so that if an item's deleted column is checked to TRUE then it will not be displayed, so showing only the non deleted items.
UPDATE Licenses SET Licenses.deleted = TRUE
WHERE Licenses.LicensesID=33;
thats what i'm using to change it case by case basis, but what i would like to do is somthing like
UPDATE Licenses SET Licenses.deleted = TRUE
WHERE Licenses.LicensesID=33,34,67....so on
LicensesID is just a auto incrememted field jsut to give a unique value to a license's row..
does anyone know any tricks to accomplish what i'm trying to do? So far I havn't really found any satisfactory examples online. and i dont really want to completly change the structure of everything unless someone suggust somthing that is clearly a better way of doing things
thanks,
O
View 2 Replies
View Related
Jan 30, 2007
Hi.
I am trying to update multiple row with different value. Is this possible w/ query or should I create a procecure?
Suppose I have two table A and B, I need to update values of A_b = B_b, A_c = B_c where A_a = B_a
below query somewhat shows what I am trying to do...
UPDATE A SET A_b = B_b, A_c = B_c WHERE A_a IN (SELECT B_a FROM B)
Thank you.
View 3 Replies
View Related
Jan 11, 2008
Hi...I have two database table. we shall call them table 1 and table 2I want to insert data into table 1 and table 2 on the same page, so that when i click a submit button data goes into both tables. So what i will like to see happening is table 1 gets updated first then using that value it just inserted it gets used in table 2.Table 1 has an ID field that Table 2 needs inorder to insert its data. The ID in table 1 is a primary key and its a foreign key in table 2.Does anybody know how i can go about doing this in a nice and simple way. I am very new to sql and asp.net, hence a simple solution will be very helpful. I hope my problem make sense.. Thanx in advance.
View 4 Replies
View Related
May 30, 2008
In my project I need to reserve rows of data in a table. I need to do this to make sure that unique automtic number, Identifier of that table will correspond with a sample that needs to be proces. Later that Id will be used for further upadate etc.
I know I can insert new row by using code link to the button_click class
protected void ReserveSample_Click(object sender, EventArgs e)
{ SampleReserved.Insert();
} but what if I would like to have a user to choose from a dropdown on the page amount of rows that needed to be inserted at once. Is there a way of tieing dropdown value, let say ‘20’ to sql insert statement to run the insert 20 times of the same values Here is the insert statement
INSERT INTO flower (reserved, reserved_date)VALUES (@reserved, GETDATE())
View 6 Replies
View Related
Apr 15, 2004
Here is my problem:
I would like to execute 10 inserts and every insert in this batch should have the same ID. The next batch would have the next sequential number (ID) and so on...
I can do this by using a secondary table where I keep track of my IDs.
Is there another way of doing it?
Thanks.
dg
View 1 Replies
View Related
Jun 12, 2005
hi friends,
i am having a problem here. I am using msde and i have a database with 3 tables.
Here is what i want to do.
1- I want to insert posted data to table 1 ( i can do this step)
2- I want to insert posted data to table 2 ( i can do this step, too)
3- I want to insert the primary keys of the rows inserted to table 1 and table 2 , to table 3 . I dont know anything about stored procedures . Can i do this in my code-behind using C# ? If you can show me the way i will be glad.Thanks in advance
View 3 Replies
View Related
Oct 4, 2006
hi, i have 4 textboxes in my form. i have one column in my table such as names.
i am filling names in those taxtboxes,so i want to use only one query to insert 4 textbox values in my column(names).so please tell me query to do this. is tere any insert all query in sqlserver?please give me query for my need
View 2 Replies
View Related
Nov 29, 2007
Hi
I am trying to do a similar thing to what I did before but now using the following command.
Basically I want to INSERT the clauses shown in the INSERT statement into the VALUES as shown but where the ADDRESS_NUMBER in the COMMUNICATIONS table is a set of values. Do I need to somehow use UNION or UNION ALL for this?
I am getting the following error when I run the statement as shown below:-
Cannot insert duplicate key row in object 'communications' with unique index 'c2962cn7081'.
The statement has been terminated.
declare @ComNum int
set @ComNum = (select max(communication_number)+1 from communications)
insert into [communications]
(address_number, contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number)
select address_number, NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum
from communications where address_number in (126, 127, 128)
Thanks for all your help.
Jon
View 4 Replies
View Related
Jul 23, 2005
Hi, I'm trying to insert multiple rows with the following statment andam getting a syntax error on line 3. The datatypes are varchars exceptfor status which is numeric.insert S (S#, SNAME, STATUS, CITY)values('S2', 'Jones', 10, 'Paris'),('S3', 'Blake', 30, 'Paris'),('S4', 'Clark', 20, 'London'),('S5', 'Adams', 30, 'Athens');What's wrong?Thanks,Sashi
View 2 Replies
View Related
Mar 28, 2008
So I've been working on this project and ran into another problem with adding multiple rows of data. I have a couple of tables particular one that associates a Minor and its classes together. What I want to be able to do is when a student adds a Minor it also adds any Minor classes that aren't already in the Student_Classes table. So let's see if I can lay it out correctly and the code I have so far...
Code Snippet
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Minors] Script Date: 03/27/2008 21:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Minors](
[MinorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Minors] PRIMARY KEY CLUSTERED
(
[MinorID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MinorRequiredClasses] Script Date: 03/27/2008 21:39:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MinorRequiredClasses](
[MinorClassID] [int] IDENTITY(0,1) NOT NULL,
[MinorID] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClassID] [nchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MinorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MinorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MinorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MinorRequiredClasses_MinorName] FOREIGN KEY([MinorID])
REFERENCES [dbo].[Minors] ([MinorID])
GO
ALTER TABLE [dbo].[MinorRequiredClasses] CHECK CONSTRAINT [FK_MinorRequiredClasses_MinorName]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Student_Classes] Script Date: 03/27/2008 21:40:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student_Classes](
[StudClassID] [int] IDENTITY(0,1) NOT NULL,
[StudentID] [int] NULL,
[ClassID] [nchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditID] [int] NULL,
[Days] [nchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Time] [nchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Classroom] [nchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Grade] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Semester] [nchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes] [nchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Completed] [tinyint] NULL,
CONSTRAINT [PK_Student_Classes] PRIMARY KEY CLUSTERED
(
[StudClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_ClassID]
GO
ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_CreditID] FOREIGN KEY([CreditID])
REFERENCES [dbo].[Credits] ([CreditID])
GO
ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_CreditID]
GO
ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_StudentsID] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
GO
ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_StudentsID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Classes] Script Date: 03/27/2008 21:40:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Classes](
[ClassID] [nchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LongName] [nchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Classes] PRIMARY KEY CLUSTERED
(
[ClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT INTO Student_Minors (MinorID, StudentID) VALUES(@minorid, @studid)
INSERT INTO Student_Classes (StudentID, ClassID, CreditID)
SELECT sClass.StudentID
,c.ClassID
,cred.CreditID
FROM Student_Classes sClass
INNER JOIN Classes c
ON MinorRequiredClasses.ClassID = c.ClassID AND MinorRequiredClasses.MinorID = @minorID
INNER JOIN Credits cred
ON cred.ClassID = c.ClassID
WHERE sClass.StudentID = @studid
AND sClass.ClassID NOT IN (SELECT mrc.ClassID
FROM MinorRequiredClasses mrc WHERE mrc.MinorID = @minorID)
An example of each table is below:
Minors
MinorID Description Criteria
Computer Science blah blah blah... blah blah blah...
MinorRequiredClasses
MinorClassID MinorID ClassID
0 Computer Science CSC180
1 Computer Science CSC200
Student_Classes
StudClassID StudentID ClassID CreditID bunch of others that aren't important...
0 0 CSC180 313 ...
Classes
ClassID LongName Description
CSC180 Intro to Computer Prog... blah blah...
CSC200 Intermediate Progr... blah blah...
To check if a minor already exists I can figure out myself but it is really adding multiple classes based on if the class is part of the minor a student is adding to his/her profile.
Hope someone can help
View 3 Replies
View Related
Apr 7, 2008
hi all~i very confuse~i using mssqlserver 2005 in visual basic 2005express..but i feel like want to update two table at onces..i faced some problem~i give an example..my tableA got user id, user name and table B got user id and status.In both table, i put user id as primary key and it will auto link each other and i retrieve the user id and user name from table A and user status from table B~..in order to come out the data in gridview with three columns which are user id, user name and user status..in this case, it works perfectly..but when come to update part, i only able to update one table at once, when i trying to update both table, it occurs errors...i did read some forum, some said it is impossible to do it?isn't?then is dere got any other ways to make me update both table at once?my purpose of this is wish to update user id and user name from table A and user status on table B....~i guess maybe my database design concept got problem..hope someone giv help on these... by the way izit foreign key can solve this?and how to put the foregin key?
View 4 Replies
View Related
May 13, 2004
Any idea how to fix my code. I am getting this error message below....
Server: Msg 512, Level 16, State 1, Procedure TrigRetReqRecIDP1, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Set @intRowCount = (select count(*) from RequestRecords where REID = @REID)
While (select @REID from RequestRecords) != @intRowCount
Begin
select @intRRID = (select REID from RequestRecords where REID=@REID and RRStatus = 'PE')
Exec TrigAssignImpTaskNewP1 @intRRID, @REID
End
View 2 Replies
View Related
Nov 14, 2000
If I want to update 2 columns at 1 time with a where clause
like
update table a
set column1 = 8 and
set column2 = 9
where id = 10
I know you can't use the and statement, what is the correct syntax?
View 1 Replies
View Related
Oct 30, 2003
I have a messy design that I can't change but need to keep in synch. I have a master table where a person's info is entered into. Upon that entry I have to take that info (name, bdate,ssn,location) and populate it to 4 separate tables in 4 separate databases, all of which have their own id field which has to be incremented (no none of them are identity columns). At the end of the process I have to update the master record with the id fields of all the other four tables. I was going to do this with an insert trigger. As far as protecting the id's across all four databases, I was going to use a begin trans and get the next id field for all four databases and then apply the logic to add the records. Does this seem a safe approach, or am I missing something? Do I need to do a begin tran for each database separately?
THanks
View 1 Replies
View Related
Oct 17, 2001
What would be the best way to handle different updates for a table, multiple triggers, or just one large triggger? I am not worried about their order of firing, just that they fire
View 1 Replies
View Related
