Duplicate Tables Insert/Update In Another Table? Triggers?
I 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.
View Complete Forum Thread with Replies
Related Forum Messages:
Ntext And Update/insert Triggers
SQL Server 2000 : I have a series of tables which all have the samestructure. When any of these tables are modified I need to syncrhoniseall of those modifications with one other table wich is a sort of mergeof the individual tables with one extra column.For most of these tables this is not a problem. The problem arriveswhen one of the tables has an ntext column which obviously can not beused in an update or insert trigger.Here's an example of one of them:CREATE TABLE tblImages(ID INT IDENTITY(1,1) PRIMARY KEY,Inventory nvarchar(8) NOT NULL,Coll nvarchar(8) NOT NULL,ImageFile nvarchar(128) NOT NULL,ImageNotes ntext NULL,TS timestamp NULLCONSTRAINT U_Images UNIQUE NONCLUSTERED (ItemCode, Inventory, Coll,ImageFile)I then had created an update trigger which looked like this:CREATE TRIGGER COLLNAME_UTRIGGER ON COLLNAME_ImagesFOR UPDATEASBEGINUPDATE tblImages SETInventory = inserted.Inventory,Coll = 'COLLNAME',ImageFile = inserted.ImageFileName,FROM inserted INNER JOIN tblImages ON inserted.ItemCode =tblImages.ItemCode ANDinserted.Invventory = tblImages.Invventory AND tblImages.Coll ='COLLNAME' ANDinserted.ImageFileName = tblImages.ImageFileUPDATE tblImagesSET ImageNotes=inserted.NotesFROM inserted INNER JOIN tblImages ON inserted.ItemCode =tblImages.ItemCode ANDinserted.Inventory= tblImages.Inventory AND tblImages.Coll ='COLLNAME' ANDinserted.ImageFileName = tblImages.ImageFileEND " & vbCrLf)The first update in my trigger, be it an update or insert trigger,works fine. It crashes with the "Cannot use text, ntext or imagecolumns in the 'inserted' or 'deleted' tables." error in the secondpart.I have read various messages through the Internet on this and severalof them reference using INSTEAD OF triggers and views. I have neverused those before as this is my first work with SQL 2000. None of theexamples of INSTEAD OF triggers I have seen yet use the actual insertedtables and I haven't quite understood how to use them correctly.Can someone help me with the basic syntax as this trigger is one ofseveral that I am going to have to get working.Thank you in advance for any help, assistance, suggestions or"direction pointing" you may provide.
View Replies !
Triggers (insert Update Delete)
hi, is there a difference when I combine all 3 above triggers in one code vs putting code in 3 different triggers. for exmple, create trigger tr_name for insert, update, delete as ... code vs writting codein 3 separate triggers Thanks AAA
View Replies !
Triggers To Update Data By Joining Tables
Dear all, I am thinking about writting Triggers. There ar 3 tables I must use: BOOKING, RESIDENT, SCHEDULE First, if new records ar inserted in BOOKING and check value in field by joining RESIDENT & BOOKING, trigger will insert one more rows in SCHEDULE by joining BOOKING. Second, if specific records is updated or deleted, trigger will update or delete records by joining BOOKING and SCHEDULE. What should I do?? I have no idea about how to join tables and update, insert by triggers! Any suggestion?? Or other alternative is more good? Thanks a lot!!!!
View Replies !
Duplicate Last Record When Using SqlDataAdapter.Update For Insert Command
I'm getting duplicate records for the last record in the datatable. No matter how much or how little my datatable contains row records, it always duplicate the last one for some reason. Is there something wrong with my code below? EXAMID pulling from another stored procedure, which is outputed back to a variable. ---Data Access Layer---- If dt.Rows.Count > 0 Then 'INSERT EXAM ROSTERInsertComm = New SqlCommandsqladapter = New SqlDataAdapterInsertComm = New SqlClient.SqlCommand("ExamOfficers_AddOfficerSpecificExamRoster", conndb)InsertComm.CommandType = CommandType.StoredProcedure sqladapter.InsertCommand = InsertCommInsertComm.Parameters.Add("@examid", SqlDbType.Int)InsertComm.Parameters("@examid").Value = examidInsertComm.Parameters.Add("@officerid", SqlDbType.Int, 12, "Officer_UID")InsertComm.Parameters.Add("@reimburse", SqlDbType.Bit, 12, "ReimburseToDb")InsertComm.Parameters.Add("@posttest", SqlDbType.Int, 12, "Post_Test")InsertComm.Parameters.Add("@pqcdate", SqlDbType.DateTime, 12, "pqc_date")InsertComm.Parameters.Add("@pqcscore", SqlDbType.Int, 12, "pqc_score") conndb.Open() sqladapter.UpdateBatchSize = 100InsertComm.UpdatedRowSource = UpdateRowSource.Nonesqladapter.Update(dt) InsertComm.ExecuteNonQuery()InsertComm.Dispose() End If ----Stored Procedure---- ALTER PROCEDURE [dbo].[ExamOfficers_AddOfficerSpecificExamRoster] @ExamID as int,@OfficerID as int,@reimburse as bit=NULL,@posttest as int=NULL,@pqcdate as datetime=NULL,@pqcscore as int=NULL ASBEGIN SET NOCOUNT ON; Insert Into Exam_Officers(EXAM_UID,Officer_UID,reimburse,post_test,pqc_date,pqc_score)values(@ExamID,@OfficerID,@reimburse,@posttest,@pqcdate,@pqcscore) END
View Replies !
Plz Help About Use Triggers To Update Other Table
Plz help I try to use trigger to see if Component table update at same time update the AssemblySubcomponent AssemblyID but it say Msg 4104, Level 16, State 1, Procedure trigupdateSubcomponentID, Line 6 The multi-part identifier "a.SubcomponentID" could not be bound. the code look like create trigger trigupdateAssemblyID on Component for update, insert as begin if update (ComponentID) update AssemblySubcomponent set a.AssemblyID= i.ComponentD from inserted i join AssemblySubcomponent a on a.AssemblyID =i.ComponentID end
View Replies !
Triggers - Insert Data To Another Table
How do I set up an insert trigger to copy all of the inserted data to another table? In other words, when someone adds a new paramater in the params table, I want to automatically create a matching set of data in the goals table. Thanks,Krista
View Replies !
Insert / Update 2 Tables
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.
View Replies !
How To Insert & Update Two Tables .?
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...
View Replies !
Insert/Update Relational Tables Using Dataadapter
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!
View Replies !
Problem Doing Update And Insert To Different Tables In Same Procedure.
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
View Replies !
Trigger To Update A Table On Insert Or Update
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 Replies !
Stored Procedure - INSERT INTO Or UPDATE - INNER JOIN TWO TABLES
Hi 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 Replies !
Insert, Update && Delete On Two Tables With Same Data Structure...
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...
View Replies !
Can A Single Insert Statement Will Update Multiple Tables
Hai Guys: I have a situation where i need to insert a value called StudentID to several tables with one insert statement. Can you please inform me the SQL statement to acheive the above mentioned task. (by the way iam using SQL Server 2000, i dont need triggers / procedures as they are too tricky and hard to use). Thanks / Regards, Dotnet Geek.
View Replies !
CAN I Command (INSERT, DELETE, UPDATE) 2 Tables At The Same Time? POSSIBLE? HOW?
i'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
View Replies !
Can We Insert/Update Into Related Tables In A Single Round Trip ?
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
View Replies !
CREATE TABLE DUPLICATE OBJECT/DUPLICATE FIELD NAME ERROR Msg 2714
Hello Everyone: I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer. When I try to create the tables, for the query, I am getting the following error: Msg 2714, Level 16, State 4, Line 12 There is already an object named 'UserID' in the database. Msg 1750, Level 16, State 0, Line 12 Could not create constraint. See previous errors. I have duplicated this error with the following script: USE [testing] IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users1] CREATE TABLE [testing].[dbo].[users1] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users2] CREATE TABLE [testing].[dbo].[users2] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users3] CREATE TABLE [testing].[dbo].[users3] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database. I think that the schema is only allowing a single UserID primary key. How do I fix this? TIA
View Replies !
Update/Insert DataTable Into SQL Table
I'm importing XML file into DataTable and need to Insert Data into SQL Table. I'm not sure if its posible to take a DataTable with Data and insert into DataAdapter. From there i wanted to update SQL using TableAdapter? Any Tips? Thanks,
View Replies !
Update Table - Insert New Column
This should be easy for someone, but I just can't seem to find a sample to do this.....I have created a table...CREATE TABLE dbo.test ( oId int NOT NULL UNIQUE, test1 varchar(50) NOT NULL PRIMARY KEY )Now, I need to go back and simply add another column to the table such as test2 varchar(50)Not sure if the insert is the way to go and been playing around with various statements but with no luck.Suggestions?Thanks all,Zath
View Replies !
Insert And Update Trigger On Same Table
I currently have 2 tables as follows:CREATE TABLE [CRPDTA].[F55MRKT119](mhan8 int,mhac02 varchar(5),mhmot varchar(5),mhupmj int)GOCREATE TABLE [CRPDTA].[F55MRKT11](mdan8 int,mdac02 varchar(5),mdmot varchar(5),mdmail int,mdmag int,mdupmj int)What I would like to do is place a trigger on F55MRKT119 which willinsert records to the F55MRKT11 if they do not exist in that tablebased on the [mdan8] field. If the record does exist I would likeUpdate the corresponding record and increment either the [MDMAIL] orthe [MDMAG] based on the inserted [MHMOT]. What I have so far is asfollows:TRIGGER #1:CREATE TRIGGER trgIns_Summary ON [CRPDTA].[F55MRKT119]FOR INSERTASBEGININSERT INTO CRPDTA.F55MRKT11select INS.MHAN8, INS.MHAC02, INS.MHMOT,case when INS.MHMOT='MAG' then 0 ELSE 1 end,case when INS.MHMOT='MAG' then 1 ELSE 0 end,'0' from INSERTED INSWHERE ins.mhan8 not in(select mdan8 from crpdta.f55MRKT11)ENDTRIGGER #2:CREATE TRIGGER trgUpd_Summary ON [CRPDTA].[F55MRKT119]FOR UpdateASBEGINUPDATE CRPDTA.F55MRKT11SET MDMAIL= case when INS.MHMOT='MAG' then 0+MDMAILwhen INS.MHMOT<>'MAG' then 1+MDMAIL end,MDMAG= case when INS.MHMOT='MAG' then 1+MDMAGwhen INS.MHMOT<>'MAG' then 0+MDMAG endfrom INSERTED INS JOIN CRPDTA.F55MRKT11on(ins.mhan8=mdan8)ENDFor instance if I do the following insert:INSERT INTO CRPDTA.F55MRKT119VALUES('212131','VK4','AL4','0')thenINSERT INTO CRPDTA.F55MRKT119VALUES('212131','VK4','MAG','0')This is what I expect in both tables:[CRPDTA.F55MRKT119] (2 Records)MHAN8 MHAC02 MHMOT MHUPMJ------ ------ ----- ------212131 VK4 AL4 0212131 VK4 MAG 0[CRPDTA.F55MRKT11] (1 Record)MDAN8 MDAC02 MDMOT MDMAIL MDMAG MDUPMJ----- ------ ----- ------ ----- ------212131 VK4 AL4 1 1 0The insert part works fine in that it iserts in both tables with thecorrect values. However it seems as if the Update protion is failingfor some reason. WHat I have tried so far is setting the trigger orderfor the update to run first and vice-versa, but still no luck. Anyhelp would be appreciated.
View Replies !
Insert Trigger To Update Table
Hi,Does anyone know of a simple way to do this? I want to create aninsert trigger for a table and if the record already exists based onsome criteria, I want to update the table with the values that arepassed in via the insert trigger without having to use all the 'set'statements for each field (so if we add fields in the future I won'thave to update the trigger). In other words, I want the trigger codeto look something like this:if exists (select * from TableA where Fld1 = inserted.Fld1) then//don't do insert, do an update instead (would i want to rollback here?and will I have access to the 'inserted' table still?)Update TableASet TableA.<all the fields> = Inserted.<all the fields>where Fld1 = inserted.Fld1end ifAny help or ideas would be appreciated.Thanks,Teresa
View Replies !
UPDATE/INSERT To Make One-to-Many Table Become One-to-One
I have a scenario where two tables are in a One-to-Many relationshipand I need to move the data from the Many table to the One table sothat it becomes a One-to-One relationship.I need to salvage the records from the many table and without goinginto detail, one of the reasons I can't do the opposite asthere are records in the ONE table that I need to keep even if theydon't have any child records in the MANY table.Below I created the code to create the sample tables:1- tblProducts is the ONE side table2- tblProductDetails is the MANY side table3- tblProductsResult is the RESULT I expect to get after runningsome T-SQL code4- tblProductComponents is another MANY side table to tblProducts5- tblProductComponentsResult is the RESULT I expect to get...Some of the points to consider:6- Normally all UniqueID columns are to be IDENTITY. Forthis sample i am entering the UniqueID values myself.7- I don't want to create new tables like tblProductsResultand tblProductComponentsResult. I want to update the real tables.I have created the tblxxxResult tables only for this post.8- The goal is to update the name of the Product by giving it thename of the first matching Name from tblProductDetails.9- If there are more than one entry in tblProductDetails for eachProduct, then I need to create new Products inheriting the originalProduct's information including its child records from tblProductComponents.If you run the code and open the tables it will be much clearerto visually see what I want to achieve.CREATE DATABASE MyTestDBGOUSE MyTestDBGOCREATE TABLE [dbo].[tblProducts] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[TagNo] [int] NULL) ON [PRIMARY]GOINSERT INTO tblProducts VALUES (1, 'ABC', 55)INSERT INTO tblProducts VALUES (2, 'DEF', 66)INSERT INTO tblProducts VALUES (3, 'GHI', 77)INSERT INTO tblProducts VALUES (4, 'JKL', 88)CREATE TABLE [dbo].[tblProductDetails] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[ProductID] int) ON [PRIMARY]GOINSERT INTO tblProductDetails VALUES (1, 'ABC1', 1)INSERT INTO tblProductDetails VALUES (2, 'DEF', 2)INSERT INTO tblProductDetails VALUES (3, 'GHI', 3)INSERT INTO tblProductDetails VALUES (4, 'GHI2', 3)INSERT INTO tblProductDetails VALUES (5, 'GHI3', 3)INSERT INTO tblProductDetails VALUES (6, 'JKL2', 4)INSERT INTO tblProductDetails VALUES (7, 'JKL', 4)INSERT INTO tblProductDetails VALUES (8, 'JKL3', 4)INSERT INTO tblProductDetails VALUES (9, 'JKL4', 4)CREATE TABLE [dbo].[tblProductComponents] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[ProductID] int,[Component] [varchar] (80) NULL) ON [PRIMARY]GOINSERT INTO tblProductComponents VALUES (1, 1, 'ABCa')INSERT INTO tblProductComponents VALUES (2, 1, 'ABCb')INSERT INTO tblProductComponents VALUES (3, 1, 'ABCc')INSERT INTO tblProductComponents VALUES (4, 2, 'DEFa')INSERT INTO tblProductComponents VALUES (5, 2, 'DEFb')INSERT INTO tblProductComponents VALUES (6, 2, 'DEFc')INSERT INTO tblProductComponents VALUES (7, 2, 'DEFd')INSERT INTO tblProductComponents VALUES (8, 3, 'GHIa')INSERT INTO tblProductComponents VALUES (9, 4, 'JKLa')INSERT INTO tblProductComponents VALUES (10, 4, 'JKLb')CREATE TABLE [dbo].[tblProductComponentsResult] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[ProductID] int,[Component] [varchar] (80) NULL) ON [PRIMARY]GOINSERT INTO tblProductComponentsResult VALUES (1, 1, 'ABCa')INSERT INTO tblProductComponentsResult VALUES (2, 1, 'ABCb')INSERT INTO tblProductComponentsResult VALUES (3, 1, 'ABCc')INSERT INTO tblProductComponentsResult VALUES (4, 2, 'DEFa')INSERT INTO tblProductComponentsResult VALUES (5, 2, 'DEFb')INSERT INTO tblProductComponentsResult VALUES (6, 2, 'DEFc')INSERT INTO tblProductComponentsResult VALUES (7, 2, 'DEFd')INSERT INTO tblProductComponentsResult VALUES (8, 3, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (9, 4, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (10, 4, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (11, 5, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (12, 6, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (13, 7, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (14, 7, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (15, 8, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (16, 8, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (17, 9, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (18, 9, 'JKLb')CREATE TABLE [dbo].[tblProductsResult] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[TagNo] [int] NULL) ON [PRIMARY]GOINSERT INTO tblProductsResult VALUES (1, 'ABC1', 55)INSERT INTO tblProductsResult VALUES (2, 'DEF', 66)INSERT INTO tblProductsResult VALUES (3, 'GHI', 77)INSERT INTO tblProductsResult VALUES (4, 'JKL', 88)INSERT INTO tblProductsResult VALUES (5, 'GHI2', 77)INSERT INTO tblProductsResult VALUES (6, 'GHI3', 77)INSERT INTO tblProductsResult VALUES (7, 'JKL2', 88)INSERT INTO tblProductsResult VALUES (8, 'JKL3', 88)INSERT INTO tblProductsResult VALUES (9, 'JKL4', 88)I appreciate your assistance on this.Thank you very much
View Replies !
Trigger To Insert Or Update 2nd Table
I am new to triggers and need help on the following: I have a hourly table that inserts new rows every hour but I need to either Insert or Update the daily table with the sum of the reading from the hourly table. If a row exist in the daily table with the date of the hourly table, then I need to update this row but if it doesn't exist, I need to insert this row. Thanks for any suggestions... Alan
View Replies !
Table Insert For Three Tables??
I have 3 tables, Type, Subtype, and Subtype2. All which are related by the ID of preceding table. Using a 3 textboxes and a button I want to insert new Types and subtypes to the tables. How do I go about identifying the new types new ID insert it in to the other tables? I am using ASP.net (vb) with sql server.
View Replies !
Insert From One Table Into 2 Tables
Hi, i need to create a stored procedure that will take data from one table and insert it into one tabe. I have 3 tables tblTest is the table that i need to take the informations from. intD1 to intD4 is a score I don't know if iexplain my sefl correctly tks in advance! tblTest 1 , 2 , 'A', 'B', 0 , 1 ,2 , 4 i need this tblResultat 1, 2, 'A','B' tblResultatXReponse 1,1,0,0 2,1,1,1 3,1,2,2 4,1,4,3 tblTest intTestId autonumber intCoursId strCodeAbsent1 strCodeAbsent2 intD1 intD2 intD3 intD4 tblResultat intResultatsId autonumber intCoursId strCodeAbsent1 strCodeAbsent2 tblResultatXReponse intResultatXReponseId autonumber intResultatsId intReponseDesc intOrdre
View Replies !
Lookup Table Insert, Update, And Delete...
All, Just wondering if anyone is aware of a SQL server shareware utility that places a front end on a table to manage insert, update, and delete of rows on a lookup table. We can certainly write this but before reinventing the wheel I figure I'd ask and see. Many Thanks, Isaac
View Replies !
Insert And Update A Table In Single Web Page
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.
View Replies !
Update Or Insert Into SQL Table From Excel Source
Hi all, I've had little success gooling/searching for this (so far). Given a simple spreadsheet: StoreNumber StoreName 1 UPDStoreName_1 2 UPDStoreName_2 3 UPDStoreName_3 4 NEWStoreName_4 I want to have an SSIS package that will update a table: mystores (storenumber int, storename nvarchar(255)) StoreNumber StoreName 1 StoreName_1 2 StoreName_2 3 StoreName_3 5 StoreName_5 .. what I need to do is insert the new, update the existing and leave the remaining unchanged. i.e. : StoreNumber StoreName 1 UPDStoreName_1 2 UPDStoreName_2 3 UPDStoreName_3 4 NEWStoreName_4 5 StoreName_5 (the UPD and NEW are added to simplify the example). Now the default action of an excel source into an ole db destination is an insert into the table - so PK constraints causes failures. Now, given that the table is referred to by other table, and is in a 24x7 website, how do I change the SSIS package such that, on a row-by-row basis, anUpSert (update or insert) is performed? The only idea I have so far is: create temp table insert excel data into temp table iterate through the table, using if exists ... update else insert logic <-- this to be done in a SP Isn't there a better way?
View Replies !
Identifying A Table Update / Insert / Delete
How to find out that a table has changed. For example if a table has 50K rows, and if any update, insert, or delete was made it should be captured without using any trigger. Is it possible to get such information from any of the system table or DMVs?
View Replies !
Insert Or Update Null If The Value Is Zero Using Trigger On Table
Hi , I have 2 tables (Dept and Emp) The columns in table Dept are Deptno and Deptname. Deptno is bigint and it is primary key. In Emp table, columns are Empno(PK) ,EmpName and Deptno(foreign key referring to Dept) To Insert or Update record in Emp through application, value of Deptno is coming as 0(Zero). I want the value of Deptno to be inserted or updated as null if the value is Zero (0). How to do this in sql server 2005 by using trigger on table Emp Thanks in advance regards, Srinivas Govada
View Replies !
Update/insert The Xml Data In Database Table
From: JAGADISH KUMAR GEDELA [jgedela@miraclesoft.com] Sent: 10/10/2007 4:13:43 PM To: jgedela@miraclesoft.com [jgedela@miraclesoft.com] Subject: forum Hi all, I need to Insert the XML File data into SQL SERVER 2005 db(table). For that I created the table with XML Native column (using typed xml) *********************************create table command************ CREATE TABLE XmlCatalog ( ID INT PRIMARY KEY, Document XML(CONTENT xyz)) *********************************** In order to Create the table with typed xml ,before that we have to create the xml schema which i mentioned below ************************************create schema command******** CREATE XML SCHEMA COLLECTION xyz AS 'Place xml schema file ’ ************************************ I created the xml schema file by using the xmlspy software. --------------------------Insert command--------- INSERT into XmlCatalog VALUES (1,'copy xml file ‘) ------------------------------- I need to retrieve the xml data from the table ------------select query---------- SELECT Document.query (‘data (/X12//UserId)') AS USERID, Document.query (‘data (/X12/X12_Q1/header/ISA//ISA_Authorization_Information_Qualifier)') AS ISA_Authorization_Information from XmlCatalog. ----------------- I Need to update/insert/delete the xml data in the table Can you please suggest the procedure to implement the above requirement(insert/update/delete)
View Replies !
INSERT Among 3 Tables And 1 Is A Junction Table
Looking for some help here, so thanks for any input. I'm a painfully new newbie to SQL scripting.Situation: I have a simple database to handle an organization's events. Those events are categorized and may have more than one category assigned to each event. I need a maintenance Web Form to update their events.Set Up (so far): I have a CATEGORIES table. It has an auto incrementing UID and a Category name field. This table will be updated so infrequently, I plan to update it manually (no need for a maintenance Web Form). Next is the EVENTS table. It also has an auto incrementing UID along with several fields (Title, Location, DateTime, etc.). The junction table is named jEVENTSCATEGORIES. It has its own auto incrementing UID along with 2 fields named for the primary keys (UIDs) in the other 2 tables (EventsID and CategoryID).Goal: On the Web Form, I have a CheckBoxList control that's populated by the CATEGORIES table. One or more categories can be checked for each event. I have a FormView control that allows Edits and Inserts.Need: I need to know the INSERT statement(s) required to insert a new record in the EVENTS table and then to update one or more rows in the junction table (jEVENTSCATEGORIES).My Assumptions: I know how to create SELECTs and INSERTs and whatnot, but I'm not certain how to create a second INSERT statement that is based on a variable (or output) from a previous action. So any help would be MUCH appreciated. Thanks for your time!
View Replies !
Multiple Triggers On A Table Or Encapsulated Triggers
This isn€™t an problem as such, it€™s more of a debate. If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own. www.handleysonline.com
View Replies !
UPDATE Table From A Remote DB OR INSERT If Doesnt Excists.
I have been looking for a solution for this for some time and have came up empty handed. I have 2 servers development box and a live box. Time has passed and my live box has a lot of new data in the database and now I need to update the dev box so I can properly test with real data. Problem here is I want to keep the records that are in the dev box, update them if they exsist on the live box, because live server may or may not contain that record and isert all records that are not on the dev box database. I hope I am making some sense here, I think I am just making it more difficult then it has to be. Any suggestions? Lito
View Replies !
Automatically Trigger A Sum From One Table To Another Upon Update/insert Query
I'm trying to update (increment) Company.SumtotalLogons from CompanyUsers.NumberOfLogons where CompanyUsers.CompanyID = Company.CompanyID I'd like to either write a formula (if it is even possible to fire a formula from one table update/insert to increment a field in another table), or a stored procedure that triggers an auto update/append into Company.SumTotalLogons I know this is possible in access, so i'm wondering how to go about it in ms-sql? any ideas?
View Replies !
Instead Of Insert Trigger Failed To Update Secondary Table Through ODBC
FYI: I'm using SQL2005 on a windows 2003 server. So, I've written an Instead of Trigger to update a foreign key field based on information in another field of the same record. To add some error handling to the process I updated the Trigger to insert any records that don't have legitimate foreign keys into a second table. This process works great when I test it by just adding a record using the table view in the SQL Management Studio or through a query run in the query browser. However, when a record is added via an ODBC connection I get foreign key constraint errors and records are not added to the second table. If the foreign key is legit the record is added and the part of trigger that updates that keyed field executes just fine. Is anyone aware of this issue? Is there a way around it? I found the following MSKB article but I'm not sure if it applies to my situation: http://support.microsoft.com/kb/304096 Here's my current code, if that track the problem in anyway: Code: ALTER TRIGGER UpdateTicketID ON Email Instead of INSERT AS IF ((Select charindex('{', [subject]) FROM Inserted) = 0) BEGIN INSERT INTO BadEmail ([Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLInk, SendTo, Cc, ContactID) Select [Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLink, SendTo, Cc, ContactID From Inserted END ELSE IF ((Select substring([subject], charindex('{', [subject])+1, (charindex('}', [subject]) - charindex('{', [subject]))-1) From Inserted) NOT In (Select TicketID From Ticket)) BEGIN INSERT INTO BadEmail ([Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLInk, SendTo, Cc, ContactID) Select [Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLink, SendTo, Cc, ContactID From Inserted END ELSE BEGIN INSERT INTO Email ([Subject], Sender, Body, ticketID, EntryID, LastModificationTime, AttachmentLink, SendTo, Cc, ContactID) Select [Subject] , Sender , Body , substring([subject], charindex('{', [subject])+1, (charindex('}', [subject]) - charindex('{', [subject]))-1) , EntryID , LastModificationTime , AttachmentLink , SendTo , Cc , ContactID From Inserted END GO Thank very much for any help. -Will
View Replies !
Select From Multiple Tables, Insert In Temp Table
What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data? Any ideas? Thanks in advance.
View Replies !
Insert Items From One To Table To Multiple Smaller Tables
I have a table that I filled with data imported from another database. What I need to do is now take this huge table and break apart the information and put it into 5 smaller tables. So I have a huge insert statement. I have one main table called Property with two keys. One key is a "Prop_ID" and the other is "owner" where Prop_Id is a automated unique ID. Once the information is inserted into that table, I then get the Unique ID that it was given, and I then used that ID to insert into the other tables. The problem I am encountering is I keep getting the following error Violation of PRIMARY KEY constraint 'PK_Prop_Res_Detail'. Cannot insert duplicate key in object 'Prop_Res_Detail'. The statement has been terminated. I have an idea what might be going wrong, but I am not sure. What I want to happen is that I want the query to look at the first row of the huge table and then do all 4 of the inserts, and then go to the next row. But I think it is trying to all the inserts into the property table, and then go on to the Prop_Res_Detail table and that is why I am getting that error. Any help is greatly appreicated. here is the code.. Code: CREATE PROCEDURE [dbo].[Insert_Properties] AS DECLARE @Prop_ID Int SET NOCOUNT ON INSERT INTO Property(Acres, Assoc_Phone, Assoc_Cell, AppraisalForm, Area, Assess_Account, AttachDetach, Block, City, County, Directions, DOM, ER_EA, FloodZone, Import_From, Import_ID, Insert_Date, LandSQFT, LandSQFTDim, LegalRemarks, ListAppraiser_ID, ListAssoc_ID, ListBroker_ID, ListDate, Listing_Office_Remarks, ListPrice, Lot, Map, Num_Images, Office_Phone, Original_ListPrice, Owner, Pending_Date, PhotoName, PropSubType, Prop_Type, Quad, Remarks, State, Status, StreetDir, StreetNum, StreetName, Township, UnitNumber, ZipCode) SELECT CONVERT(FLOAT(8), Acres), CONVERT(Varchar(25), Assoc_Phone), CONVERT(Varchar(25),Assoc_Cell), CONVERT(Varchar(50), AppraisalForm), CONVERT(Varchar(10), Area), CONVERT(Varchar(50), Assess_Account), CONVERT(Varchar(20), AttachDetach), CONVERT(Varchar(20), Block), CONVERT(Varchar(40), City), CONVERT(Varchar(50), County), CONVERT(Varchar(1000), Directions), CONVERT(int, DOM), CONVERT(Varchar(10), ER_EA), CONVERT(Varchar(50), FloodZone), CONVERT(Varchar(20), Import_From), CONVERT(Varchar(20), Import_ID), CONVERT(datetime, Insert_Date, 101), CONVERT(Varchar(20), LandSQFT), CONVERT(Varchar(50), LandSQFTDim), CONVERT(Varchar(2000), LegalRemarks), CONVERT(Varchar(50), ListAppraiser_ID), CONVERT(Varchar(50), ListAssoc_ID), CONVERT(Varchar(50), ListBroker_ID), CONVERT(varchar(11), ListDate), CONVERT(Varchar(1000), Listing_Office_Remarks), CONVERT(Varchar(10), ListPrice), CONVERT(Varchar(20), Lot), CONVERT(Varchar(10), Map), CONVERT(Varchar(10), Num_Images), CONVERT(Varchar(25), Office_Phone), CONVERT(Varchar(10), Original_ListPrice), CONVERT(Varchar(50), Owner), CONVERT(datetime, Pending_Date, 101), CONVERT(Varchar(50), PhotoName), CONVERT(Varchar(25), PropSubType), CONVERT(Varchar(20), Prop_Type), CONVERT(Varchar(10), Quad), CONVERT(Varchar(1000), Remarks), CONVERT(Varchar(25), State), CONVERT(Varchar(10), Status), CONVERT(Varchar(4), StreetDir), CONVERT(Varchar(15), StreetNum), CONVERT(Varchar(50), StreetName), CONVERT(Varchar(20), Township), CONVERT(Varchar(6), UnitNumber), CONVERT(Varchar(20), ZipCode ) FROM Imported_Closed_Property_From_MLS SET @Prop_ID = @@Identity /*Property Res Table */ INSERT INTO Prop_Res_Detail(Prop_ID, Addition, Appliances, Basement_Area, BasementDesc, Builder, Construction, Cool, Dining, District_School, Energy, Exterior_Features, Fence, Floors, Foundation, FP, FP_Type, Garage_Attach_Detach, Garage_Cap, Handicap, Heat, HOA, HOA_Fee, HOA_Inc, HOA_Period, Inlaw_Plan, Interior_Features, Livestock, Lot_Desc, Mechanical, NumLivingArea, Num_Baths, Num_Beds, Num_Levels, Other_Info, OvenDesc, Owner, Parking, Patio, Patio_Dim, Perc_Basement_Com, Pool, Pool_Type, Prop_Faces, Range, RangeDesc, Remodeled, Rental, RentalAmount, Roof_Type, Roof_Year, RoomOther, Sect, SQFT, SQFTSource, Style, Tax_Amount, Tot_Rooms, UtilityAvailable, WindowType, Year_Built) SELECT @Prop_ID, CONVERT(Varchar(50), Addition), CONVERT(Varchar(100), Appliances), CONVERT(Varchar(25), Basement_Area), CONVERT(Varchar(100), BasementDesc), CONVERT(Varchar(50), Builder), CONVERT(Varchar(50), Construction), CONVERT(Varchar(20), Cool), CONVERT(Varchar(10), Dining), CONVERT(Varchar(60), District_School), CONVERT(Varchar(100), Energy), CONVERT(Varchar(100), Exterior_Features), CONVERT(Varchar(40), Fence), CONVERT(Varchar(100), Floors), CONVERT(Varchar(40), Foundation), CONVERT(Varchar(50), FP), CONVERT(Varchar(40), FP_Type), CONVERT(Varchar(50), Garage_Attach_Detach), CONVERT(Varchar(25), Garage_Cap), CONVERT(Varchar(20), Handicap), CONVERT(Varchar(20), Heat), CONVERT(Varchar(40), HOA), CONVERT(Varchar(30), HOA_Fee), CONVERT(Varchar(100), HOA_Inc), CONVERT(Varchar(20), HOA_Period), CONVERT(Varchar(20), Inlaw_Plan), CONVERT(Varchar(100), Interior_Features), CONVERT(Varchar(40), Livestock), CONVERT(Varchar(400), Lot_Desc), CONVERT(Varchar(100), Mechanical), CONVERT(Varchar(10), NumLivingArea), CONVERT(Varchar(5), Num_Baths), CONVERT(Varchar(5), Num_Beds), CONVERT(Varchar(30), Num_Levels), CONVERT(Varchar(100), Other_Info), CONVERT(Varchar(100), OvenDesc), CONVERT(Varchar(50), Owner), CONVERT(Varchar(100), Parking), CONVERT(Varchar(25), Patio), CONVERT(Varchar(50), Patio_Dim), CONVERT(Varchar(25), Perc_Basement_Com), CONVERT(Varchar(20), Pool), CONVERT(Varchar(20), Pool_Type), CONVERT(Varchar(40), Prop_Faces), CONVERT(Varchar(20), Range), CONVERT(Varchar(100), RangeDesc), CONVERT(Varchar(50), Remodeled), CONVERT(Varchar(10), Rental), CONVERT(Varchar(10), RentalAmount), CONVERT(Varchar(20), Roof_Type), CONVERT(Varchar(5), Roof_year), CONVERT(Varchar(100), RoomOther), CONVERT(Varchar(10), Sect), CONVERT(Varchar(10), SQFT), CONVERT(Varchar(50), SQFTSource), CONVERT(Varchar(100), Style), CONVERT(Varchar(10), Tax_Amount), CONVERT(Varchar(5), Tot_Rooms), CONVERT(Varchar(100), UtilityAvailable), CONVERT(Varchar(50), WindowType), CONVERT(Varchar(5), Year_Built) FROM Imported_Closed_Property_From_MLS /*Sold Info Table */ INSERT INTO Sold_Info(Prop_ID, Buy_Pts, Closed_Date, Closed_Price, Closed_Price_SQFT, COOP_Sales, Days_On_Market, InterestRate, Lender, LoanAmount, LoanTerms, Loan_Years, Origination_Fee, Owner, SellerConcessions, LoanType, Sold_Remarks) SELECT @Prop_ID, CONVERT(Varchar(10), Buy_Pts), CONVERT(datetime, Closed_Date, 101), CONVERT(Varchar(10), Closed_Price), CONVERT(Varchar(50), Closed_Price_SQFT), CONVERT(Varchar(50), COOP_Sales), CONVERT(Varchar(5), DOM), CONVERT(Varchar(10), InterestRate), CONVERT(Varchar(50), Lender), CONVERT(Varchar(10), LoanAmount), CONVERT(Varchar(50), LoanTerms), CONVERT(Varchar(10), Loan_Years), CONVERT(Varchar(10), Origination_Fee), CONVERT(Varchar(50), Owner), CONVERT(Varchar(100), SellerConcessions), CONVERT(Varchar(25), LoanType), CONVERT(Varchar(1000), Sold_Remarks) FROM Imported_Closed_Property_From_MLS /*Remarks Table */ INSERT INTO Remarks(Prop_ID, App_Date, App_Remark, Contract_Date, Inspection_Type, Owner, PendingSalesPrice, PendingSaleComments) SELECT @Prop_ID, CONVERT(datetime, App_Date, 101), CONVERT(Varchar(1000), App_Remark), CONVERT(datetime, Contract_Date, 101), CONVERT(Varchar(50), Inspection_Type), CONVERT(Varchar(50), Owner), CONVERT(Varchar(10), PendingSalesPrice), CONVERT(Varchar(1000), PendingSaleComments) FROM Imported_Closed_Property_From_MLS GO
View Replies !
Merge Data From Two Tables Into One Table - No Updates/only Insert
Hi all,, I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS.. May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS.. I have a flat file that I want to merge with table in SQL server 2005. 1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package). Now here is my question. I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design). I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same).. Here is an example: Table A -------------- 1 test test1 test2 test3 test4 test5 2 test test6 test7 test8 test9 test10 Table X ------------ 1 test test1 test2 test99 test4 test5 2 test test98 test97 test 96 test95 test94 -------------------------------------------------------- Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1.. The new Table A should look like NEW Table A' ----------------- test test1 test2 test3 test4 test5 test test6 test7 test8 test9 test10 test test98 test97 test 96 test95 test94 ------------------------------------ I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this? Thanks in advance.
View Replies !
VS2005 - Using Detailsview To Update, Insert And Delete Rows From SQL 2005 Database. Delete And Insert Work But Update Does Not - No Errors Returned
Using VS 2005 DetailsView to insert, delete, and update rows in SQL 2005 database. insert and delete work but update does not. I recieve no errors and the detailsView comes back unchanged (as well as table row is unchanged). I am trying to use as little code behind as possible. However I do have ItemUpdating routines that seem to work (i.e Checking table for new login duplicates and encrypting passwords). The following is the source code generated by VS2005:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="frmDbRegionMgrNew.aspx.vb" Inherits="frmDbRegionMgrNew" Title="Region Manager DB Update" Theme="detailsVeiwTheme" %><%@ Import Namespace="System.Data" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"><title>Untitled Page</title> </head><body><form id="form1" runat="server"><div><asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Style="z-index: 101; left: 14px; position: absolute; top: 10px" Width="199px">Data Base Maintenance </asp:Label><asp:HyperLink ID="HyperLink1" runat="server" BackColor="ActiveBorder" BorderColor="ActiveBorder"BorderStyle="Outset" Font-Bold="True" Font-Size="X-Small" ForeColor="#004000"Height="31px" NavigateUrl="DataBaseMaint.aspx" Style="z-index: 133; left: 524px;position: absolute; top: 7px" Width="96px">DB Main Menu</asp:HyperLink><br /><br /><br /><table style="width: 654px"><tr><td style="width: 120px"><asp:Label ID="Label2" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Text="Regional Manager" Width="189px"></asp:Label></td><td style="width: 100px"></td><td style="width: 203px"></td></tr><tr><td style="width: 120px" valign="top"><asp:Label ID="Label3" runat="server" Font-Bold="True" Font-Size="Small" ForeColor="#004000"Style="z-index: 128; left: 2px; position: absolute; top: 115px" Width="128px">Select Greenhouse -></asp:Label></td><td style="width: 100px" valign="top"><asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"DataTextField="Name" DataValueField="GrnHseID"></asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>"SelectCommand="SELECT [GrnHseID], [Name] FROM [Greenhouse]"></asp:SqlDataSource></td><td style="width: 203px"> <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"DataKeyNames="RegionMgrID" DataSourceID="SqlDataSource2" Height="50px" Width="125px"><Fields><asp:TemplateField HeaderText="RegionMgrID" InsertVisible="False" SortExpression="RegionMgrID"><EditItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Eval("RegionMgrID") %>'></asp:Label></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("RegionMgrID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="GrnHseID" SortExpression="GrnHseID"><EditItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Eval("GrnHseID") %>'></asp:Label></EditItemTemplate><InsertItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:TextBox></InsertItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundField DataField="DispLvl" HeaderText="DispLvl" SortExpression="DispLvl" /><asp:BoundField DataField="FName" HeaderText="FName" SortExpression="FName" /><asp:BoundField DataField="Minit" HeaderText="Minit" SortExpression="Minit" /><asp:BoundField DataField="LName" HeaderText="LName" SortExpression="LName" /><asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /><asp:BoundField DataField="Ext" HeaderText="Ext" SortExpression="Ext" /><asp:BoundField DataField="Cell" HeaderText="Cell" SortExpression="Cell" /><asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" /><asp:BoundField DataField="Login" HeaderText="Login" SortExpression="Login" /><asp:BoundField DataField="Password" HeaderText="Password" SortExpression="Password" /><asp:BoundField DataField="AccessLvl" HeaderText="AccessLvl" SortExpression="AccessLvl" /><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /></Fields></asp:DetailsView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>" DeleteCommand="DELETE FROM [RegionMgr] WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"InsertCommand="INSERT INTO [RegionMgr] ([GrnHseID], [DispLvl], [FName], [Minit], [LName], [Phone], [Ext], [Cell], , [Login], [Password], [AccessLvl]) VALUES (@GrnHseID, @DispLvl, @FName, @Minit, @LName, @Phone, @Ext, @Cell, @Email, @Login, @Password, @AccessLvl)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [RegionMgr] WHERE ([GrnHseID] = @GrnHseID)"UpdateCommand="UPDATE [RegionMgr] SET [GrnHseID] = @GrnHseID, [DispLvl] = @DispLvl, [FName] = @FName, [Minit] = @Minit, [LName] = @LName, [Phone] = @Phone, [Ext] = @Ext, [Cell] = @Cell, = @Email, [Login] = @Login, [Password] = @Password, [AccessLvl] = @AccessLvl WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"><DeleteParameters><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></DeleteParameters><UpdateParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></UpdateParameters><SelectParameters><asp:ControlParameter ControlID="DropDownList1" Name="GrnHseID" PropertyName="SelectedValue"Type="Int32" /></SelectParameters><InsertParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /></InsertParameters></asp:SqlDataSource> </td></tr><tr><td style="width: 120px"></td><td style="width: 100px"></td><td style="width: 203px"></td></tr></table></div></form></body></html>Appreciate any and all help!Dave
View Replies !
Insert Multiple Rows To Table Based On Values From Other 2 Tables.
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT. I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId". What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display. Sample: ProjectId - SiteId 1 - 5 1 - 9 1 - 16 1 - 18 1 - 20 1 - 27 1 - 31 ProjectId stays the same, only values for SiteId are being different. I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
View Replies !
Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements
Hi can anyone help me with the format of my stored procedure below. I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true. At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2 @publicationID Int=null,@typeID smallint=null, @title nvarchar(MAX)=null,@authorID smallint=null AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ERROR Int --Create a new publication entry INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) --Obtain the ID of the created publication SET @publicationID = @@IDENTITY SET @ERROR = @@ERROR --Create new entry in linking table PublicationAuthors INSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SET @ERROR = @@ERROR IF (@ERROR<>0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
View Replies !
Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements
Hi can anyone help me with the format of my stored procedure below. I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true. At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2 @publicationID Int=null,@typeID smallint=null, @title nvarchar(MAX)=null,@authorID smallint=null AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ERROR Int --Create a new publication entry INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) --Obtain the ID of the created publication SET @publicationID = @@IDENTITY SET @ERROR = @@ERROR --Create new entry in linking table PublicationAuthors INSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SET @ERROR = @@ERROR IF (@ERROR<>0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
View Replies !
INSERT INTO... SELECT... Cannot Insert Duplicate Key...
I want to add the content of a table into anotherI tried to copy all fields, except the primary key:INSERT INTO table2(field2, field3, field4, ...)SELECT field2, field3, field4, ...FROM anotherDB.dbo.table1gives the following error:Violation of UNIQUE KEY constraint...Cannot insert duplicate key...Why?I didn't ask him to copy the key column; Isn't the SQL Server supposedto know how to increment the key ?
View Replies !
How To Insert Several Insert Commands, Triggers?
Hello, what i want is simple. This is a simple forum, it has several topics (that the users can create), when a user create a topic, its stored in forum_topics. The user can then view the topic and post a response that is store in forum_answer, the user can also add this to his favorite list, forum_favorites is simple, contains a TopicID that refers to the topic, a username of the user that has the topic on his favorite list and a auto increment id to be able to delete specified topic favorites. Now my question is: when a user posts a answer to Topic X, i want a predefined message to be sent to post_inbox for all the users that has Topic X in their favorite list. How can i get MS SQL 2005 to get all the users from Topic X and then loop thru them and insert a new post into post_inbox? Patrick
View Replies !
|
TRACKER
