Can someone help me with the following SQL and help me write it in an
OPENQUERY format. I am running the following code from a SQL Server 7
box, trying to update a table in an Oracle Linked Server. The code
runs fine, except it takes almost an hour to complete. I know if I run
via OPENQUERY,I can get the same done in much less time.
Some of the relevant information is as follows:
ORACLE_HBCPRD04 is a linked Oracle Server.
SITEADDRESS is a table in Oracle
#SiteAddress_New is a table in SQL Server.
UPDATE ORACLE_HBCPRD04...SITEADDRESS
SET
CUST_ADDR1 = CASE WHEN SiteAddress_New.CUST_ADDR1 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR1 END,
CUST_ADDR2 = CASE WHEN SiteAddress_New.CUST_ADDR2 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR2 END ,
CUST_ADDR3 = CASE WHEN SiteAddress_New.CUST_ADDR3 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR3 END,
CUST_ADDR4 = CASE WHEN SiteAddress_New.CUST_ADDR4 = '' THEN NULL
ELSE SiteAddress_New.CUST_ADDR4 END ,
CTY_NM = CASE WHEN SiteAddress_New.CTY_NM = '' THEN NULL ELSE
SiteAddress_New.CTY_NM END,
ST_ABBR = CASE WHEN SiteAddress_New.ST_ABBR = '' THEN NULL ELSE
SiteAddress_New.ST_ABBR END,
POST_CD = CASE WHEN SiteAddress_New.POST_CD = '' THEN NULL ELSE
SiteAddress_New.POST_CD END,
CNTY_NM = CASE WHEN SiteAddress_New.CNTY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTY_NM END,
CNTRY_NM = CASE WHEN SiteAddress_New.CNTRY_NM = '' THEN NULL ELSE
SiteAddress_New.CNTRY_NM END,
ADDR_STAT = NULL ,
LAST_UPDATE_DATE = SiteAddress_New.LAST_UPDATE_DATE
FROM
ORACLE_HBCPRD04...SITEADDRESS SiteAddress INNER JOIN
#SiteAddress_New SiteAddress_New ON
SiteAddress.LEGACY_ADDR_ID = SiteAddress_New.LEGACY_ADDR_ID
Currently we are running SQL Server 7 with SP1 installed on an NT box. I need to update a field in a table in Oracle. I setup a linkedserver in SQL Server using the Microsoft OLE DB Provider for ODBC (MSDASQL) and didn't have any problems selecting data from the linked Oracle tables using OPENQUERY. For example: SELECT * FROM OPENQUERY(STATSDEV, "Select * from CR_EXPORT") This query works fine.
However, now I need to update a field in the CR_EXPORT table. So I have written the following query and I tried to run it:
UPDATE OPENQUERY(STATSDEV, "SELECT * FROM CR_EXPORT WHERE REQUEST_NUMBER = 1") SET STATUS = 2
I got the following error message:
Server: Msg 7352, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.
The Oracle table has a unique index, so I think that the issue might be associated with the version of the OLE DB provider or the Service Pack, but I can not find any documentation to support my theory. Any help you could give would be VERY appreciated!
Hi AllI am updating a local table based on inner join between local tableand remote table.Update LocalTableSET Field1 = B.Field1FROM LinkedServer.dbname.dbo.RemoteTable BINNER JOIN LocalTable AON B.Field2 = A.Field2AND B.Field3 = A.Field3This query takes 18 minutes to run.I am hoping to speed up the process by writing in OPENQUERY syntax.ThanksRS
I have an application that uses a MS SQL 2005 database. When data is changed in certain tables, that data needs to be pushed to a MySQL box. I've added the MySQL server as a linked server in SQL 2k5 and I can delete and insert data with no problem, however when I try to update I get the following error (query included):
Code Snippet
with RemoteTable(r_AccountID, r_Name) as (select AccountID, Name from openquery(RACS_TEST, 'select AccountID, Name from accounts')) update RemoteTable set r_Name = ex_Name from Export_RACS_Accounts join remotetable on r_AccountID = ex_AccountID
OLE DB provider "MSDASQL" for linked server "RACS_TEST" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.". Msg 7343, Level 16, State 4, Line 33 The OLE DB provider "MSDASQL" for linked server "RACS_TEST" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
I need to update an Oracle table from SQL Server. I am trying to use Openquery Update statement. I need to pass a integer as a parameter. I will be updating a date field and a status field.
This is the gist of what I need to do in a stored procedure
DECLARE   @ID1      INT,       @SQL1      VARCHAR(8000),       @STATUS    VARCHAR(10),       @DATE      DATETIME; SET      @ID1 = 350719; SET      @STATUS = 'COMPLETED'; SET      @DATE = GETDATE(); SELECT   @ID1; SELECT @SQL1 = 'UPDATE OPENQUERY(NGDEV2_LINK2, ''select DM_IMPORT_STATUS, DM_IMPORT_DATE FROM NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ' + CAST(@ID1 as nvarchar(30)) + ''')' SET DM_IMPORT__STATUS = @STATUS, DM_IMPORT_DATE = @DATE; EXEC (@SQL1);
UPDATE OPENQUERY(LINKEDSERVER, 'SELECT START_ORDER_NO FROM "OECTLFIL" WHERE "FILE_KEY" = 1') SET START_ORDER_NO = 0
----The START_ORDER_NO field contains a 48
SET START_ORDER_NO = 1~9
----The START_ORDER_NO field contains 49~57 respectively.
SET START_ORDER_NO = 10 ---The START_ORDER_NO field contains 12337 SET START_ORDER_NO = 11 ---The START_ORDER_NO field contains 12593 SET START_ORDER_NO = 12 ---The START_ORDER_NO field contains 12849
incrementing by 256 as I increase the value passed...
ASCII 48-57 are the characters 0-9. The string '10' consists of the two bytes with the values 49 (0x31) and 48 (0x30). It is being viewed in reverse byte order as the value 0x3031 which equals 12337 (48*256 + 49).
The LinkedServer is Pervasive SQL 2000i using 'OLE DB Provider for ODBC'
The START_ORDER_NO field is a Numeric(8,0)
I'm thinking some kind of Unicode, or translation or code page issue, but I haven't had any luck yet.
I'm not sure how difficult this is, I don't think I'm a neophyte but I'm feeling like one...
Server A - Oracle 10g Database Server B - SQL2005 Client PC - Sql Express
Server A holds all data. I am using a linked server to connect server A and B. I use a set of stored procedures containing the openquery() syntax to get data from Server A to Server B. These stored procedures run every 20 minutes. I then create a publisher on Server B. I subscribe from the client PC to publisher to get data down from Server B to client(Download only subscription).
When I fire up the stored procedures and attempt to replicate, everything works fine. It appears after about 4-5 hours of the stored procedures running replication begins to hang more and and more until eventually it hangs for about 10 minutes and I recieve the following error:
Command attempted: {call sp_MSreleasemakegenerationapplock} Error messages: The merge process was unable to create a new generation at the 'Publisher'. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994)
I'm not sure if there is an error with openquery() locking? There is some locking activity going on with the TempDB and and Server B database. I've also come across some threads talking about the agent profiles. I'm very new to replication and very confused by all of the options in the agent profiles. Any help would be greatly appreciated!
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process
This is my curren code for updaing ESN number. But this is incorrect. <asp:SqlDataSource ID="ESNTrackingDataSource" UpdateCommand="UPDATE [ESNTracking] SET [EsnNumber] = @EsnNumber WHERE [EsnTrackingId] = @EsnTrackingId" OnInit="ESNTrackingDataSource_Init" OnUpdating="ESNTrackingDataSource_Updating" OnUpdated="ESNTrackingDataSource_Updated" runat="server"> <UpdateParameters> <asp:Parameter Type="String" Name="EsnNumber"></asp:Parameter> </UpdateParameters></asp:SqlDataSource> this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId. This is my query that returns the ones are not assigned to and Asset SELECT DISTINCT EsnId, EsnNumberFROM dbo.ESNTrackingWHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))
I have 2 database tables with column "city". When I update one of these two I want to be updated all the records in the other table. For example: In table "a", column "city" contains the record "NY"; in table "b", column "city" also contains the record "NY" several times. When I change "NY" to "New York" in table "a" I want that also in table to be changed all the records. The condition is not to use foreign key, but just the text of the records. Any help would be appreciated,thanks.
I have an package that goes out and downloads information from an Informix Database. This Information needs to be replicated in another table. However I cant just delete and insert the information into the table, There are a number of clients updating from this table once a minute. How can I make SQL Serve 2000 update the information based on another sql table ( Sort of like a local replication?). Is there a simply query I can use or do I need to set up another packeage stored procedure. Right now the information is placed in a tempory table, the actual table needing to be updated is cleared of its data, and the information is moved. However this causes latency issues in my program. Also, since the Infromix query can take up to 30 seconds to complete, this leaves some of my users retrieving blank data.
I want to update 2 tables in one sp as the parameters are the same. Here's my sp and I don't get any syntax errors in Ent manager, but the error 'Incorrect syntax near 'spRB_CancelFacMenuBooking' when I run it.
AS BEGIN UPDATE tblRB_FacilitiesBookings SET FB_Cancelled =@strCancelled, FB_CancelledDate=@strCancelledDate, FB_CancelledBy=@strCancelledBy WHERE FB_BookingNo = @strBookingNo END
BEGIN UPDATE tblRB_MenuBookings SET MB_Cancelled =@strCancelled, MB_CancelledDate=@strCancelledDate, MB_CancelledBy=@strCancelledBy WHERE MB_BookingNo = @strBookingNo END GO
hi i have two table and would like to update a one of them from the other. i have to fill the column "num" of the CLIENT table , from the TAB1 table. with the condition CLIENT.id = TAB1.id UPDATE [client] INNER JOIN nom_tab1 ON [client].id = tab1.id SET [client].num = [tab].[num]; but it didn't worked for me ,can u give me a hand ?
Hi, I have a problem with adding new rows to a table in access ADP file. Most of my tables i have created using SQL Server Management Studio and i cant edit those with ADP When i create table using ADP project file I can easly edit add delete rows the way i want.The problem is that most of them are already created using SQL Server Management Studio so i cant change them in ADP. i was thinking that the properties are different when its created by adp project but they loook the same. Dont anybody knows what to do to be able to edit tables in adp.
<----------I have 2 tables are: 'customers (parent)' and 'open_ac (child)'
<--------I have tried to insert and update data into sql database by using textboxes (don't use datagrid)
<--------My tables details are below
<-------this table uses for keeping user data
customers fields:
Column name type length
Description
cu_id
int 4 Primary key Identifiers
cu_fname
nvarchar 20 allow null first name
cu_lname
nvarchar 40 allow null last name
cu_nat
nvarchar
20 allow null nationality
cu_add
nvarchar
40 allow null address
cu_wplace
nvarchar
40 allow null workplace
cu_tel
nvarchar 10 allow null telephone
cu_fax
nvarchar 10 allow null fax
cu_email
nvarchar 10 allow null email
<----the open_ac uses for keeping register date/time of customers
open_ac fields:
Column name type length Description
cu_id
int 4 link key
op_date date/time 8 register date
<----------my code
Imports System.Data.SqlClient
Public Class cus_reg Inherits System.Web.UI.Page Dim DS As DataSet Dim iRec As Integer 'Current Record Dim m_Error As String = ""
Public Property MyError() As String Get Return m_Error End Get Set(ByVal Value As String) m_Error = Value If Trim(Value) = "" Then
Label3.Visible = False Else
Label3.Text = Value
Label3.Visible = True End If End Set End Property
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not Page.IsPostBack Then Dim C1 As New MISSQL 'DS = C1.GetDataset("select * from customers;select * from open_ac;select * from accounts") DS = C1.GetDataset("select * from customers;select * from open_ac")
Me.MyDataBind() Else DS = Session("data") iRec = ViewState("iRec") End If Me.MyError = "" End Sub
Public Function BindField(ByVal FieldName As String) As String Dim DT As DataTable = DS.Tables(0) Return DT.Rows(iRec)(FieldName) & "" End Function Public Sub MyDataBind() Label1.Text = "Record : " & iRec + 1 & " of " & DS.Tables(0).Rows.Count txtcu_id.DataBind() txtcu_fname.DataBind() txtcu_lname.DataBind() txtcu_add.DataBind() txtcu_occ.DataBind() txtcu_wplace.DataBind() txtcu_nat.DataBind() txtcu_tel.DataBind() txtcu_fax.DataBind() txtcu_email.DataBind() End Sub
Here is update code
Private Sub bUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bUpdate.Click Dim DT As DataTable = DS.Tables(0) Dim DR As DataRow = DT.Rows(iRec) 'Dim DR1 As DataRow = DT1.Rows(iRec)
If DR.RowState = DataRowState.Added Then If txtcu_id.Text.Trim = "" Then
Me.MyError = "please enter your id"
Exit Sub End If DR("cu_id") = txtcu_id.Text End If
If txtcu_fname.Text.Trim = "" Then Me.MyError = "please enter your name" Exit Sub Else
DR("cu_fname") = txtcu_fname.Text End If
If txtcu_lname.Text.Trim = "" Then Me.MyError = "please enter your last name" Exit Sub Else
DR("cu_lname") = txtcu_lname.Text End If
If txtcu_add.Text.Trim = "" Then Me.MyError = "please enter your address" Exit Sub Else
DR("cu_add") = txtcu_add.Text End If
If txtcu_occ.Text.Trim = "" Then Me.MyError = "please enter your occupation" Exit Sub Else
DR("cu_occ") = txtcu_occ.Text End If
If txtcu_wplace.Text.Trim = "" Then Me.MyError = "please enter your workplace" Exit Sub Else
DR("cu_wplace") = txtcu_wplace.Text End If
If txtcu_nat.Text.Trim = "" Then Me.MyError = "Please enter your nationality" Exit Sub Else
DR("cu_nat") = txtcu_nat.Text End If
If txtcu_tel.Text.Trim = "" Then
DR("cu_tel") = DBNull.Value Else
DR("cu_tel") = txtcu_tel.Text End If
If txtcu_tel.Text.Trim = "" Then
DR("cu_fax") = DBNull.Value Else
DR("cu_fax") = txtcu_fax.Text End If
If txtcu_email.Text.Trim = "" Then
DR("cu_email") = DBNull.Value Else
DR("cu_email") = txtcu_email.Text End If
Dim Strsql As String If DR.RowState = DataRowState.Added Then Strsql = "insert into customers (cu_id,cu_fname,cu_lname,cu_add,cu_occ,cu_wplace,cu_nat,cu_tel,cu_fax,cu_email) values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10)" Else Strsql = "update customers set cu_fname=@P2,cu_lname=@P3,cu_add=@P4,cu_occ=@P5,cu_wplace=@P6,cu_nat=@P7,cu_tel=@P8,cu_fax=@P9,cu_email=@P10 where cu_id =@P1" End If Dim C1 As New MISSQL Dim cmd As SqlCommand = C1.CreateCommand(Strsql) C1.CreateParam(cmd, "ITTTTTTTTT")
DR.AcceptChanges() Else Me.MyError = "Can not register" End If <---------code above in this sub it can update only customers tables and when I tried to coding below<------------it alerts can not update Dim DT1 As DataTable = DS.Tables(1) Dim DR1 As DataRow = DT1.Rows(iRec) If DR1.RowState = DataRowState.Added Then If txtcu_id.Text.Trim = "" Then
Me.MyError = "Please enter id"
Exit Sub End If DR1("cu_id") = txtcu_id.Text End If If Label2.Text.Trim = "" Then
DR1("op_date") = Label2.Text End If
Dim StrSql1 As String If DR1.RowState = DataRowState.Deleted Then StrSql1 = "insert into open_ac (cu_id,op_date) values (@P13,@P14)" Else StrSql1 = "update open_ac set op_date=@P14 where cu_id=@P13" End If Dim C2 As New MISSQL Dim cmd2 As SqlCommand = C2.CreateCommand(StrSql1) C2.CreateParam(cmd2, "ID")
cmd2.Parameters("@P1").Value = DR1("cu_id")
cmd2.Parameters("@P2").Value = DR1("op_date")
Dim Y1 As Integer = C2.Execute(cmd2) If Y1 > 0 Then
DR1.AcceptChanges() Else Me.MyError = "Can not register" End If End Sub End Class
<------this is class I use for connecting to database and call parameters....
MISSQL class
Imports System.Data.SqlClient Public Class MISSQL Dim PV As String = "Server=web_proj;uid=sa;pwd=sqldb;" Dim m_Database As String = "c1_itc" Public Strcon As String Public Sub New() Strcon = PV & "database=" & m_Database End Sub Public Sub New(ByVal DBName As String) m_Database = DBName Strcon = PV & "database=" & m_Database End Sub Public Property Database() As String Get Return m_Database End Get Set(ByVal Value As String) m_Database = Value Strcon = PV & "database=" & m_Database End Set End Property
Public Function GetDataset(ByVal Strsql As String, _ Optional ByVal DatasetName As String = "Dataset1", _ Optional ByVal TableName As String = "Table") As DataSet Dim DA As New SqlDataAdapter(Strsql, Strcon) Dim DS As New DataSet(DatasetName) Try DA.Fill(DS, TableName) Catch x1 As Exception
Err.Raise(60002, , x1.Message) End Try Return DS End Function
Public Function GetDataTable(ByVal Strsql As String, _ Optional ByVal TableName As String = "Table") As DataTable Dim DA As New SqlDataAdapter(Strsql, Strcon) Dim DT As New DataTable(TableName) Try DA.Fill(DT) Catch x1 As Exception
Err.Raise(60002, , x1.Message) End Try Return DT End Function
Public Function CreateCommand(ByVal Strsql As String) As SqlCommand Dim cmd As New SqlCommand(Strsql) Return cmd End Function
Public Function Execute(ByVal Strsql As String) As Integer Dim cmd As New SqlCommand(Strsql) Dim X As Integer = Me.Execute(cmd) Return X End Function
Public Function Execute(ByRef Cmd As SqlCommand) As Integer Dim Cn As New SqlConnection(Strcon) Cmd.Connection = Cn Dim X As Integer Try Cn.Open() X = Cmd.ExecuteNonQuery() Catch X = -1 Finally Cn.Close() End Try Return X End Function
Public Sub CreateParam(ByRef Cmd As SqlCommand, ByVal StrType As String) 'T:Text, M:Memo, Y:Currency, D:Datetime, I:Integer, S:Single, B:Boolean, P: Picture Dim i As Integer Dim j As String For i = 1 To Len(StrType) j = UCase(Mid(StrType, i, 1)) Dim P1 As New SqlParameter
P1.ParameterName = "@P" & i Select Case j
Case "T"
P1.SqlDbType = SqlDbType.NVarChar
Case "M"
P1.SqlDbType = SqlDbType.Text
Case "Y"
P1.SqlDbType = SqlDbType.Money
Case "D"
P1.SqlDbType = SqlDbType.DateTime
Case "I"
P1.SqlDbType = SqlDbType.Int
Case "S"
P1.SqlDbType = SqlDbType.Decimal
Case "B"
P1.SqlDbType = SqlDbType.Bit
Case "P"
P1.SqlDbType = SqlDbType.Image End Select
Cmd.Parameters.Add(P1) Next End Sub End Class
<-------Thank you in advance<-------and Thank you very much for all help
Is there some sort of sql command where a tuple in a table has one of its cells updated depending on the value of a cell from another table. Please I would appreciate some help. Thanks
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?????
hi,does any know how to update two tables with the same statement?is it possible?here is my unsuccessful try:UPDATE A,BSET A.VAL='BLUH1', B.VAL='BLUH2'from #T A, #T2 BWHERE A.LOANID=B.LOANID
is it possible to do something likeUPDATE Table1, Table2 INNER JOIN Table2 ON Table1.ID=Table2.ParentID SET Table1.Name=xxx, Table2.Address=yyyI want to update at same time the Asp.net Users table and a "UsersDetails" table, sharing the same UserID key.
I am trying to update a field within one table with the values from another table. With the criteria that another field in each table are equal. What is the correct way to do this. My syntax is all wrong.
this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId.
This is my query that returns the ones are not assigned to and Asset
SELECT DISTINCT EsnId, EsnNumber FROM dbo.ESNTracking WHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))
I have recently defragged my SQL server using INDEXDEFRAG. Can somebody please tell me how to update the statistics on all the tables? Thanks in advance.
Below is the script that I executed to defrag all the tables in my database if anyone needs this.
/*Perform a 'USE <database name>' to select the database in which to run the script.*/ -- Declare variables SET NOCOUNT ON DECLARE @tablename VARCHAR (128) DECLARE @execstr VARCHAR (255) DECLARE @objectid INT DECLARE @indexid INT DECLARE @frag DECIMAL DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow SELECT @maxfrag = 20.0
-- Declare cursor DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
-- Loop through all the tables in the database FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0 BEGIN -- Do the showcontig of all indexes of the table INSERT INTO #fraglist EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') FETCH NEXT FROM tables INTO @tablename END
-- Close and deallocate the cursor CLOSE tables DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag FROM #fraglist WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor OPEN indexes
-- loop through the indexes FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
I have 2 tables. They both have the same fields [OFO_Code] and [Description]
Table 1 is Called Qualification and Table 2 OFO_Code. The second table is a lookup table. The info in Table 1 's description column in some places does not match that of Table 2.
I am trying to loop through these tables to Update the info in Table 1's description to match the correct description in Table 2.
Their are 1417 items in Table 2 and 77000 items in table 1.
There are NO errors when executing, just no update
Here is my code so far:
Code: Update Qualification SET [Description] = B.Description FROM Qualification A INNER JOIN OFO_code B ON A.OFO_CODE = B.OFO_CODE WHERE A.OFO_Code IN ('" & OFO & "' )"
how to update a temp table with another temp table for example I have #tempdashboard that has info in it.I also have #tempappscount of which I want to insert the info inside it into #tempdashboard...I believe the column in #tempdashboad is DailyAppsInINT,
I did a query that gave me the number of daily apps created. I was told to create #tempappscount and my query to dump into #tempappsaccount. Then dump the info from #tempappsaccount into #tempdashboard.
I have a customers CRM DB that I need to run an update query on, affecting around 14,000 records. The fields used in the entity in question are split across two tables. I need to update a field in one tabled based off of the result of a field in the other table.
So this is what I started with:
USE db1 UPDATE tbl2 SET field1 = '1' WHERE tbl1.CreatedOn < '2013-28-09 00:00:00.000';
This didn't work, SQL complained about being unable to bind tbl1.CreatedOn. I assume because it's in a different table to the one I'm updating.
I attempted a JOIN to the best of my limited SQL knowledge, thinking I could just shove the two tables together and it might be happy.
USE db1 INNER JOIN tbl1 ON tbl2.Id=tbl1.Id; UPDATE tbl2 SET field1 = '1' WHERE tbl1.CreatedOn < '2013-28-09 00:00:00.000';
This also didn't work, complaining of syntax error near 'INNER'
I'm obviously missing something, but IU don't know what it is.
Given the below table relationships, I am trying to update the price of disk by %20 percent that were interpreted by 'Joe Smith'. I took a crack at this, but I do not feel comfortable with it. Do I need to do some type of join or union?
UPDATE Disk SET price = price *.20 WHERE Interpreter.name = ‘Joe Smith;
ans writes "I’m using SQL Server • I’ve got 3 tables: tblOne, tblTwo, tblThree • Using the stored procedure(let call this stored procedure sp_Result) I generate result using all the above tables(tblOne, tblTwo, tblThree). • The problem is, it takes too long to execute(around 15 sec), which is not acceptable as I’m using in the result in the web application • I’m thinking of scheduling to run the above stored procedure to run once every hour and write the result in a new table, tblResult. • now the user only queries in the tblResult tables. Since it is a straight forward query it will take less than 1 sec. Perfect !!! • Now the tblResult already exist, and the user are queries only to this table. • How would I compare the result produced by stored procedure sp_result and the table tblResult? • How would I update tblResult with the produced result? • Would be a good idea to write the result in a new table called temp, then drop the table tblResult and then rename the table temp to tblResult?"
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.?
