T-SQL (SS2K8) :: Repopulating A Sequenced Column That Has Gone Out Of Sequence
Jul 5, 2014
We have an application which has had a big design floor and is now causing an issue which needs to be rectified. It inserted data in batches of 2000 rows which should have always been in sequence. A column was used which recorded an ever increasing numerical value for each row that was inserted. So batch 1 would have a start value of 1 and an end value of 2001. Batch 2 would have a start value of 2002 and an end value of 4002 etc.
The problem is the application which inserted these records in order, missed some records. So in actual fact we have:
Now the missing data has been found, it will be inserted at the end of the table, as we cannot insert it where its should be as the sequence value has been used by the next batch already. Lets say the table is 10,000 rows so far, so the seq for batch 2, for example, will be 2002-3998, then after the missing data is inserted, 10,001 - 10,005. The application uses these sequences to show the range of data so where it should be 2000 rows being returned, it would now show 8003 rows - all the other batches between the first and last sequences for the batch!
I need to change the sequence values across the whole table so they are in order. I can select the data in the correct sort order as there is another unique key on the table, but unsure how best to use this to change the sequence column to be in order for all rows. Adding an identity column would work and then remove and rename the seq column, but I obviously cannot add this based on another columns sort order. The identity column would use the clustered key which is in the incorrect order!!
I'm trying to do a simple insert into a table, something like this:
insert into sometable (ID, somecolumn) select 'Task-ID', somevalue from SomeOtherTable where something = 'someothervalue' (or something to that effect)
So, the SELECT would generate something that looks like this:
What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.
Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.
What I'm hoping to do is rewrite my SQL like this:
insert into sometable (ID, somecolumn) select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable where something = 'someothervalue'
I am pretty new to SQL and facing difficulties with a current problem.
I have a list of customers and a sequence of events they have triggered . I know the sequence in which these events have been triggered and only want to increment a value when a new event is triggered (value to remain the same if the event is the same). I have come close to a solution with Dense_rank but the problem here is that the ranking doesn't reset if the same even previously triggered is triggered a bit later in the sequence. see below an example of current results and expected results:
Can you help me with SQL issue I€™m stuck on? I wish to take source data that looks like this:
Invoic_num
Line_num
6658
0
6658
2
6658
8
7721
2
7721
3
And rebuild the line numbers like this:
Invoic_num
Line_num
6658
1
6658
2
6658
3
7721
1
7721
2
This seems completely impossible to me. So I was thinking that maybe a second procedure using update could be run against the table after the initial build.
Hi, I'm new to sql server but have used oracle sequence numbers to create unique keys in my tables. I've been looking for examples on how to create a sequence type table in sql server and have also found that there is a column called identifier but am unsure how to use this... I created a table and used this column attempting to insert (thinking it would autoincrement) but it fails... Any insight into how I can accomplish a sequence type activity for unquie numeric ids would be helpful, Thank you for your help,Jay
I apologize if this is redundant.How would one fill an empty column with a sequence of numbers? Thecolumn exists in a table with aproximately 1000000 rows of data. Ibelieve in oracle the following would work:update foo set bar = rownum;....but 'rownum' does not seem to exist in mssql. The numbers do not needto be in order, but I would like to keep them somewhat small.Any help would be appreciated.
I am looking for ways to add new column to a table with records inside. If I add the column using this statement:ALTER TABLE Table1 ADD NewColumn1 decimal(18, 2) NULLThe column would appear at the end.
Is there a way to set where the column should be placed? (Excluding dropping all columns and add the columns in sequence again). I know this might not be very important, I am interested in knowing how Enterprise Manager done this, since you can move a column up and down to change their sequence eventhough there're records inside.
i want to update one column from my table regularly on sequence number i.e. 0,1,2,3,4,5
i created procedure but it is not working as per my output
declare @IndexIDGen int declare @ID int set @ID = 0 update temp_test set indexid = NULL declare IndexIDGen cursor for select indexid from temp_test open IndexIDGen FETCH Next from IndexIDGen into @IndexIDGen while @@fetch_status = 0 begin update temp_test set indexid = @ID where indexid is null
set @ID = @ID + 1 print @id fetch next from IndexIDGen into @IndexIDGen end
I have a Column called SaleID in some tables in a Database.The SaleID column has the int datatype. I need a stored procedure that returns the next value in sequence for the SaleID column.
For Example, If the last value inserted into the SaleID column was 1022 the stored procedure should return 1023 If the last value inserted into the SaleID column was 1023 the stored procedure should return 1024. If the last value inserted into the SaleID column was 1024 the stored procedure should return 1025.
Also an exclusive lock should be maintained while the the stored procedure is running.
Create Table Sample (ID int not null primary key, RefID int , SeqNo int , Name varchar(10) )
insert into Sample
select 1, 1000, 1, 'Mike' union select 2, 1000, 2, 'Mikey' union select 3, 1000, 3, 'Michel' union select 4, 1001, 1, 'Carmel' union
[code]....
select * from SampleI have here sample data given. What I want to do is, I want to check the RefID which is not having proper order of sequence number. If you see the RefID 1000, 1001 they are having properly sequence order in SeqNo field. But it is not in RefID 1002. RefID 1002 does not have proper order. It is because user has deleted a row which was having seqno 2. So i want to get what are all the RefID's are not having properly sequenced. So that I would be able to know these are all the RefID's are affected by delete statement that was done by user.
If Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' ) Begin UPDATE HH835HP SET ID_1 = ( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ; End;
Obviously... The stuff inside the IF is wrong syntax...I mean
UPDATE HH835HP SET ID_1 = ( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
I am working on 1 POC project.I have 2 customer having source file in txt format, but the column sequence of both customer are diffrent.Number of columns in all files are like below.
CustA
ID Â NAME Â AGE 1 Â Â VIPIN Â Â 29
CustB
ID Â AGE Â NAME 2 Â Â 29 Â Â Â jayesh
As per source file you can see that CustA have column sequence ID,NAME,AGE and CustB Have ID,AGE,NAME sequence .I have target table #Temp with ID,NAME,AGE sequence.Like that I have many files from both customer, I have to load in ID,NAME,AGE sequence from all source file to target table.How can we change the sequence of source column before loading to target table.
The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' ) Begin --Adding a sequence of numbers to the ServiceLineID column. SET @id = 0 UPDATE tmpMHPCLMDET SET @id = ServiceLineID = @id + 1; End;
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change DECLARE@size varchar(5)--The new size of the column DECLARE @TSQL varchar(255)--Contains the code to be executed DECLARE @Object varchar(50)--Holds the name of the table DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10) ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1 Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
I have a script that loops through a series of tables to send data to a table from each of the tables. My issue is that not all tables have the columns I need in them. What I would like is to replace the column with another column when it does not exist. Something like below
Select Misisng_Column(A.Name, replace with B.Name) as Name FROM SomeTable A Cross Join (Select Name FROM AnotherTable) B
AnotherTable has one record in it. To avoid a Cartesian issue. Like I said just an example
In my real script the table aliased as A is from a list of tables in a sys.tables query that loops through to the end.
I have a table with Million plus records. Due to Running Totals article, I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.
For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46
I am trying to do some type of self join, but not sure how to include each actindx number differently.
Table creation and data insert is below.
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[TrialBalance]( [Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
With cte_table (columna) As (select a from cust) Update columna Set a = 'hello'
Question: would a in cust be updated even though the update is to the CTE column named columna? Or would there be an error where the column name must match?
I have to populate a table of exchange rates which is easy enough however because rates are held on Fridays but I need to make calculations on weekends or holidays I need to populate the Friday rate on non weekends and holidays. I have a sample table below with null values on the weekends for the last 90 days but need a script that will show the Friday exchange rate on Saturday and Sunday
Here was my latest attempt
;with cte as ( select currxdate, [from], [TO], CurrXRate from dbo.CurrXchange ) select a.CurrXDate, a.[From],a.[To] , isnull(a.CurrXRate, b.currxrate) as 'CurrXRate'
i need distinct max between a1&a2 which i can get no problem but i cant get that unique datetime that correspond to a1&a2 in 1 query because this is will a subquery in a big query. Creating another temp table etc is not an option for me. for every specific a1 there is many entries of a2 + timedate etc.
create table abc_test ( id int ,runs int ,date1 datetime
[code]....
I can either get distinct ID + latest date or ID + largest #ofRuns, both will do but also need the third column.
What I want to do is add a column that has 5 values and create a row for each value
I want it too look like this -
024Swanson, Ronrswanson@tv.com a 024Swanson, Ronrswanson@tv.com b 024Swanson, Ronrswanson@tv.com c 024Swanson, Ronrswanson@tv.com d 024Swanson, Ronrswanson@tv.com e
[Code] ....
Currently all my query looks like is this -
SELECT EmpID, LastFirst, Email FROM dbo.EmpList WHERE (Active = 1)
I have table1 with col1 varchar,col2 int , col3 xml , col4 bit ...best way to fetch the col3 into file (.txt or .sql) into seprate file for each col3 record . I want to export this in different files for each record if possible with date and time stamp .
Now for columns Week1 to week3 if value is 0 then i want to display by searching next week value, if it is also 0 then go for next week and if value found there then display instead of zero. so my output would be as below instead of above.