I would like to update/insert data into a Orderhearder Table along with the related details into the corrosponding OrderDetails Tables. Can this be done using a single stored procedure or do we have to make one call to the UpdateOrderHeader Stored Procedure and loop thru all the details and call the UpdateOrderDetails Stored Procedure. How do we handle the Transactions in such a case ?
Thanks
Anurag Agarwal
w/ SqlServer, is there anyway to pack a number of calls to the same stored procedure into a single round-trip to the DB short of dynamically writing a T-SQL block? For example, if I'm calling a procedure "Update Contact" which takes 2 params @Campaign, @Contact 20 times how would I pass in the values for those 20 diffrent versions?
View 6 Replies View Relatedi am using ms sql server 2005 express for the following:
T1 = {t1.c1(PK), t1.c2, t2.c1(FK)}
T2 = {t2.c1(PK), t2.c2}
where T1 and T2 are tables from the same database, and tn.cn are CoulmnN of TableN
T1 and T2 are linked by the relationship t2.c1
i need to know how to add records to T1 and T2 using ms sql.
record for T2 needs to be added first then retreive t2.c1 from T2 to add record to T1 with t1.c1, t1.c2 t2.c1
please help, thanks
Hi,
I have 3 tables:
Employees with the fields:idEmployee and employeeName
Roles with the fields:idRole and roleName.
An employee can have one or many roles.
I created the table EmployeeRoles with the fields: id,idEmployee,idRole.
idEmployee and idRole are foreign keys.
I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.
How can I do this?
Thanks
I have two sets of related tables: Quote - QuoteDetail and Order - OrderItem
I need to copy Quote - QuoteDetail records to Order - OrderItem tables
I have the stored procedure up to this point: Insert a Quote in the Order table and get the new Order @@Identity.
I need to insert the QuoteDetail records into the OrderItem table using the new OrderID
Thank you for your help.
I am really stuck here,
I hope to get some helpful answers on this forum.
Ok, I have three four tables in my db,
1- Stages
2- Activities
3- Tasks
4- Subtasks
Structure is like:
Stages
1- SrNo (Unique)
2- Stage
3- StartDate
4- FinishDate
Activities
1- SrNo (Unique)
2- Activity
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Stages Table)
Tasks
1- SrNo (Unique)
2- Task
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Activities Table)
Subtasks
1- SrNo (Unique)
2- Subtasks
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Task Table)
Now what i want is to update Tasks, StartDate and FinishDate according the Min(StartDate) and Max(FinishDate) of related Subtasks and same for Activities and Stages.
I have tried following query to Update tasks, StartDate and FinishDate
Update Tasks Set Tasks.StartDate=(Select Min(Subtasks.StartDate) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1
But this query updates all Tasks with Min and Max date from Subtasks regardless of their relation.
Any help, would be appreciated.
Thanks
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 RelatedI Want to write a single SP which inserts and updates.
CREATE PROCEDURE sp_NewPassword
@UserName nvarchar(50),
@OldPassword nvarchar(50),
@NewPassword nvarchar(50)
AS
BEGIN
INSERT INTO RGH_ChangePassword(vUserName, vNewPassword) VALUES(@UserName, @NewPassword)
UPDATE RGH_LoginMaster SET vPassword=@NewPassword WHERE vUserName=@vUserName and vPassword=@OldPassword
END
When i tried to create above sp it gives me an error saying
Msg 137, Level 15, State 2, Procedure sp_NewPassword, Line 8
Must declare the scalar variable "@vUserName".
In VB6 using MDAC 2.8 I could do a single select statement that would act as either an Insert or an update. Is there a way to do this in ADO.net?
My old VB6 code
Dim dbData As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim strParm As String
Dim strCusNo As String
'
strParm = "Provider=SQLOLEDB; Data Source=SQL2000; Initial Catalog=DATA_01; User ID=UserName; Password=password"
dbData.Open strParm
'
strParm = "Select CusNo from CusFil Where CusNo = '" & strCusNo & "'"
rs1.Open strParm, dbData, adOpenStatic, adLockOptimistic, adCmdText
If rs1.BOF And rs1.EOF Then
rs1.AddNew
Else
End If
With rs1
!CusNo = strCusNo
.Update
End With
rs1.Close
'
Set rs1 = Nothing
dbData.Close
Set dbData = Nothing
Is there an ADO.Net equivalent?
thanks,
hi friends
in my sqlserver 2005 i want to update two tables in a single query.
is it possible? or is it possible by creating a view?
thanks in advance.
shweta
Is it possible to update 2 tables in a single t-sql statement?
If Yes, whats the syntax?
Update Table1,Table2 ... is not working
A problem has come up in designing a Web page to maintain a small reference table in SQL Server 2000 (9 columns, about 25 records).
I tried to design the Web page to allow INSERT and UPDATE operations using a single page that posts back to itself. The page contains a set of empty form fields for a new record, followed by a set of filled-in form field for each row in the table. The form fields for existing records are given a unique name based on the field name concatenated with the primary key value for that row.
If I set up the page to INSERT only, it works properly. But when I add the fields for existing records, the INSERT operation malfunctions. Specifically, anytime a set of existing fields for a particular column is added to the page, the INSERT will no longer work properly for that column. This is true for all fields except the primary key field. It always INSERTs correctly. I tried adding only some columns to the set of existing form fields. In that case, the INSERT operation added the correct values for the fields that were not listed in the existing records section, but failed for the others.
I am using the INSERT INTO syntax for that operation and the recordset .Update syntax for the edits. I tried using the recordset .AddNew/.Update syntax for the insert, but it exhibited the same problems. The column data types contain smallint, bit, nvarchar, and ntext types.
I know that the correct values are being put into the INSERT statement. I also tried renaming the INSERT form fields to be totally different than the names of the existing record fields. But the problem comes back no matter what.
If necessary, I can split the logic so that inserts and updates are handled by different pages. But I would like to make this work if possible. If a reader knows why SQL Server is causing this problem, any help would be greatly appreciated.
Hello All,
I want to update multiple tables using single query and fields name are same of tables.
I am trying like:
update tablename1 t1,tablename2 t2 set t1.fieldname1 = t2.fieldname1 = 'value' where condition;
or
update tablename1 t1,tablename2 t2 set t1.fieldname1 = 'value' t2.fieldname1 = value where condition;
Plzzzzzz help me.Thanx in advance.
Thanx & Regards,
Smita.
Hi,
I am using SQl server 2005.
want to update rows in 2 tables,which have a relation on Id field.
Some thing like
Update tblA a , tblB b
Set a.UpdatedDt=getdate(),b.Updateddt=getdate()
where a.Id=b.Id and a.Name='xyz'
can anyone out there help me?
Thanks
Renu
I have Table Staffsubjects with columns and Values
Guid AcademyId StaffId ClassId SegmentId SubjectId Status
1 500 101 007 101 555 1
2 500 101 007 101 201 0
3 500 22 008 105 555 1
I need to do 3 scenarios in this table.
1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this, But the problem is If do the update, It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate
Here is the stored Procedure what i have wrote
ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier
[Code] .....
I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior.
For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this?
Thank you
I have Table Staffsubjects
with columns and Values
Guid AcademyId StaffId ClassId SegmentId SubjectId Status
1 500 101 007 101 555 1
2 500 101 007 101 201 0
3 500 22 008 105 555 1
I need to do 3 scenarios in this table.
1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this. But the problem is If do the update. It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate
Here is the stored Procedure what i have wrote
ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier
[Code] ....
I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052 Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
how to achieve this in single query?
Hi,
I'm trying to update 2 tables in SQL (say 2 Costumers table). 1 Lists all costumers per location( so 1 costumer can be placed in multiple locations), while the other is by location with contact details.
Is it possible for me to update both pages in 1 web update page?????
Thanks in advance.
Hi,
I am using sqlServer 2000.I want to insert as well as update Two Tables at the Same Time using storedprocedure.Both My Tables Have the Same columns only difference being the name.My Tables are,
ADDRESS AND ADDRESSBOOK and their values are Name, Address, Place,
Date,City.
How can achieve this task using stored Procedures.?
Any Help would be appreciated....
Thanks...
We are trying to update and insert to two different tables using the code below. However the code never excutes the second insert statement. (see noted area) Does anybody have any ideas what we are doing wrong? Any help would greatly be appreciated.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddPhoto]
@AlbumID int,
@Caption nvarchar(MAX)
AS
INSERT INTO [Photos] (
[AlbumID],
[Caption],
[Location],
[LastModified])
VALUES (
@AlbumID,
@Caption,
'tmpLocation', /* tmpLocation needed because app broke when Location column set to Allow NULLs */
GetDate())
/* Retrieve generated PhotoID */
DECLARE @PhotoID int
SET @PhotoID = SCOPE_IDENTITY()
/* Build unique location path from album and photo ID */
DECLARE @Location nvarchar(MAX)
SET @Location = '' + CONVERT(nvarchar(10), @AlbumID) + '' + CONVERT(nvarchar(10),@PhotoID) + '.jpg'
/* Update photo with new location path */
UPDATE [Photos]
SET
[Location] = @Location
WHERE
[PhotoID] = @PhotoID
/* Update photo with new location path */
******************************************The code never executes the statement below********************************************
INSERT INTO [PhotoDefault] (
[pidm],
[defaultPhoto],
[activityDate])
VALUES (
'1234',
'test',
getdate()
)
/* Return PhotoID and Location */
SELECT @PhotoID, @Location
RETURN
Thanks,
Jason
Hi!
I am trying to insert data into 2 different tables. I am using dataadapter and dataset.
Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
Call ConnectionString()
Dim insertSQL As New SqlCommand()
insertSQL.Connection = sqlConn
insertSQL.CommandText = "SELECT location.CountryName, location.CityName, location.BuildingName, location.FloorID, rooms.name, rooms.FloorID AS Expr1 FROM location INNER JOIN floors ON location.FloorID = floors.id INNER JOIN rooms ON floors.id = rooms.FloorID"
Dim ds As New DataSet()
Dim da As New SqlDataAdapter()
da.SelectCommand = insertSQL
Dim scb As New SqlCommandBuilder(da)
Try
da.Fill(ds)
Dim ndr = ds.Tables("location").NewRow
Dim ndr2 = ds.Tables("rooms").NewRow
ndr("FloorID") = FloorIDDDL.SelectedValue
ndr("CountryName") = CountryNameTextBox.Text
ndr("CityName") = CityNameTextBox.Text
ndr("BuildingName") = BuildingNameTextBox.Text
ndr2("name") = RoomNameTextBox.Text
ndr2("FloorID") = FloorIDDDL.SelectedValue
ds.Tables("location").Rows.Add(ndr)
ds.Tables("room").Rows.Add(ndr2)
da.Update(ds)
ErrMsgLbl.Text = "Information saved successfully"
Catch ex As Exception
ErrMsgLbl.Text = ex.ToString
End Try
sqlConn.Close()
End Sub
The above code does not throw any error. It also does not update the tables.
Your help will be appreciated.
Thanks!
How do i update the stats of tables when we insert data into it. I believe Auto stats update happens only when 500+ 20% of the rows are changed for a table. Once we insert say some 1000 records in to a particular table the query time takes too long (more than 1 min). The same query executes faster once i manually update the stats.
View 3 Replies View RelatedHi all,can somebody help to write this stored procedure Table1 Table2LogID MigIDUserMove LogIDUserNew Domain User The two tables are inner join with LogID.If in Table2 LogID=NULL then create new dataset in Table1 (INSERT)and then Update LogID in Table2IF in Table2 LogID= 2 (or something else) then update the dataset in Table1 with the same LogID Thanks
View 1 Replies View RelatedI want to be able to duplicate every single record that is inserted or updated in a particular table to another table, but not the delete. Is the best way to set-up a trigger? If so can anyone provide me with an example of how to do this? Also could you just duplicate certain columns in the row I would you have to do all columns?
Thanks for help.
I have created two table with same data structure. I need realtime effects (i.e. data) on both tables - Table1 & Table2.
Following Points to Consider.
1. Both tables are in the same database.
2. Table1 is using for data entry & I wants the same data in the Table2.
3. If any row insert, update & delete occers on Table1, the same effect should be done on Table2.
4. I need real time data insert, update & delete on Table2.
I knew that using triggers it could be possible, I have successfully created a trigger for inserting new rows (using logical table "Inserted") in Table2 but not succeed for update & delete yet.
I want to understand how can I impletement this successfully without any ambiguity.
I have attached data structure for tables. Thanx...
How do I deny insert / update / delete on a handful of tables for all DB users on a database? These users need and have DB_Datawriter permissions.
View 2 Replies View Relatedi've read the transact-sql command,
i known that the select command use to retrieve many fields from many tables with one command
select * from table1,table2
yes,
but i ' ve not seen the way to add,delete or update those fields from those tables with one command...
Is it possible? why?
I don't have any idea , can u help me
I want to know the sql commands , if it's possible
thanks for reply,
mochi
I'm trying to insert data into a table from two tables into a single table along with a hard coded value.
insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)
I get the following-
Msg 512, Level 16, State 1, Line 1
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.
Do I need to use a cursor?
I have a fundamental problem with how CDC works for bulk updates.When CDC enabled table is updated for single row - My CDC system tables its recording it as update (3 & 4) which is perfect and what it should be. No Complains!But when I do a bulk update in the same CDC enabled tables for the same columns - My CDC system tables its recording as delete and then insert (1 & 2). This is not correct and this is what my problem is. We used triggers before CDC we did not face this problem with triggers every thing was fine with triggers other than performance.The way how the CDC is handling the bulk update is a big problem for me because based on the output of CDC system tables we are doing some migration work to legacy system.
It will be impossible for me to go and change my migration logic scripts because we have 100's or procedures in it.Is it a know problem with CDC? Is there any solution in CDC when a bulk update happens on a table the CDC system tables record it as updates. I don't think CDC 'net changes' in this situation because the net change would show as single inserted row.If this can't be done with CDC then I have to completely abandon CDC and go back to triggers..
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies View RelatedIs there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?
View 4 Replies View RelatedIs it possible to retrieve all tables that a given one is related tovia foreign keys?
View 2 Replies View Related