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:

Batch 1 = seq 1 - 2001 (total 2000)
Batch 2 = seq 2002 - 3998 (total 1996, 4 missing)
batch 3 = seq 3999 - 5999 (total 2000)

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!!

View 9 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Programmatically Generate A Number Sequence?

Sep 3, 2014

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:

ID somecolumn
-- ----------
Task-ID somevalue1
Task-ID somevalue2
Task-ID somevalue3
(etc.)

Here's where my problem comes in: ID is a PK, and needs to be unique. What I need it to do is this:

ID somecolumn
-- ----------
Task-ID.1 somevalue1
Task-ID.2 somevalue2
Task-ID.3 somevalue3
(etc.)

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'

Is there an easy way to do this?

View 9 Replies View Related

Sequenced Increment Based On Previous Row Change

Jul 6, 2014

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:

Customer ID,Sequence ID,event,current result,expected result
1,1,A,4,1
1,2,A,4,1
1,3,B,3,2
1,4,C,2,3
1,5,A,4,4
1,6,A,4,4
1,7,E,1,5
1,8,D,5,6
2,1,B,3,1
2,2,C,2,2
2,3,C,2,2

View 8 Replies View Related

Generating Sequenced Line Numbers For Each Invoice Number

May 8, 2008

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.

View 10 Replies View Related

Sql Server Sequence Column?

Oct 7, 2006

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

View 2 Replies View Related

Generate Sequence No In One Column

Mar 20, 2007

helen writes "I have a sql table:
NAME AGE
Anna 10
Susan 5
Dina 12

Please help me to come up with a QUERY STATEMENT with this result:
SEQNO NAME AGE
1 Anna 10
2 Susan 5
3 Dina 12 "

View 2 Replies View Related

Fill Column With Sequence

Jul 20, 2005

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.

View 6 Replies View Related

Adding Column Using TSQL With Sequence Specified

Feb 14, 2005

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.

View 1 Replies View Related

Update Column With Sequence Number Every Time

Sep 25, 2006

hello friends

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

close IndexIDGen
deallocate IndexIDGen


where i am going in wrong direction ??????

T.I.A

View 8 Replies View Related

SQL Query For Most Probable Sequence Text In A Column

Nov 6, 2006

Sandip writes "I am doing a Project for detection of advertisement in a Web page.

For that I have used one column called ImageText

For giving Training to my database I stored (say) 100000 records in ImageText

Now I want to First n Sequence of Text Which Comes Most Times

For Exa:

ImageText
---------
ABc
ABcd
ABABABABAB
ABcdxyAB
ABcdABcdABcdcdAB

Here,
String No Of Times String Occurs
------ ---------------
AB 13
cd 6
ABc 6
ABcd 3
cdA 3
.....
....

ABc 1
ABcd 1
ABABABABAB 1
ABcdxyAB 1
ABcdABcdABcdcdAB 1

That is i want to find within a string also

For First 3 Most Popular Sequences

Output
------
AB
cd
ABc

Please Reply
If you do not understand the question
then
send me mail"

View 2 Replies View Related

Stored Procedure That Returns The Next Value In Sequence For A Column

Mar 9, 2008

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.

I am using SQL Server 2005.

View 4 Replies View Related

SQL Server 2012 :: How To Find A Missing Sequence In A Column

Mar 20, 2014

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.

View 8 Replies View Related

SQL 2012 :: Update A Column With A Sequence Of Numbers Starting From 1?

Oct 7, 2015

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 ) ;

View 4 Replies View Related

Integration Services :: How To Change Sequence Of Source Column

Nov 3, 2015

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.

View 5 Replies View Related

How To Create A Sequence Invoice Number And Insert Or Update To A Column?

Feb 22, 2005

Hi, can anyone teach me how to automatic create a invoice number and insert or update it to a column?

View 2 Replies View Related

SQL 2012 :: Populate Int Column With A Sequence Of Numbers But Sorted By Another Field

Oct 8, 2015

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;

View 2 Replies View Related

T-SQL (SS2K8) :: Cannot Define Primary Key Constraint On Nullable Column But Column Not Null

Sep 30, 2014

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.

View 2 Replies View Related

T-SQL (SS2K8) :: Replace Column With Another Column When It Does Not Exist

Jun 24, 2014

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.

View 2 Replies View Related

T-SQL (SS2K8) :: Use Previous Month Data In Column As Following Months Data Different Column

Aug 20, 2014

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,

[Code] ....

View 7 Replies View Related

Same Query Gives Result With Different Column Sequence When Used In Query Analyzer

Feb 25, 2012

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.

result in Excel 2003 SQL OLE DB query

col-A col-B col-C
values...

Result with Query Analyzer and VBA Macro

col-c col-B col-A
values...

View 3 Replies View Related

T-SQL (SS2K8) :: Pipe Row To Column

Mar 4, 2014

Sample Data

MemberID Codes
00000123 012|222|123|333
00000233 012|222|332
00000244 012|211
00000332 012

I am trying to get it so as following:

