I am a little new at T-SQL. and I could use a suggestion on the best way to accomplish this task. I have an application where I must loop through a table in Microsoft SQL Server 2005, find the records that match and break them up into groups where the value of the Thickness field is in groups of ( <=8).
Lets say I have 100 records and the table contains sacks marked A, B, C, and D. Within Sack A is 6 records. Each of the 6 records has a thickness column with a value between 0.5 to 1.0 (my example shows 2) I have a column called bundle that I must update to show a 1 for the first group of (<=8) inches thick a 2 for the second group (<=8) inches thick and so on. Then repeat the process for sacks B, C and D.
What I need to do is sum the thickness until it gets to less than 8 and update bundle with a 1. The result would be 4 records and the table would then look like this:
Notice there are two records that are not bundles for sack A. I need to place them in a separate bundle called 2. The result will be as follows
Order Number |Zipcode | Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |2
6 |19809 |A | 2 |2 |
7 |19721 |B | 4 |NULL|
8 |19721 |B | 3 |NULL
9 |19721 |B | 2 |NULL
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |
Now Sack A is complete so I must move on to Sack B. The first two records total to 7 and adding another record would move the thickness past 8 so I must update the bundle for only the first two records with a 1. The next two total to seven also and the following 2 total to four. We must set those bundles to 1, 2, 3 as follows
Hello, Its hard trying to explain this. I have 3 tables Table 1 is where the users are stored, each user has a username and a userrank Table 2 is where the points that decides the userrank are stored Table 3 contains the available userranks like this
Table 1 (user_list) looks briefly like this:username nvarchar(20),userrank int, -- Reference to Table3 id... alot more fields Table 2 (settings_profile) looks like this:username nvarchar(20),total_active_points int,... some more fields Table 3 (data_ranks) looks like this:id int primary key auto inc,rankname nvarchar(20),min_pts int,max_pts int
Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code: WHILE (SELECT @username = username, @id = id, @temp1 = ap_sentmails, @temp2 = ap_createdthreads, @temp3 = ap_createdanswers, @temp4 = ap_signguestbook, @temp5 = ap_blogcomment, @temp6 = ap_createblogentry, @temp7 = ap_profilefirsttime, @temp8 = ap_profilephoto, @temp9 = ap_activateguestbook, @temp10 = ap_addnewfriend, @temp11 = ap_superguruvote, @temp12 = ap_forumtopicvote, @temp13 = ap_labervote, @temp14 = ap_funstuffitemvote, @temp15 = ap_movievote, @temp16 = ap_actorvote, @temp17 = ap_money_new WHERE (created < Dateadd(dd, -7, @todaysdate))BEGINSET @sum = 0SET @sum = @temp1 + @temp2 + @temp3 + @temp4 + @temp5 + @temp6 + @temp7 + @temp8 + @temp9 + @temp10 + @temp11 + @temp12 + @temp13 + @temp14 + @temp15 + @temp16 + @temp17UPDATE settings_profile SET total_active_points = total_active_points - @sum WHERE (username = @username)DELETE FROM konto_daylist WHERE (id = @id)END Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.Something like this... DECLARE @username nvarchar(20)DECLARE @pts int, @rank int ...something that starts a loop thru table A (user_list) and get the username into @username... SELECT @pts = total_active_points FROM settings_profile WHERE (username = @username)-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranksSELECT @rank = id FROM data_ranks WHERE (pts_min => @pts AND pts_max < @pts)UPDATE user_list SET rank = @rank WHERE (username = @username) ...next persion in the loop... This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank... But how do i loop thru all the users? with a cursor?
The process of adding a column with DEFAULT (0) to a table that has 15million records takes a despicable amount of time (too much time) and the transaction log of the database grew to an unacceptable size. I would like to accomplish the same task using this procedure:
·Add the column to the table with null value. ·Loop through the table (500000 records at a time) and SET the value in the newly added column to 0. ·Issue a commit statement after each batch ·Issue a checkpoint statement after each batch. ·Alter the table and SET the column to NOT Null DEFAULT (0)
Here is my Sample script
ALTER TABLE EMPLOYEE ADD EZEVALUE NUMERIC (9,6) NULL Go
Loop UPDATE EMPLOYEE SET EZEVALUE = 0 Commit Tan CHECKPOINT END (Repeat the loop until the rows in EMPLOYEE have the value 0)
Go
ALTER TABLE EMPLOYEE ALTER COLUMN EZEVALUE NUMERIC (9,6) NOT NULL DEFAULT (0)
My problem is with the loop section of this script. How do I structure the loop section of this script to loop through the employee table and update the EZEVALUE column 500000 rows at a time, issue a Commit Tran and a CHECKPOINT statement until the whole table has been updated. Does anyone out there know how to accomplish this task? Any information would be greatly appreciated.
Hi. This is my first attempt at a using stored procedures and I'm a bit confused. I'm trying to follow as many best practices as I can to improve speed, security and scalability. However, I can't find a solution to what I think should be a simple problem. I have a search page where users enter the criteria of properties they are interested in (bedrooms, price etc...). That takes them to a results page where the properties are displayed. The problem is that I want the amount of times each property has been shown on the results page to be tracked so the property owner gets statistics. The property details are all held in a single table along with the amount of times each property has be shown: Table Name: zk_Property_USA
ID INT
User_ID INT
Property_Type TINYINT
Market_Status TINYINT
Price INT
Bedrooms TINYINT
Address_State VARCHAR
Address_Location VARCHAR
Property_Description VARCHAR
Searched INT 0
Contacted INT 0 I'm trying to find a way to SELECT all the property details to be returned to my results page and UPDATE the "Searched" field by 1 without re-scanning the table for the UPDATE. Is there a way to update "Searched" at the time when the record is chosen to be a result? I am using SQL Server 2005 and Visual Basic ASP.NET 2.0. Many Thanks
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.
The space between '1' and 'A' isn't showing up correctly.
TABLE_B [fleet] [ron] 1 A 1 A 1 A 1 A 1 A 2 2 2 2 3 B 3 B
(fleet=1, ron=A; fleet=3, ron=B; etc.etc.)
I would like to fill TABLE_B's column "ron" by referring to TABLE_A's column "dest".ac = fleet, and for each ac, I would like to loop through the 'dest' column in TABLE_A from top to bottom to get the top most value. If there are no values (like with ac = 2), then value is blank.
For 1, the value is A. For 2, the value is blank. For 3, the value is B.
Therefore, in TABLE_B, for all 'ron' for fleet=1, the value is filled A. For all 'ron' for fleet=2, the value is blank. For all 'ron' for fleet=3, the value is B.
- create variable 'v' - where ac=fleet, loop through 'dest' from top to bottom to get top-most value. variable 'v' = the value. - in TABLE_B, for each ac=fleet, insert variable 'v' into the 'ron' column
I've been looking online and cannot find any help / resources with this so I brought it here :D I'm looking for help in creating a Cursor (this will be inside a SP) that will loop through the records of a "Table" (Temporary or Retrieved) and for each row that is looped through I can use it's values to do inserts against a few other tables. Any resources / help would be great! I work best by example.
Hi, If i have an SP called mySP that accepts one parameter @param If I have a table of paramaters with only one column like this: Param1 Param2 .. ParamN
How do I do if I want to execute the SP on all the table fields: some thing like this: Exec my SP 'Param1' Exec mySP 'Param2' ... Exec mySP 'ParamN' I want that automatically since the parameters are going to be in a table called myTblParams Notice that I don t want to pass all the parameters to the SP just once but only one value each time I execute the SP since mySP ccepts only one parameter.
I have a large table that looks like this. (ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10)) 1, 1, p12, 1, p23, 2, p34, 2, p45, 3, p56, 3, p67, 4, p78, 5, p19, 5, p210,5, p83 i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner. can anyone help? i have to use temporary tables. (not cursors-with cursors i know how to di it, but i want with temporary table) thanks in advance
I have a table (temp_Order) wherein I use to insert a temporary order... after inserting the temporary order, when the buyer submits the order,I want to loop through the table and fetch the orders made by the buyer and insert those products on another table (Order)...how can I achieve this? im currently using sqlDataSource...
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.
I have a master table containing details of over 800000 surveys made up of approximately 400 distinct document names and versions. Each document can have as few as 10 questions but as many as 150. Each question represents one row.
My challenge is to create a separate spreadsheet for each of the 400 distinct document names and versions containing all the rows and columns present in the master table. The largest number of rows would be around 150 and therefore each spreadsheet will not be very big.
e.g. in my sample data below, i will need to create individual Excel files named as follows . . . "Document1Version1.xlsx" containing all the column names and 6 rows for the 6 questions relating to Document 1 version 1 "Document1Version2.xlsx" containing all the column names and 8 rows for the 8 questions relating to Document 1 version 2 "Document2Version1.xlsx" containing all the column names and 4 rows for the 4 questions relating to Document 2 version 1
I assume that one of the first things is to create a lookup of the distinct document names and versions assign some variables and then use this lookup to loop through and sequentially filter the master table data ready for creating the individual Excel files.
--CREATE TEMP TABLE FOR EXAMPLE
IF OBJECT_ID('tempdb..#excelTest') IS NOT NULL DROP TABLE #excelTest CREATE TABLE #excelTest ( [rowID] [nvarchar](10) NULL, [docName] [nvarchar](50) NULL,
Hi friends,ID LASTNAME--------+----------------------------2FFF1XXX0CCC1DDD2BBB0EEE0GGG3III3HHH4ZZZ4ZZZplease help me in sorting this table. the table should be sorted basedon LASTNAME, and then the ID (only non-zero id should be sorted).ID LASTNAME--------+----------------------------2BBB2FFF0CCC1DDD1XXX0EEE0GGG3HHH3III4XYZ4ZZZshould be sorted based on last name and should be grouped with thesame ID, except for the ID as 0
Hi Friends,How can we insert records in sorted orderlike consider a tableNo Name Phone1 test1123451 test1234552 test2686383 test3676844 test4548084 test474594if i add a new record like this2 test234454it should go in specific order. so that the final table should looksomething like thisNo Name Phone1 test1123451 test1234552 test2344542 test2686383 test3676844 test4548084 test474594
Hi, I have a table whose Identifying column is not an integer but rather a manually entered id. (i.e. 106F, 106-09, G11 etc.) When sorted ascending, a G11 will come before a G2 in the list, 106-11 before 106-2, etc.I would like to insert a new column in the database or use some kind of function when sorting the database to ensure that the list in returned in the proper order. Any ideas?Much appreciated...
I have a table which visibility can be toggled by a text box. By default it is invisible. After it is made visible, clicking a sortable column header makes the table invisible. Does this mean sorting makes the table go back to its default visibility?
I have a field (varchar) in a list that contains numbers and letters. I want to sort this table but I have only two functions that will work to convert the values:
Val The Val function sorts the numbers in the string, but the letters are not sorted
CStr The CStr function sorts the letters, but the numbers are not sorted
akanksha shukla writes "the issue is that i want to sort the records stored in a table according to aparticular column in such a manner that the sorted reaults are permanantly commited onto the same table i.e. the results are reflected on the same table whose records are being used for sorting i know that order by clause can provide me a view of records sorted on basis of a particular column but the changes are not being reflected onto the table . i donot want to create a fresh table to reflect the changes. kindly suggest some help"
As I said above, how do I put sorting + paging in a stored procedure.My database has approximately 50000 records, and obviously I can't SELECT all of them and let GridView / DataView do the work, right? Or else it would use to much resources per one request.So I intend to use sorting + paging at the database level. It's going to be either hardcode SQL or stored procedures.If it's hardcode SQL, I can just change SQL statement each time the parameters (startRecord, maxRecords, sortColumns) change.But I don't know what to do in stored procedure to get the same result. I know how to implement paging in stored procedure (ROW_NUMBER) but I don't know how to change ORDER BY clause at runtime in the stored procedure.Thanks in advance.PS. In case "ask_Scotty", who replied in my previous post, http://forums.asp.net/thread/1696818.aspx, is reading this, please look at my reply on your answer in the last post. Thank you.
Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005 TABLES Category SubCategory (PK)CategoryName (PK) SubCategoryNameCategoryID SubCategoryIDDate Date (Just shows the date inserted) (FK)CategoryID On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
INSERT INTO #Activities VALUES('Field phase S14-04932-01') INSERT INTO #Activities VALUES('Phase reporting') INSERT INTO #Activities VALUES('Phase running') INSERT INTO #Activities VALUES('RD1')
Hi All, I have a Problem while updating one table data from another table's data using sql server 2000. I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms) I need to update TableA with TableB's data using a single query that i have including in a stored procedure.
Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong...
Using T-sql!!!
update import set import.gone = from import inner join stat ON stat.id = import.id CASE WHEN stat.A = import.field2 THEN import.gone = sec.A WHEN stat.B = import.field2 THEN import.gone = sec.B WHEN stat.C = import.field2 THEN import.gone = sec.C WHEN stat.D = import.field2 THEN import.gone = sec.D WHEN stat.E = import.field2 THEN import.gone = sec.E WHEN stat.F = import.field2 THEN import.gone = sec.F ELSE import.gone = null END
I am trying to update a table in one database with data from a temporary table which i created in the tempdb.
I want to update field1 in the table with the tempfield1 from the #temp_table
The code looks something like this:
Use master UPDATE [dbname].dbo.table SET [dbname].dbo.table.field1 = [tempdb].dbo.#temp_table.tempfield1 WHERE ( [dbname].dbo.table.field2= [tempdb].dbo.#temp_table.tempfield2 AND [dbname].dbo.table.field3= [tempdb].dbo.#temp_table.tempfield3 AND [dbname].dbo.table.field4= [tempdb].dbo.#temp_table.tempfield4)
I get the following error: Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "tempdb.dbo.#temp_table.tempfield2" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "tempdb.dbo.#temp_table.tempfield3" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "tempdb.dbo.#temp_table.tempfield4" could not be bound.
I want to write trigger code to update values in table1 to table2. ALTER, CREATE, DROP cannot be used in a trigger, I guess.
Tabel1 ID, Name, Asset, Date, Active --------------------------------- 1, A, 10 , 01/08/04 Y 1, B, 16 , 06/08/04 Y 1, C, 12 , 07/08/04 Y 1, D, 13 , 10/08/04 Y 2, E, 10 , 15/08/04 Y 2, F, 11 , 16/08/04 Y 2, Y, 12 , 01/08/04 N 2, G, 15 , 17/08/04 Y 3, H, 13 , 19/08/04 Y 3, I, 15 , 02/08/04 N
As you can see, code must select only active('Y') names in table1 arrange in descending order of asset for each ID and updates(or insert into?)table2. Please help me with code
How do I update a table from another table which resides in a different database but on the same server. Example Update table a which is in database 1 from table b which is database 2. Please help I need this as soon as possible. Thanks in advance to anyone who can help.
Accidents, Vehicles, Casualties. the relationship is many vehicles to an accident and many casualties to a an accident.
I have one new table which will contain all the vehicles related to all accidents. however i want to be able put in all casualties that every vehicle is related to..so within the new table, there are three severities of casualties Vehicledetails column severity1 severity2 severity3
For every vehicle where there is 0 or more casualties I want to be able put a (total )number for each category of casualty( severity in the VehicleDeatils table ) into the columns in vehicledriverdetails
how can i do this?? i dont want ot have to manually update every single entry one by one...
