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,
Can you use a DataTable to update an SQL Table and can it be done in a batch UPDATE as opposed to incrementing through every row using a stored procedure similar to UPDATE SQLTable SET Position = @Position WHERE ID = @ID?
View 3 Replies View RelatedI passed .net datatable from a .net app to a store procedure. From this store procedure, how to code to bulk insert (or another way) to SQL table?
View 7 Replies View RelatedHi
I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table
Please help
Thanks
CP
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
hi,
I have my database stored in the sqlserver 2005. Using the table name i am retrieving the table and it is displayed to the user in the form of datagridview.I am allowing the user to modify the contents of the table, including the headers. Is it possible for me to update the table straightway rather than giving a sql update command for each and every row of the table .
Pls reply asap....
-Sweety
Hi, I have the code below. I need to skip the first row in the datatable as it has the headers. This works now, but my gridview gets the header row inserted as a record.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection) ' old method: Lots of INSERT statements ' first, create the insert command that we will call over and over: destConnection.Open() Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection) ins.CommandType = CommandType.Text ins.Parameters.Add("@contactdate", SqlDbType.Text) ins.Parameters.Add("@dnbnumber", SqlDbType.Text) ins.Parameters.Add("@prospectname", SqlDbType.Text) ins.Parameters.Add("@businessofficer", SqlDbType.NVarChar) ins.Parameters.Add("@phonemeeting", SqlDbType.Text) ins.Parameters.Add("@followupcalldate2", SqlDbType.Text) ins.Parameters.Add("@phonemeetingappt", SqlDbType.Text) ins.Parameters.Add("@followupcalldate3", SqlDbType.Text) ins.Parameters.Add("@appointmentdate", SqlDbType.Text) ins.Parameters.Add("@appointmentlocation", SqlDbType.Text) ins.Parameters.Add("@appointmentkept", SqlDbType.Text) ins.Parameters.Add("@applicationgenerated", SqlDbType.Text) ins.Parameters.Add("@applicationgenerated2", SqlDbType.Text) ins.Parameters.Add("@applicationgenerated3", SqlDbType.Text) ins.Parameters.Add("@comments", SqlDbType.Text) ins.Parameters.Add("@newaccount", SqlDbType.Text) ins.Parameters.Add("@futureopportunity", SqlDbType.Text) ' and now, do the work: For Each r As DataRow In sourceTable.Rows For i As Integer = 0 To 16 ins.Parameters(i).Value = r(i) Next ins.ExecuteNonQuery() 'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then 'Console.WriteLine("-- copied {0} rows.", rowscopied) 'End If Next End Using destConnection.Close() End Sub
View 3 Replies View RelatedHi All!! I need to export a whole datatable to the database, through bulk insert or any other method..Can any body help me in that??Yes, col's in the database table and datatable are same!!Thx,Regards,Nilee..
View 4 Replies View Relatedtable2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
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
Imagine the following scenario: two tables (say, "requests" and "details") are joined in a 1:n relationship on MSSQL 2008. Both tables contain an ID (autoincrement field) and a timestamp field for proper concurrency management. Data access in the frontend is provided by a typed dataset in VS 2010. There are SPs on the server which select, update, insert or delete data in each of the two tables (so, 8 SPs alltogether: uspRequestsSelect, uspDetailsInsert etc.). These SPs are used for data access in the dataset. The GUI is a Windows form with 2 datagridviews, one for request datatable and one for the child-relation-based datatable FK_request_details. So, each request shows its details. The form works well so far.
Now, trouble strikes. A business rule says: "the first details row of a request (=row with lowest ID) always has a 0 in column "additional fee". For additional detail rows, this field has to be set to constant value 45". In short: the first detail row of each request is free, second and later details are charged 45 €.
So, I created a SP "uspRequestFeeManager", which recalculates all (!) detail rows of a request. This SP is called in uspDetailsInsert, uspDetailsUpdate and uspDetailsDelete, as each of this cases causes the additional fee to be recalculated for all rows (as rowcount can change). In Management Studio, this works as well!
But: as the uspRequestFeeManager changes data even for rows the user did NOT touch, there's a concurrency exception in my frontend in the following case:
In a request with 2 detail rows (first row has fee = 0, second = 45), the user deletes the row with fee = 0. Committing via TableAdapterManager calls uspDetailsDelete, which calls uspRequestFeeManager, which sets the remaining single details row to a fee value of 0 (which is correct!). This causes TableAdapterManager.UpdateAll to fail ("concurrency exception; delete command has handled 0 of 1 expected records"), as uspRequestFeeManager has "edited" a row which the user didn't touch, and thus updated its timestamp as well. So, the list is out of sync.
The uspRequestFeeManager looked like a good idea... but it seems not to be.
Approach would be: instead of calling the uspRequestFeeManager from within the SP, call it programmatically after TableAfterManager.UpdateAll, and after that, 're-fill' the details datatable with the updated data. But that would transfer business logic from server to client. I don't like that...
We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region. this process is schedule, every 5 min job is run and Update fact table.but time of Insert and Update too long from staging to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number how many region we need to update and at a time single Region we are updating using while loop in current SP.
View 7 Replies View RelatedFilling a DataTable from SqlQuery : If SqlQuery returns some null values problem ocurrs with DataTable.
Is it possible using DataTable with some null values in it?
Thanks
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 2 Replies View RelatedI 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
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 1 Replies View RelatedHi,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 3 Replies View RelatedI 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 6 Replies View RelatedHello,I am trying to create a new table in SQL Server based on an excel sheet someone uploads to my site (ie No DTS, and I don't know the field names). How can I easily do that? Can I make a sql table based on a DataTable without going row-by-row? Cause then I could go excel to datatable to sql table.Thanks a bunch,
View 3 Replies View RelatedAll,
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
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.
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)
Is there a way to structure a query to update an existing table record if it already exists, otherwise insert a new record into that table?
View 2 Replies View RelatedHi 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?
I have Three tables Student,Daily_Attendance_Master and Daily_Attendence_Details.
I want to run sql of insert or update of student attendence(apsent or present) in Daily_Attendence_Details based on Daily_Attendance_Master_Id and Student_Id(from one roll number to another).
If Both are present in table Daily_Attendence_Details then i want to run Updating of attendance from one roll number to another roll number in Daily_Attendence_Details on the basis of Daily_Attendence_Details_Id
And if both or any one is not present i want to run insert of student attendense from one roll number to another roll number in Daily_Attendence_Details.
I give below the structure of three tables Student,Daily_Attendance_Master and Daily_Attendance_Details.
Student:-
CREATE TABLE [dbo].[Student](
[Student_Id] [bigint] IDENTITY(1,1) NOT NULL,
[Course_Id] [smallint] NULL,
[Class_Id] [int] NULL,
[Batch_Year] [varchar](20) NULL,
[Student_Initials] [varchar](20) NULL,
[Code] ....
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
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 13 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'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?
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
Is it possible to allow a user to insert and update data in a table but prevent them from performing deletes against that same table? For auditing purposes I need to prevent the end users from being able to delete data.
View 1 Replies View RelatedHello,
I created this DataTable, add rows of data to it, and then display the data on to a form via a repeater.
Dim ds As DataSet = New DataSetDim dtTableName As DataTable = New DataTable("dtTableName") dtTableName.Columns.Add("Description")dtTableName.Columns.Add("ItemNumber")dtTableName.Columns.Add("Quantity")dtTableName.Columns.Add("Price")ds.Tables.Add(dtTableName)
What I need to do now is create a table in SQL Server and update the database with the data I've collect in my dataset. How do I bind and update this data to a sql server table?
Thanks!James
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