MemberID Code1, Code2, Code3, Code4

I tried using the XML method but in working with an example, I actually got stuck on one of the declare fields and cant seem to work around it.

Failed Attempt:
DECLARE @x xml;
DECLARE @line VARCHAR(MAX);
SET @x = Cast(
'<field>'
+ replace(@line, '|', '</field><field>')
+ '</field>' AS XML);

[code]...

View 6 Replies View Related

T-SQL (SS2K8) :: CTE Update Column

Oct 24, 2014

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?

View 2 Replies View Related

T-SQL (SS2K8) :: Rows To Column

Dec 29, 2014

Came across one scenario not able to find out how to do it..

Below is the table data

ColA
1
-1
2
-2

Need output in below way

ColA ColB
1 -1
2 -2

View 5 Replies View Related

T-SQL (SS2K8) :: Get Last Non Null Value From Column

Apr 14, 2015

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'

[Code] ....

View 8 Replies View Related

T-SQL (SS2K8) :: Row Into Multiple Column

Sep 1, 2015

Create table control_Total ( Filename varchar(1000) )

insert into control_Total values('PCH123_TLNX.account.TUED.20150831.txt Bytes:645 Records:4')
insert into control_Total values('PCH123_TLNY.account.TWED.20150831.txt Bytes:1920 Records:12')

how can I get output like this :

FileName RecordCountBytes
PCH123_TLNX.account.TUED.20150831.txt4645
PCH123_TLNY.account.TWED.20150831.txt121920

View 7 Replies View Related

T-SQL (SS2K8) :: Computed Columns - Max From Other Column For The Same ID?

Jul 25, 2012

Suppose a table being

Create table myTable (ID int, col1 int, col2 int)

I know how to make a computed column being the sum of other column for the same ID e.g. "computed_column = col1 + col2".

Getting the average would be "computed_column = (col1 + col2)/2" But how to get the Max, Min?

Even "Sum(col1,col2)" or AVG(col1, col2) does not work as the formula for a computed column...

View 9 Replies View Related

T-SQL (SS2K8) :: CTE Has More Columns Than Were Specified In Column List

Mar 22, 2014

I get the following error , when I execute my CTE on SQL Server 2008 R2.CTE1 has more columns than were specified in the column list

--SQL CODE IS AS BELOW

WITH CTE1 (AUCTIONID,[Open Time], [Response SLA Broken], [Response SLA Deadline], [Response SLA Actual], [Responsetime SLA Broken Before Transfer], [Resolution SLA Broken], [Resolution SLADeadline], [Resolutiontime SLA Broken

[code]...

View 2 Replies View Related

T-SQL (SS2K8) :: Converting Rows Into Column

Apr 18, 2014

It is possible to covert rows into column by using tsql script, see attached file for more details...

CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, ItemNo int)
GO
INSERT INTO Table1
SELECT '01', 'Camera', 100, 1
UNION ALL
SELECT '01', 'Memory 4GB', 10, 2

[code]....

View 5 Replies View Related

T-SQL (SS2K8) :: Max With Distinct Two Columns And Corresponding Third Column

Apr 30, 2014

T1
-------
a1
a2
datetime
a4
a5
.....

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.

View 5 Replies View Related

T-SQL (SS2K8) :: Creating A Custom Column?

May 14, 2014

creating a custom column that will put a list of 5 values for each unique value(or in this case Employee).

So I have a Employee table were I pull a list of all active employees -

Example -

024Swanson, Ronrswanson@tv.com
026Donaughy, Jackjdonaughy@tv.com
028Scott, Michaelsmichael@tv.com

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)

View 4 Replies View Related

T-SQL (SS2K8) :: Export Column To TXT Format

May 21, 2014

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 .

View 5 Replies View Related

T-SQL (SS2K8) :: Update A Column With AutoNumber

Jul 7, 2014

I need to update a column with AutoNumber which is depending on some other column. The output should be like following..

Obj1Obj2
0001ijk-000
0001ijk-000
0001ijk-000
0001ijk-000
0001ijk-000
0002ijk-001
0002ijk-001
0002ijk-001
0002ijk-001

I want to Update The column Obj2 based on the values of Obj1..

View 3 Replies View Related

T-SQL (SS2K8) :: Assign Next Available Column Values?

Sep 29, 2014

find below object and data:

create table #StuDetails(City varchar(25),StuStatus varchar(25), currentValue int,Week1 int,week2 int,week3 int,week4 int)
insert into #StuDetails values('A','new',13,10,0,0,12)
insert into #StuDetails values('B','Old',10,10,41,0,12)
insert into #StuDetails values('C','Fail',10,9,0,0,5)
select * from #StuDetails

Output of above is display as:

CityStuStatuscurrentValueWeek1week2week3week4
Anew 13 10 0 0 12
BOld 10 10 41 0 12
CFail 10 9 0 0 5

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.

CityStuStatuscurrentValueWeek1week2week3week4
Anew 13 10 12 12 12
BOld 10 10 41 12 12
CFail 10 9 5 5 5

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved