T1 is a table of data imported by DTS, it has staff numbers and location names
T2 is a table that contains system data about staff and has a column for the location id
T3 is a table for the location names and their ID's
i am trying to write the sql to update T2 with the new location IDs for staff that have moved location but am not getting what i need.
so far i have :
update
T2
set T2.LocationId =T3.LocationID
from T3
inner join T1
on T3.departmentname = T1.contact_location
the update runs but sets all rows to the same value. am now burried deep in the [I've stared at this too long and cant see the wood for the trees] stage :mad:
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.
We did some "at scale" fuzzy lookup tests today and were rather disappointed with the performance. I'm wanting to know your experience so I can set my performance expectations appropriately.
We were doing a fuzzy lookup against a lookup table with 25 million rows. Each row has 11 columns used in the fuzzy lookup, each between 10-100 chars. We set CopyReferenceTable=0 and MatchIndexOptions=GenerateAndPersistNewIndex and WarmCaches=true. It took about 60 minutes to build that index table, during which, dtexec got up to 4.5GB memory usage. (Is there a way to tell what % of the index table got cached in memory? Memory kept rising as each "Finished building X% of fuzzy index" progress event scrolled by all the way up to 100% progress when it peaked at 4.5GB.) The MaxMemoryUsage setting we left blank so it would use as much as possible on this 64-bit box with 16GB of memory (but only about 4GB was available for SSIS).
After it got done building the index table, it started flowing data through the pipeline. We saw the first buffer of ~9,000 rows get passed from the source to the fuzzy lookup transform. Six hours later it had not finished doing the fuzzy lookup on that first buffer!!! Running profiler showed us it was firing off lots of singelton SQL queries doing lookups as expected. So it was making progress, just very, very slowly.
We had set MinSimilarity=0.45 and Exhaustive=False. Those seemed to be reasonable settings for smaller datasets.
Does that performance seem inline with expectations? Any thoughts to improve performance?
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
I'm building a package to import data from a flat file into a Customer table, and I have set up a Lookup to check if that customer already exists in this table, and if so, perform an Update command instead of the bulk load. I don't expect many updates, if any, this is why i just used the OLE DB Command instead of using a staging table.
I've a bit of a problem executing this within a transaction and having the lock table option set on the SQL Server Destination. Is there anyway I can get Transaction support for this Data Flow working, as I want to be able to rollback a complete file/import if possible.
Hi, I have an example situation that seems like it should have a super easy solution, but my jobs keep failing. Here we go. . .
I have a SQL Server 2005 table as my source in a data flow task. This table contains raw data. We'll call it FACT_Product_Raw - which contains a field called ProductType varchar(1) Let's say that ProductType contains values of "A" or "B" or "C" - or for that matter, some null and garbage values
I have a lookup table, LOV_Product_Types This table contains 3 fields that will transform my raw data table We'll call these fields ProdTypeID smallint, ProdTypeRaw varchar(1) and ProdType smallint It contains pairs such that A = 1, B = 2, and so on.
Here's what I want to do. I want to ADD a field to FACT_Product_Raw that contains the "looked up" value from LOV_Product_Types. Let's say that I want to add the ProdTypeID field to my _Raw table.
I have used the _Raw table as both my source and destination It blows up every time. Help. Thanks, David
I have two records in the source with information ID, RevisionID, Description, Region
There are two lookup files one with ID,Description amd other with ID, Region
I wish to update my two source records with performing lookup with these two files.To get the correct description and region data. How to do this in ssis DFT.
I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.
Source Data Table:
Table Name: Source
SrcID AGE City Date 01 32 London 01-01-2013 02 35 Lagos 02-01-2013 03 36 NY 03-01-2013
Metadata Table:
Table Name:Metadata
MetaID Column_Name Column_type 11 AGE col_integer 22 City col_character 33 Date col_date
Destination table:
The source data to be loaded into the destination table(as shown below):
Hi..This may be silly but i am stuck at this for some time. I have 10000 rows in a table and i want to copy the value of column 1 in all the rows to column 2 ( which contains NULL presently in all the rows ). The two columns are of the same datatype.
Hello all and I look forward to reading your replys. Currently, I'm trying to update a table in my SQL database by writing a script that will go get the field name and correct info, verify it has the correct field by using two other columns and then update the information.
I do not know how to use tell SQL what to update when the field name can change. I've used the following in the past.
UPDATE [Import Table]
SET [Import Table].[???] = [Correction_Table].[Correct_Info]
WHERE [Import Table].[UNIQUE ID] in (select [USO Unique ID] from [Correction_Table])
IN this new instance, I'm reading the Field_Name from the Correction_Table so I don't know what to put in my SET statement.
Please any help or pointers in the right direction would be greatly appreciated.
I have a table Products with their prices, and I have another table Sales, that have the prices of the products but they are null, I need to make an update that update the tables Sales, inserting the prices from the table Products
I need to do an update. Can you tell me if it's possible to update multiple tables in a single statement when joined, as with a select, or this has to be taken care of in seperate statements with the same join? I'm sure this is a simple YES / NO answer.
Here is what I'm using, with the same structure as somewhere else... why do I get 'incorrect syntax near q' on that very first line?
-- Do the update update ccs_quotes q set ccs_quo_user_date#1 = j.completed from "ken-sql-002".hettonhosttestdatabase.dbo.job j inner join ccs_quotes q on left(j.cid,10) = q.ccs_quo_ref_number#2 where completed is not null and ccs_quo_ref_number#2 = 'SHGD40021'
I need to copy data from one column that has a max size of 45 characters toa column that has a max size of 20 characters.What is the syntax to say fill the first 20 characters out of 45 fromcolumn 1 into column 2?jeff--Message posted via http://www.sqlmonster.com
Hi folks. I'm trying to update a single field in a database table with the input from a single text box, but I'm getting "Insert Error: Column name or number of supplied values does not match table definition". I know at least part of the problem is that I haven't specified the name of the table field, but I'm not sure where to do this. The table in question has many fields, but I only want to update one. The submit button is "sendMail", which calls UpdateDB.I'm learning and trying to limit myself to 1 idiotic question per week. Please have mercy. Thanks. Chrisprivate void UpdateDB(string cmd) { // point to connection string in web.config string connectionString = "Data Source=localhost;Initial Catalog=databaseName;Integrated Security=True"; // create connection object; initialize; open System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); try { connection.Open(); // create sqlcommand object and assign connection System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(); command.Connection = connection; command.CommandText = cmd; command.ExecuteNonQuery(); } finally { connection.Close(); } }
I have been trying to create a simple page to update my SQL Express database with some text boxes that are auto filled. If here is the code if you have any help for me it would be great. Partial Class Secured_Default2 Inherits System.Web.UI.PageProtected Sub Accept_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Accept.Click SqlDataSource3.Update() End SubProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load AcceptDate.Text = System.DateTime.Now.ToStringAcceptedBy.Text = Request.ServerVariables.Get("Logon_User") StatusBox.Text = "2"End Sub End Class<%@ Page Language="VB" MasterPageFile="~/Secured/MasterPage.master" AutoEventWireup="false" CodeFile="TEST.aspx.vb" Inherits="Secured_TEST" title="Untitled Page" %> <%@ Import Namespace="System.Data.SqlClient" %><%@ Import Namespace="System.Data" %>
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()
hi, i've just started using sql server 2005 and visual studio, i have made 3 stored procedures, an update, insert and delete. The update one doesn't work but the others do so i'm on the right track with them..
is there anything obvious i am missing in the following update stored procedure? All of the datatypes and names line up with the datatypes/sizes/names in the table..
Code:
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author:<Author,,Name> -- Create date: <Create Date,,> -- Description:<Description,,> -- ============================================= ALTER PROCEDURE [dbo].[spUpdateAddresses] -- Add the parameters for the stored procedure here (@addressID int, @fname varchar(50), @lname varchar(50), @address01 varchar(50), @address02 varchar(50), @suburb varchar(50), @state varchar(50), @postcode char(10), @comments varchar(1250), @phone char(10), @fax char(10), @mobile char(10))
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Update addresses set @fname=fname, @lname=lname, @address01=address01, @address02=address02, @suburb=suburb, @state=state, @postcode=postcode, @comments=comments, @phone=phone, @fax=fax, @mobile=mobile where (@addressID = addresses.addressID)
Hi, wondered if anyone here can help out with an SQL query thats been driving me mad. Currently I do this programmatically in a C# program with a hard coded mapping class but want to put the new codes into a table and run an update query instead.
Basically I want to update all GROUP_CODE entries in the CarModels table with the NEW_CODE values from NewGroupCodes where there is a match between CarModels.GROUP_CODE and NewGroupCodes.OLD_CODE.
Hey all,i have a students_table which i want to do a multiple update on.Update student_tableset grant = 35000where course_id = 2There are lots of students in the student table which have course_id of2.I get this error:Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used as anexpression.The statement has been terminated.Does anyone know how to do a multiple update like this.It seems such asimple thing but im confused*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
They both have the shared id of CallID. I'm trying to write a trigger that when one adds a record to the repository table, I want the CallLog table flagged with 'T'. My existing code is flagging everything thing in the CallLog table that has CallID match from the Repository table... How do I update the single row in the CallLog table? Driving me nuts!
Hello,I have a query that I need help with.there are two tables...Product- ProductId- Property1- Property2- Property3PropertyType- PropertyTypeId- PropertyTypeThere many columns in (Product) that reverence 1 lookup table (PropertyType)In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeIdHow do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3ProductId | Property1 | Property2 | Property3 | PropertyType1 | PropertyType2 | PropertyType3 PropertyType(1) = PropertyType for Property1PropertyType(2) = PropertyType for Property2PropertyType(3) = PropertyType for Property3I hope this makes sence.Thanks in advance.
hi, everyone, When I update a row that does not exist in a table using VBscript and SQL 2003 server, the row is automatically added to the table. Why does this happen? Can somebody help me? Thanks in advance!
I have a simple update/initialization query (set integer column = 0 on all rows) that's been running for over 28 hours. There are just over 27 million rows in the table. In current activity it shows that the transaction is open but it's sleeping, and in locks it shows 1 DB S mode lock, 766 page X mode locks, 1 page U mode lock, and one table X mode lock. Server is 7.0 with 1.7 gig ram. Anyone have any ideas as to why it's taking so long? Table is about 7 gig in size; can't get to it in Enterprise Manager without locking it up...
I was trying to update records of a recordset(ADODB.Recordset) returned from a stored procedure(SQL server 7.0) in ASP file, this stored procedure select records into a temporary table, so the records returned by the SP actually physically are in tempdb database rather than in the user database.
When executing Update statement , I got the error message: Microsoft OLE DB Provider for ODBC Drivers error '80040e37' [Microsoft][ODBC SQL Server Driver][SQL Server]Database name 'Mydatabase' ignored, referencing object in tempdb.
Any ideas/comments would be highly appreciated! Dana Jian dan_jian@hotmail.com
Kairn writes "I have created a table and imported data from another table to it. I need to update the existing data with values from another import but only need specific columns and am assuming a stored procedure is the way to go...IF I create a source table as well as destination(?). I have to compare the value in 6 columns to find the matching record in the destination, then update 5 of the other columns where source=dest. Finally, I need to sum, by row, the value in 3 of the columns and update another column to reflect it. All rows are unique in the destination. The source is a duplicate of them. Basically, the rows are identical aside from the values in the columns I want transferred. The rows must remain unique. I am a newbie and have no idea how to do this. Would you please submit a basic outline and I can fill in the rest?
Thank-you very much for your time and expertise, Kairn"
When I execute following SQL Query Analyzer (SQL 2005) i am getting message 3 times "95 Records effected" in query analyzer message window.
UPDATE dbo.Load_AC_Install SET Load_AC_Install.Rejected_Reason = Load_AC_Install_Rejected.Validation_Reason FROM (Load_AC_Install JOIN Load_AC_Install_Rejected ON Load_AC_Install.Hardware_Portfolio_Barcode = Load_AC_Install_Rejected.Hardware_Portfolio_Barcode AND Load_AC_Install.Install_Model_Barcode = Load_AC_Install_Rejected.Model_BarCode )
After I load "Load_AC_Install" table into Temporary table #Load_Install and When I execute following SQL Query from SQL Query Analyzer using temporary table I am getting message Only 1 time "95 Records effected"
Update #Load_Install SET #Load_Install.Rejected_Reason = Load_AC_Install_Rejected.Validation_Reason FROM (#Load_Install JOIN Load_AC_Install_Rejected ON #Load_Install.Hardware_Portfolio_Barcode = Load_AC_Install_Rejected.Hardware_Portfolio_Barcode AND #Load_Install.Install_Model_Barcode = Load_AC_Install_Rejected.Model_BarCode )
So My Question is if i use phicical table (dbo.Load_AC_Install) then i get message 3 times, but If i use same data with tempoary table(#load_Install) I get message only one time "95 Records Effected" in query analyzer message window!
Anyone know why?
Thanks for response and please ask me if you still have any questions!
I am extremely new at SQL Server2000 and t-sql and I'm looking tocreate a simple trigger. For explanation sake, let's say I have 3columns in one table ... Col_1, Col_2 and Col_3. The data type forCol_1 and Col_2 are bit and Col_3 is char. I want to set a trigger onCol_2 to compare Col_1 to Col_2 when Col_2 is updated and if they'rethe same, set the value on Col_3 to "Completed". Can someone pleasehelp me?Thanks,Justin
I want to update the Vehicle_Specialisation_Id records for the same Motortrade_Id and the minimum effectdate record. In other words i want my outout like this..
I have wriiten a SP to do this one but it is a bit slow and i have quarter million records in my table. So i want a simple and single update query to do this one.
I am tring to execute the simple update query in query analyiser but after some seconds process got block by the same process(DEADLOCK).
query:
update tablename set col1 = 0
Other info may help you: Database had 3 different primary data files. In these files, first two files restricted file growth option is selected and there size is full. Only third file has space and option to grow. Table containts total 50 columns and 15 milion rows.
Please help me to solve this ASAP. Thanks in advance.
I recently transfered date into my sql server source table from Access data base but at that time one column was blank. ID and mainID so mainID was missing
Now i received and excell sheet. in which again i have ID and mainID both provided .
please let me knwo how do i update those mainID into the sqlserver cource table column using available ID.
I searched a bit but didn€™t get too far in actually solving a case of deadlock in a simple query I have running here. The queries in question are executed under 2 separate transactions (Serializable IsolationLevel) and are shown below. I guess I don€™t understand how those 2 can deadlock because they are operating on different rows of the table and Serializable should keep them isolated pretty well too. Is it because I€™m using the column value inside an update stmt? How should this query be split if that€™s the case?
This is what the SQL Profiler has to say: Lock: Deadlock Chain Deadlock Chain SPID = 59 Lock: Deadlock Chain Deadlock Chain SPID = 57 Lock: Deadlock my_user_name
57: UPDATE CreditCard SET Balance = Balance - 200 WHERE (Account = 0 AND CardHolder = 'Foo' AND Balance - 200 >= 0) 59: UPDATE CreditCard SET Balance = Balance - 250 WHERE (Account = 3 AND CardHolder = 'Bar' AND Balance - 250 >= 0)
I also used DBCC TRACEON(1204, 3605, -1) but I don€™t understand what the SQL log is telling me. Can anyone shed some light on why the above 2 statements sometimes cause the following: System.Data.SqlClient.SqlException: Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I really don't want to retry the update if I don't have to. Table looks like: Column DataType Length
I'm converting an ASP system from using msaccess to SQL Server as the db engine, and I'm stumped on the following query
update timecard set TcdDate = #3/18/05#
TcdDate is defined as a date/time type
It will not run with the date bracketed by # signs, and when I take them out, 1/1/1900 is stored in the dbs. Is there a different symbol to bracket the date with or should I be using a function to convert the date?
