Data Update
Jul 20, 2005
Hello all,
I'm new in this newsgroup and I apologise if my question has already done.
Let me knwo if FAQ is avaible somewhere.
I use msSQL server to manage data for a B2B portal.
Orginal data comes from as400 db2.
I need to update msSQL data from db2 source.
I used MetaDataService executed by SQL server Agent, but dosn't work as
excepted 'cause I need to delete all row in a table and then copy all new
rows, this works fine but what happen if an user is quering data while sql
agent starts?
I need to make data update very often so I need other solution.
The update procedure must follow these rules:
1- check diffrents between a table in DB2(source) and the same table on
msSQL server
2- update my msSQL table if some diffrents was found.
I'm a beginner with msSQL server, any advices are welcomed, I need a startin
point.
please help.
thank you all
kindly regards
Alberto
View 1 Replies
ADVERTISEMENT
Dec 12, 2014
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
View 5 Replies
View Related
Oct 18, 2006
When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
View 7 Replies
View Related
Aug 6, 2015
I want to make data changes in read_only database , that's why i must set database read_write. While database is at read_write mode, i want to be sure that no one makes change in database.
For this aim, i write the code below, but i suspect that after setting the database read_write, till the setting database
single_user ,is it possible get DML script from another user. Is the code below enough for this operation. Or is there another way?
Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.
The code;
use master
alter database xxx set read_write
with rollback immediate
alter database xxx set single_user
with rollback immediate
use xxx
update tablexxx set columnxxx=yyy
use master
alter database xxx set read_only
with rollback immediate
alter database xxx set multi_user
with rollback immediate
View 5 Replies
View Related
Sep 28, 2006
Hi,First post so apologies if this sounds a bit confusing!!I'm trying to run the following update. On a weekly basis i want toinsert all the active users ids from a users table into a timesheetstable along with the last day of the week and a submitted flag set to0. I plan then on creating a schduled job so the script runs weekly.The 3 queries i plan to use are below.Insert statement:INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)VALUES ('user ids', 'week end date', '0')Get User Ids:SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'Get last date of the weekSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)I'm having trouble combing them as i'm pretty new to this. Is the bestapproach to use a cursor?If you need anymore info let me know. Thanks in advance.
View 4 Replies
View Related
May 10, 2007
How do u achieve this -- While SSIS Data Load Execution itself?
Update Col B with Col A value if Col B is NULL in the Data File?
View 1 Replies
View Related
Mar 10, 2008
i am really in need of help. i have a text file consiting of some data.i want to update my database from that text file periodically say 12 hours.the text file is being updated by another server program in every 12 hours can any one help me in this case? i am lost for this scenario?? help me please.....
View 1 Replies
View Related
Jun 5, 2014
I got this script but all I get are the errors:
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'PartNrFabrikant'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'omschrijving'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'verkoopprijs'.
Msg 116, Level 16, State 1, Line 13
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
script:
UPDATE artikelen
SET omschrijving = [Hofstede].[dbo].[sparepartsupdate].[omschrijving] ,
verkoopprijs = [Hofstede].[dbo].[sparepartsupdate].[verkoopprijs] ,
gewijzigd = getDate()
[code]...
View 8 Replies
View Related
Nov 4, 2015
finding one set of data within another set of data for update,so if within column 1 it finds the results from another select on another table, it updates a different column with the result.
Example
SELECT Title
from H..Import_Table
returns
'PRN - Concord'
'PRN - San Jose'
'SLP - PRN - San Jose'
'San Jose - PT - PRN'
[code]....
View 1 Replies
View Related
Jul 22, 2014
I have created a table in a database for a football that I want to update as the season progresses. Is there any way I can add the new data to the old data in my columns through SQL rather than searching for the old data, doing the maths in my head and doing a simple update query? I know it doesn't save much extra time but I can get awful lazy when it comes to extra maths!
Example would be:
Player Minutes
Adam 287
Adam has played 287 minutes prior to the most recent match in which he played 67 more. I would like to know if its possible to do an update where I can just simply add 67 onto the existing numbers with one query rather than find Adam's minutes, use a calculator, do a table update.
View 4 Replies
View Related
Aug 15, 2007
First_Name Last_Name Contact_Name---------- --------- ------------NULL NULL YVES MERCIERNULL NULL YUN CHUNGNULL NULL YUKITO YAMASAKINULL NULL YUICHI MOCHIZUKINULL NULL YUGUNDERA BHIDE
My goal is to split the full contact name into first and last as well. Because that is what is in the database and I want to keep it consistent for the few thousand records missing it.
Do I update this using a stored Procedure?
How would it be formed?
CREATE PROCEDURE dbo.ap_Insert_FirstLastName_WhereNull
SELECT * FROM Booth_Visitors_Data WHERE First_Name IS NULL And Last_Name Is NULL And Contact_Name IS NOT NULLORDER BY Contact_Name Desc
Update Booth_Visitors_Data Set
??
Or is this impossible with a stored procedure?
SHould I wirte a .aspx page to do it?
Or can I do it all in code-behind??Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click Dim con As New SqlClient.SqlConnection
con.ConnectionString = "Data Source=10.10.10.10;Initial Catalog=Leads;Persist Security Info=True;User ID=xxxxx;Password=xxxxx"Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandText = "SELECT * FROM(Booth_Visitors_Data) WHERE(First_Name Is NULL And Last_Name Is NULL And Contact_Name Is Not NULL) ORDER BY Contact_Name Desc"
myCommand.BeginExecuteReader()
myCommand.Connection = con
con.Open()
con.Close()
End Sub
If I can do it in code-behind, how does it look in modern .net code as opposed to the old classic asp way?
[Edited by Dinakar Nethi]
Masked userid/pwd in connection string.
[/Edit]
View 6 Replies
View Related
Aug 4, 2006
I am a SQL Server Express and VS 2005 rookie. I currently have a form with a datagrid on it, mapped to a binding source with everything basically done for me using a wizard. I want it so that the user can enter data into the grid, and the data is stored in the database. However, everytime i click save, it doesn't actually save the data, so that everytime you execute the application in debug again, the data is gone. I have the End Edit and Update statements correct. What is wrong!
View 2 Replies
View Related
Sep 29, 2005
I thought there would be an easy way to do this ... is there?
View 7 Replies
View Related
May 27, 2007
Please Help!!
I have been trying to think of a solution to a problem i have and still not sure how to go about it.
I have a database filled with message data that is sent by one user to another.What i am trying to do is have it so (if users are online) they are notified - by a popup or something - that a new message has arrived for them.My idea was to create a trigger on the message table to update a change table with sender and recipient ids and date of change in it.Not a problem so far but what is troubling me is that i want the recipient to automatically query this change table every say 30 seconds to check if new message has arrived with their id.Tryed using ajax update panel and timer but naturally that kept posting back whole page - looks really terriable :( - tryed sqlnotification but as far as i can see that is only changing cached data for current data - not the same issue i think.Is there a way to query db table regualry without the client side knowing? - thinking along lines of how outlook requeries server for new messages and displays popup to say new message but doesnt interupt you if doing something else.Any help would be greatly appreaciated.Thanks
View 12 Replies
View Related
Sep 3, 2007
Hi Everyone,
I develop a asp.net 2.0 site with Sql2005 Database, now i want to publish it on a webhosting service.
Once I copy the DB to app_data folder on webhosting, what i can do to update data from my local sql2000 server?
I want update data daily, may I use SQL replication? How? I can access my sql2005 DB by FTP?
Any ideas?
Thanks a lot.
Regards,
Bordonhos
View 1 Replies
View Related
Feb 5, 2008
I am currently wrapping up a website upgrade for a client and I am working on a development server/database. The development server/database will become the live version. When the upgrade goes live, I will need to update that database with the latest data from specific datatables (no all of them) in the previously live database, but I don't know how to do a bulk refresh of datatables.
Problem: specific datatables (not all datatables) from Database1 need to be updated with the data from Database2. Database1 and Database2 are copies of each other with vast differences in some of the data.
Result: All of the current, up-to-date data needs to reside on Database1.
Solution: Any ideas?
I am using MSSQL 2000 and the databases reside on the same server.
View 1 Replies
View Related
Jun 18, 2008
I am attempting to update contact data that is displayed in a form. Unfortunately the followiing code that runs off the button click event does not seem to work or I am misssing something to make it fire. I get no errors but the data does not get updated. What am I missing? Thanks.Private Sub btn_Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Submit.Click
Dim strConnection As StringDim cn As SqlConnection
Dim sql As String
strConnection = ConfigurationSettings.AppSettings("ConnectionString")cn = New SqlConnection(strConnection)
sql = "UPDATE vw_BS_IssuerContacts SET ContactLastName = @ContactLastName, ContactFirstName = @ContactFirstName, ContactTitle = @ContactTitle, ContactEmail = @ContactEmail, ContactPhone = @ContactPhone, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, Country = @Country, ZipCode = @zipCode, AccessLevel = @AccessLevel WHERE IssuerCode = @IssuerCode"Dim UpdateCommand As New SqlCommand(sql, cn)
UpdateCommand.Connection = cn
UpdateCommand.CommandText = sqlUpdateCommand.Parameters.Add(New SqlParameter("@IssuerCode", Lbl_IssuerCode.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@ContactLastName", txt_Lname.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ContactFirstName", txt_Fname.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@ContactTitle", txt_title.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ContactEmail", txt_Email.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@ContactPhone", txt_Phone.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Address1", txt_Address.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@Address2", txt_Address2.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Address3", txt_City.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@Country", txt_Country.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ZipCode", txt_zipCode.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@AccessLevel", Lbl_currAccessLevel.Text))
'UpdateCommand.Parameters.Add(New SqlParameter("@AccessLevel", SqlDbType.Char, 2).Value = txt_CurrAccessLevelSelectedValue))UpdateCommand.Parameters.Add(New SqlParameter("@UserName", txt_Username.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@infopw", txt_pw.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Company", txt_IssName.Text))
'do the update
cn.Open()
UpdateCommand.ExecuteNonQuery()
cn.Close()
End Sub
View 1 Replies
View Related
Mar 22, 2004
Hi,
I develop ASP.NET applications and use MS SQL 2000 Enterprise.
Each customer has one database and one custom design application. And, there is a gateway and a central database to get data from all databases.
Supposed there are 10 DBs. I want the data in those 10 databases updating to the central database directly.
So, the gateway can gather all data of 10 DBs from a central DB.
Now, my question is how should I do it in MS SQL 2000? Can I do it with a stored procedure (SP)? If so, how does the SP like?
Or, SP can only perform on it's own DB, but not execute across DBs? If so, how can I do that?
Thanks for advice
View 1 Replies
View Related
Apr 12, 2005
Hi!
I have quite strange problem, that I haven't seen before.
When I use update command:
UPDATE categorys SET banner_valid= '0', section_id= '1', main_cat_default= '0', banner= '', b_link= '', external_text= 'NÄ?kotnes parks ', in_frontpage= '1', name = '100. pants' WHERE (id = 130)
Then the field EXTERNAL_TEXT should have value NÄ?kotnes parks but instead of this it makes it Nakotnes parks.
I changed collation to Latvian and this did not work.
But!!! When I open Enterprise manager and just type in NÄ?kotnes parks and save it then it is ok, but it does not work with Update/add script
Any help or ideas???
View 3 Replies
View Related
Dec 15, 2005
I'm trying to update a single record in a table using data from a single record in another table. For the life of me I can't figure this out. I'm trying to avoid having to write a bunch of +=" string stuff in C#. Is there any way to do this with just SQL?Thanks in advance.
View 2 Replies
View Related
Mar 16, 2006
Hello All,
I have two tables T1 and T2 with the same data structure. I need to compare T1 with T2 for all columns and update T2 for deleted, inserted and updated rows. How can I do this?
View 4 Replies
View Related
Jun 28, 2006
Dear All,I am facing a problem from last one month. I have around 100 of client user of my database. I am using VB application for the client users. When they submit the information most of the time the information submits properly. But twice or thrice a day no one can submit the information properly. It remains for more then 15 mins and it will sort out automatically. What might be the problem. Please suggest
View 3 Replies
View Related
Sep 18, 2007
My DTS package transfers data mostly codes from temp to master table. The master table has 10 description fields for each of the codes. These description fields are then populated using update statements joined with 10 description tables. However, the update process is so inefficient since a single update statement takes several hours to finish.
The second option is to populate description fields as codes are transferred from temp to master table using 10 left outer joins with description tables. But the result was unexpected. It's giving me more than twice as many records as there are in the temp table.
What would be the best (efficient) approach for this situation? Would greately appreciate any help/thoughts.
thanks.
View 2 Replies
View Related
Apr 11, 2008
I want to add to a column in one table the data in another. I have tried different update statements and none work. My update statement currently looks like this:
update t1
set t1.amtYTD = t1.amtYTD + t2.Total
from #ProgramData t1 join vw_MER_Reclass t2 on (t1.ProjId = t2.ProjId and t1.task = t2.task and t1.acct = t2.acct)
I need to take the amtYTD in table1, and add to it the value of Total in table 2.
Any help would be appreciated!
View 5 Replies
View Related
Nov 20, 2013
The query below brings up a set of data below that. All items are valued at $9.99. If a row has any information in the column "ShadowOf", I want to update that row. It should take the price from the row where the contents of shadowof appear & add 2%. To give an example 'GreenApples' is a shadow of 'BoxOfApples'. 'BoxOfApples' is valued at $9.99, so I want to make 'GreenApples' $10.19. How would I do this?
DECLARE @tmpTable TABLE(ID NVARCHAR(50), BuyDotComPrice DECIMAL(18,2), ShadowOf NVARCHAR(50), CompanyID INT);
INSERT INTO @tmpTable VALUES
('BoxOfApples', 9.99, '', 344),
('GreenApples', 9.99, 'BoxOfApples', 344),
('GalaApples', 9.99, 'BoxOfApples', 344),
('CaseOfSoda', 9.99, '12Sodas', 344),
('12Sodas', 9.99, '', 344),
('PackageOfSoda', 9.99, '12Sodas', 344);
[code]....
View 3 Replies
View Related
Feb 4, 2015
Basically without going into too much detail, our company gets databases arriving and put onto our systems which have been made my other organizations with no guarantee of what the primary key is or if this is one at all.
I should probably give my main problem in an example for clarity:
Currently I have a .csv file full of data that needs to be put into say TableA. However I do not know if TableA has a primary key or not, or if the file that needs to be inserted into TableA contains duplicate data. I have the importer sorted that does this if you ignore the problem of duplicate data, however what I would like is an MS SQL query that does the following (but I cannot figure it out):
Assuming we are reading through the file line-by-line and a check is performed each time:
1.If there is a line with a primary key in the file that matches a primary key in TableA in the database update that row in the database with the line in the file.
2. If there is no primary key on the table and there is an exact data match between the line in the file and a row in the database then update it.
3. If neither 1 or 2 are successful then just insert the data.
Obviously the potential lack of a PK here makes things a lot more convoluted.
View 1 Replies
View Related
Feb 3, 2006
Hi,
I am not able to modify(add rows into) a table present in sql server from my vb.net application. I am using sql server 2005 enterprise edition and vs.net 2003. are they compatible?
thanks for your help..
View 5 Replies
View Related
May 13, 2007
Hi all, have a pretty simple query. but am new to workin in multiple tables. This statement shows
Items in table1 that exists in table2 that match a filter.
I would like to change this query to update the TOBEDELETED column to 'F' based on the same column and filter match.
SELECT ITEMS.ITEMNO, ITEMS.TOBEDELETED
FROM ITEMS
INNER JOIN QLOG
ON ITEMS.ITEMNO=QLOG.SOURCEID
WHERE QLOG.SOURCE= 'ITEMS'
Thanks all in advance
View 4 Replies
View Related
Jul 20, 2005
Hello Everybody!I have a POLINE table on a SQL Server 2000 DB. Before I update therecord I need to check that either field, STORELOC or WONUM has dataon it. If both fields are NULL I would like to send a message lettingthe user know that either fields needs data before they can save therecord. If any of the fields have data then, it is OK to save therecord.Could you please let me know how to accomplish this? An example willbe really helpful, I can do this in Access but I do not know how to doit in SQLServer. I was thinking using trigger but there are not reallygood examples.Thanks in Advance!Martin
View 1 Replies
View Related
Jul 20, 2005
Hi all.If my table looks like the followingEMPLID NAME BU SAL ELIG_CONFIG11001 Tom 10 500001002 Sarah 10 490001003 John 20 450001005 Jane 10 67000Now I would like to populate ELIOG_CONFIG with the value that is inBU. How is this done recursively so that Tom's ELIG-CONFIG1 = 10 andJohn's ELIG_CONFIG1 = 20 etc.EMPLID NAME BU SAL ELIG_CONFIG11001 Tom 10 50000 101002 Sarah 10 49000 101003 John 20 45000 201005 Jane 10 67000 10update PS_JOB set ELIG_CONFIG1 = (select BU from PS_JOB where.........)How is this done recursively thorugh the whole table?Does this make sense. Assume for example that EMPLID is KEY.John
View 2 Replies
View Related
Jul 20, 2005
Hi,how can i update the data in a field like that:organizationamesurenamezipcityI want to update the content from "organization"into "eworganization" followed by "amesurenamezipcity"yoursmarvin
View 1 Replies
View Related
Jan 21, 2008
I have to Load Contacts data from different systems into a single SQL Server 2005 table, the scenario is the following :
I have three systems System 1,System 2 and System 3
Step 1 Load Data From System 1
Step 2 Load Data from System 2 and if there are matching contacts with System 1, then match their details, keep System 1 details
Step 3 Load Data from System 3 and if there are matching contacts with System 2, then match their details and if different, flag them, do the same with System details.
The unique ids in the three systems are not the same so only way to match is to do a concatenation of name + address + zip code
Is there any other efficient way to do this...
Please advise
View 3 Replies
View Related
Oct 16, 2007
Hi,
Is there any way to restore the old data after changes made to the table.
Suppose i run a query Update CustomerDetails set customerName='Michel' and i forgot to mention the CustomerID whom name should be changed to Michel,in that case in all the CustomerName will update by Michel Name.
So,How can i revert back the changes made to the table?
Thanks in advance.
Regards,
- Hitesh Kumar
View 3 Replies
View Related
