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?
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
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
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
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?
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.
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.
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?
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.
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
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?
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...
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
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.
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())
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.
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
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
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)
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
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
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.
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?
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
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
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
