Currently working on a upload module where in the data from excel file is imported to the destination tables. Data in the excel sheet comes in phases. All excel sheet columns data don't come at first shot. The excel sheet's data is dumped into temporary tables which inturn is looped using cursor's and gets finally updated to the actual tables.
Now, the problem I am facing is how do I update columns of the actual table with the data (i.e NON NULL values) available in the temporary table without tampering the data allready present in actual table.
Ideally what required is, update the actual table column values with the corresponding columns of temporary table ONLY for Non NUll column values of temporary table.
Temporary and Destination tables have 85 columns each. I don't want to write 85 update queries.
The scenario which I am facing is given below with 2 columns as an example.
1. Table 1 :- tbl_source (Temporary Table) has two columns src_Col1 & src_Col2
2. Table 2 :- tbl_destination (Actual Table) has two columns dest_Col1 & des_Col2
Scenario -1
---------------
tbl_Source Sample Data (after excel import to the temporary table)
------------------------
src_Col1 src_Col2
------------------
50 NULL
tbl_Destination Sample Data
------------------------
dest_Col1 dest_Col2
------------------
50 NULL
Scenario -2
---------------
tbl_Source Sample Data
------------------------
src_Col1 src_Col2
------------------
NULL 100
tbl_Destination Sample Data
------------------------
Can you create an UPDATE TRIGGER and use some typeof code to figure out which SP just updated the current table?If not how can i achieve what i want?I tried to run SQL Profiler and i don't understand why i can'tsimply have the Profiler filter events only for the specific database idand the table's object id i chose?What am i doing wrong with SQL Profiler? I was testing thisthrough SQL EM. I had the filters chosen for a specific database idand a specific table's object id, yet when i open another table SQLProfiler captures that information too.Thank you
Is there a way to update a specific cell(s) in an excel? I have an excel with charts and graphs which use as data source a range of cells from another sheet within the same spreadsheet. Is there a way update a specific cell from within the sql using openrowset()... ?
In my datawarehouse fact table I have a column (revenue) that I want to populate based on the values of number of columns, for simplicity, say just 2 columns, 'productid' and 'affiliateid'.
I have a revenue lookup table, with those same 2 columns and the amount. So far so simple, but rather than have one row for every possible combination, I use 0 to mean default. For instance, all the affiliates have the same revenue value apart from a couple, so instead of 200 rows identical except for the affiliateid, I have one row with a '0' for the affiliateid and 4 rows with specific affiliateIDs where it differs from the default.
To update the values, I join to the revenues table twice, one for both columns matching, and once for the default. I.E.
UPDATE facttable SET revenue = ISNULL(rev1.revenue, ISNULL(rev2.revenue,0)) FROM facttable FT LEFT OUTER JOIN revenues rev1 ON FT.AffiliateID = rev1.AffilateID and FT.TypeID = rev1.TypeID LEFT OUTER JOIN revenues rev2 ON rev1.AffilateID = 0 and FT.TypeID = rev1.TypeID (In fact, this is over-simplified, because in fact there are 3 columns, so I have to have 8 joins like this).
This works very well, and cuts down the management of revenues significantly, there are a few 100 rows instead of the more than 100,000 there would be if I put every possible combination of values in its own row.
However, now there is a requirement to increase the granularity of the revenue allocation up to 5 columns, which makes 36 joins and there could well be more columns added later.
Has anyone come across a situation like this (and found a neater solution).
I have a table with 35,000 records in it. I want to update a value in column A for only the first 5000 records, leaving the value in Column A for the remaining 30,000 records as it is now. What would be the command I would use to update Column A for the first 5000 records.
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
I'm not sure if this could be done, but if anyone has any insight on how to do this please let me know...
Currently, I have a table that has a field of Categories. I recently created a Category table in which each category has it's own ID. I need to replace the Data that was in my original table with the new ID's based on the actual category name... Is there any way of doing this without manaul data entry?
I'm not sure if this could be done, but if anyone has any insight on how to do this please let me know...
Currently, I have a table that has a field of Categories. I recently created a Category table in which each category has it's own ID. I need to replace the Data that was in my original table with the new ID's based on the actual category name... Is there any way of doing this without manaul data entry?
I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?
I have a question. I know its possible to create a dynamic query and do an exec @dynamicquery. Question: Is there a simpler way to update specific columns in a table at run time without doing if else for each column to determine which is null and which has a value because i'm running into a design dilemna on how to go about it.
FYI: All columns in the table design are set to null by default.
There are two tables. the second one is a table that contains fields based on which the exclusion logic has to be written. It has 5 fields and there may be data in all of them or only in some of them.
The first and second table have one field in common - the Account number.(ACCT_NUM)
the exclusion is based in steps..
1) if it has all the fields in the second table for that account number, then compare all of them.. 2) if only 4 of them are present,compare and check for null for the rest 3) if only 3 of them are present,compare and check for null for the rest 4) if only 2 of them are present,compare and check for null for the rest.
I'm looking for a quick script that someone has already written to update statistics (not to rebuild or re-organise) on specific indices in specific databases - I guess loop though a table comprising of a list of databases and the indices.
I know Ola has one but I'm not look for something that is that complicated. If I cannot find one I'm going to have to write one myself - I want to try and avoid re-inventing the wheel as tomorrow I have to do this work and it's about 7K plus indices in about 10+ databases.
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
I want to copy FirstName and LastName cells from table1 to table2. Which FirstName and LastName cells depend on the username, i.e. which person has logged in. Q1) How can this be done? (I have chosen to SELECT from Table1 and output to variables (C# code); then INSERT these variables into Table2. [Finding it very difficult]). Any suggestions/comments would be appreciated. Thank you in advance.
I need the start and end time of consecutive records of the same vehicle with 0 speed ordered by date_time. If there is more than one consecutive record with zero speed it needs to be grouped together.
I have some duplicate values for my query results, about 200 duplicates out of 30000 rows. Of these 200 duplicates I want to keep the ones that have a higher value for... 'UpdatedBatchID'.
SELECT IR.Id as 'ID' , CAST(IR.Priority as varchar) as 'Priority' , IRSupportGroupDN.DisplayName as 'Support Group' , DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.CreatedDate) as 'Created Date' , DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.ResolvedDate) as 'Resolved Date' , SLOConfig.DisplayName as 'SLO' , DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),SLOFact.TargetEndDate) as 'SLO Target' , SLOStatusDN.DisplayName as 'SLO Status' , SLOMetric.DisplayName as 'SLO Metric' , SLOFact.UpdatedBatchId as 'UpdatedBatchID'
I have a table in SQL server which has two rows. One has an ID of 'Bag CL55412'. Another has an ID of 'Bag CL55412-Cpy'. The Price for the first one is $99.99. I want to make the price for the second one 2% more & $.99 more.
The data looks like this IDChannelPriceCompanyID Bag CL5541299.99111 Bag CL55412-Cpy102.99500
The SQL to select that formula looks like this.
SELECT [ID] ,[ChannelPrice] ,(CEILING([ChannelPrice]*1.02)+.99) ,CompanyID FROM [SC].[dbo].[Product] WHERE ID like '%CL55412%'
To Update, I can think of something like this, but it will update based on itself, not a different row in the table.
UPDATE [SC].[dbo].[Product] SET ChannelPrice=(CEILING([ChannelPrice]*1.02)+.99)
How would I get it to update as I want it to? The origin CompanyID will always be 111 & the destination company ID will always be 500 for all of the respective rows that need to be updated
I have a table with a max key value in it, and another table with a few rows in it. I'm trying to update the two new rows with key values that are iterative from the MAX value in the first table. Could anyone point me to some good LOOP...UPDATE, etc resources or pointers before I go postal?
::grins::
Example: first table has a key field, last value in it is say 1000. The second table has two records. They need keys too, and those keys need to start at the last value in the first table + 1, so 1001, and end on the last row of the second table, or, say 1002. I cannot figure out how to read the last value of the first table, and create an update loop to iteratively update the key value in the second table based on the max value of the first, and looping based on the number of records in the second, in this case 2.
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Hi People, hope someone can help me out here with a little problem. Basically i've go a asp.net page which has a listbox on. This list box is populated from a SQL database table with the datetime of the a selected field. Thus in the list box you get a list of strings looking like this "24/09/07 12:58" Also on the page is a submit button, and some other editing textboxes. The main issue here is the when the submit button is used i get the currently selected listbox timedate string and then pass this along with other items to update a record in the database based on the datetime in the listbox control.
Below is how i get the string from the listbox control Dim except_time As DateTime except_time = DropDownList1.SelectedValue The expect_time is then passed to store procedure along with some other vars, which looks like this -- =============================================-- Author: Lee Trueman-- Create date: 1st Sept 2007-- =============================================CREATE PROCEDURE [dbo].[spExcept_UpdateData] -- Add the parameters for the stored procedure here @validated bit, @update_time datetime, @except_time datetimeASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON -- Insert statements for procedure here UPDATE exceptions SET validated = @validated, update_time = @update_time WHERE (except_time = @except_time)END So validated and update_time should be updated when except_time is equal to @except_time
My problem is that the database never updates. If i debug the asp.net page the watch var shows the datetime in US format (I.e "09/24/07 12:58"), if this is true then this would explain why when it is passed to the stored proc nothing gets updated, as there would not be a date match. can anyone see any silly mistakes i'm doing here ????
I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.
Update CurrRank = ??? from tblAppKitOffers where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D') ORDER BY tblAppKitOffers.OfferAmount Desc
I am trying to figure out if what i am attempting to do is possible and whether or not my approach is wrong to begin with.
I am trying to build a custom report for our accounting system which is Traverse from Open systems. This is what i have done in the stored procedure thus far
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
ALTER PROCEDURE rptArFLSalesByCustItemized_sp @custId pCustID, @dateFrom datetime, @dateThru datetime, @itemIdFrom pItemId, @itemIdThru pItemId as set nocount on
-- define some variables for previous year declare @LYqty int, @LyAmt money, @LYfrom datetime, @LYthru datetime
-- set defaults SET @itemIdFrom=ISNULL(@itemIdFrom,(SELECT MIN(itemId) FROM tblInItem)) SET @itemIdThru=ISNULL(@itemIdThru,(SELECT MAX(itemId) FROM tblInItem)) SET @LYfrom=DATEADD(YEAR,-1,@dateFrom) SET @LYthru=DATEADD(YEAR, -1, @dateThru)
-- create small temp table to hold customer info Create Table #tmpArCustInfo ( custId pCustID, custName VARCHAR (30), ) -- populate customer temp table with info Insert into #tmpArCustInfo select custId, custName from tblArCust WHERE custId = @custId
-- create a temp table to hold the Data for each Item Create Table #tmpArSalesItemized ( itemId pItemId, productLine VARCHAR (12), pLineDesc VARCHAR (35), descr VARCHAR (35), LYQtySold int, LYTDQtySold int, QtySold int, LYTDsales money, totalSales money, LastInvDate datetime, )
-- populate the temp table with all of the inventory items insert into #tmpArSalesItemized select ii.itemId, ii.productLine, ip.Descr, ii.Descr, 0,0,0,0,0, NULL from tblInItem ii, tblInProductLine ip where ip.productLine = ii.productLine AND ii.itemId BETWEEN @itemIdFrom AND @itemIdThru
-- update table with this years quantities update #tmpArSalesItemized SET QtySold = (select SUM(QtyOrdSell) from tblArHistDetail hd where TransId IN (select TransId from tblArHistHeader where custId = @custId) AND orderDate IN (select OrderDate from tblArHistHeader where OrderDate BETWEEN @dateFrom AND @dateThru) AND hd.partId BETWEEN @itemIdFrom AND @itemIdThru GROUP BY hd.partId )
-- Return the temp tables results select * from #tmpArSalesItemized, #tmpArCustInfo
drop table #tmpArSalesItemized, #tmpArCustInfo
return
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
My problems begin where i want to start updating all of the Qty's of the QtySold field. I have managed to get it to write the same sum in every field but i cannot figure out how to update each row based on the sum of the qty found for that item in the tblArHistDetails table, trouble is too that there is no reference to the custId in that table either. The custId resides in tblArHistHeader and is linked to the details table via the TransId column. So really i need to update many rows based on criteria from 2 other tables.
Can anyone please help? I dont have a clue how to make this work, and most of what i have learned about sql thus far has been from opening other stored procs etc in the accounting system and just reading to see how the developers have done things.
I am trying to update records based on the results of a query with a subquery.
The result set being produced shows the record of an item number. This result produces the correct ItemNo which I need to update. The field I am looking to update is an integer named Block.
When I run the update statement all records are updated and not the result set when I run the query by itself.
Below is the code I am using in an attempt to update the block column but it updates all records and not the ones which I need to have the Blocked field set to 1.
Update #items set Blocked = 1 Where Exists ( SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END) --,SearchNo --,COUNT(*)
[Code] ...
Why is the update changing each record? How can I change the update to choose the correct records?
CREATE TABLE EDI_data_proc_log( ID int IDENTITY(1,1), comment VARCHAR(3000), time_recorded DATETIME DEFAULT GETDATE(), run_by varchar(100), duration int );
When a record is inserted I like the duration column to be computed.This should happen only after the first record to the table has gotten inserted.You might say a trigger would be the best.. Ok then, show me the syntax.
Or I am thinking can we write a user defined function that will compute the value for the duration column.
--By default, I would like to update the duration column as follows:
--It should record the number of seconds between the last insertion ( You can get that time from the time_recorded column from the previous record and the current time can be obtained from the getdate() function )
I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
Could you guys please help me with printing reports invoked thru command line/ URL access to print automatically to specific printers and specific trays and also is it possible to set the specific printer and tray as parameters.
