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


ADVERTISEMENT

Adding Line Sequence Numbers

Jun 25, 2007

hi all

I am stuck with something that seems easy but im obviously clueless as how to execute the idea.

I have a custom table that houses invoices on the details level. So for example i have:

InvcNo
00000001
00000001
00000001
00000002
00000002
00000003

and so forth

What I am wanting to do in another column is keep track of the sequence number for each distinct invoice like:

SeqNo
1
2
3
1
2
1

I am working in a stored proc and i cant get past adding the numbers up at each line as a whole and not reseting when the next invoice number is present. Any help would be so greatly appreciated.

Thanks

View 10 Replies View Related

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...



ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;


I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;







View 18 Replies View Related

TSQL - Generating New Column With The Maximum Value Of Other Column

Dec 2, 2007

Hi guys,
In the query below, I need to insert a new Column that shows the last Document date.
In example,
Document Date Last Date
2006/01/15 2007/11/30
2007/03/25 2007/11/30
2007/11/30 2007/11/30
I need to show the last (max) date for each row.
Thanks in advance for any help,
Aldo.




Code Block
SELECT
Stock.VALUEDATE AS 'Document Date'

FROM
Stock As Stock
INNER JOIN ACCOUNTS AS Accounts
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN EXTRANOTES AS ExtraNotes01 ON Accounts.ACCOUNTKEY = ExtraNotes01.KEF AND ExtraNotes01.NOTEID IN ('17')
LEFT OUTER JOIN EXTRANOTES AS ExtraNotes02 ON Accounts.ACCOUNTKEY = ExtraNotes02.KEF AND ExtraNotes02.NOTEID IN ('18') ON Stock.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID

WHERE
Accounts.SORTGROUP Between '0' AND '379' AND
Accounts.SORTGROUP Not Between '100' AND '150'
AND Stock.DOCUMENTID In ('1', '4', '5', '11', '78', '95')
AND Stock.STATUS Not In ('0', '2')
AND Accounts.SORTGROUP Between '0' AND '379'
AND Accounts.ACCOUNTKEY Like '313%'

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

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

TSQL Change The Position Of A Column?

Dec 16, 2004

We have a little app to help us move applications from developent --> stage --> production. It uploads the files, runs the new stored procedures etc.

One of the things it does is create new columns in existing tables. In EM you can create a new column in any position, but ALTER TABLE seems to only add columns to the end of the table. Is there a way in TSQL to create columns in arbitrary positions in the table? Or do I have to recreate the whole table?

View 2 Replies View Related

Adding Column Where Adding Year To Date In Date Format

Aug 6, 2013

What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?

View 1 Replies View Related

Adding Column To A Table Before An Existing Column

Mar 30, 2004

I simply need the ability using SQL to add columns in an existing table before (or after) columns that already exist.

The MS SQL implementation of ALTER TABLE doesn't seem to provide the before or after placement criteria I require. How is this done in MS SQL using SQL or is there a stored procedure I can use?

Thanks.

View 5 Replies View Related

Adding A Time Column To A Date Column

Jul 20, 2005

I have two columns in a table:StartDate DateTime and StartTime DateTime.The StartDate column holds a value such as 07/16/2004The StartTime column holds a value such as 3:00:00 PMI want to be able to add them in a stored procedure.When I use StartDate + StartTime I get a date two days earlier than expected.For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns7/14/2004 3:00:00 PM.Can anyone point out wht I'm doing wrong with this one?Thanks,lq

View 2 Replies View Related

Problem Returning A Timestamp Column Inside An TSQL Transaction

Jan 15, 2007

I cannot manage to fetch the new timestamp value inside a TSQL Transaction.  I have tried to Select "@LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match.  If they do, the Update begins as a Transaction.  I send @LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@NewLastChanged"):  1 select @TimestampCheck = LastChanged from ADD_Address where AddressId=@AddressId
2
3 if @TimestampCheck is null
4 begin
5 RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1) -- AddressId does not exist.
6 return -1
7 end
8 else if @TimestampCheck <> @LastChanged
9 begin
10 RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1)
11 return -1
12 end
13
14
15 Begin Tran Address
16
17 Update ADD_Address
18 set StreetNumber= @StreetNumber, AddressLine1=@AddressLine1, StreetTypeId=@StreetTypeId, AddressLine2=@AddressLine2, AddressLine3=@AddressLine3, CityId=@CityId, StateProvidenceId=@StateProvidenceId, ZipCode=@ZipCode, CreateId=@CreateId, CreateDate=@CreateDate
19 where AddressId= @AddressId
20
21 select @error_code = @@ERROR, @AddressId= scope_identity()
22
23 if @error_code = 0
24 begin
25 commit tran Address
26
27 select @LastChanged = LastChanged
28 from ADD_Address
29 where AddressId = @AddressId
30
31 if @LastChanged is null
32 begin
33 RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1)
34 return -1
35 end
36 if @LastChanged = @TimestampCheck
37 begin
38 RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1)
39 return -1
40 end
41 return 0I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?Thank you,jspurlin  

View 1 Replies View Related

TSQL Script For Finding Column Names In Stored Procedures

Feb 23, 1999

Does anyone have a TSQL utility (that they can share) that recursively searchs sysobjects for a matching input parameter string(for instance column name) for stored procedure object properties that returns the stored procedure name?

View 1 Replies View Related

How To Find Differences In Column Data Types Between Tables In Two Different Databases Using TSQL

Apr 21, 2008


I have Two Database that exist on Two seperate servers. The two database contain same schema and contains tables and columns of same name. Some tables have slight differences in terms of data types or Data type lenght.

For example if a Table on ServerA has a column named - CustomerSale with Varchar (100, Null) and a table on ServerB has a column named CustomerSale with Varchar (60, Null), how can i find if other columns have similar differences in all tables with the same name and columns in the two servers.

I am using SQL Server 2005. And the Two Servers are Linked Servers

What Script can i use to accomplish this task. Thanks






View 4 Replies View Related

Adding Date Column On First Column

May 2, 2014

I have a query like following

SET NOCOUNT OFF
SET ROWCOUNT 0
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime)
SET @EndDate = GetDate()

[Code] ....

and when i execute it, it gave a return that i expected, but then i want to add a date column on the first column.

View 5 Replies View Related

Check For Column Before Adding A Column

Oct 8, 2007

How can I test to see if a column exists before adding a column to a sql mobile table?


thanks,

Luis

View 1 Replies View Related

Adding New Column

May 5, 2008

Hi,I have an application up and running. I need to add a new column to one of the tables which is currently being used - would adding a new column change or cause errors in the current application? e.g. if the table is being accessed by selecting * from table, will adding a new column cause an error? If there is any circumstance where an error would be caused by adding a new column, I will have to create an entirely new table. If I have to do this - how do I get a column in the new table to have the same values as a column in the old table? Can I create a computed column where column=oldcolumn? Thanks,Jon 

View 5 Replies View Related

Help With Adding Column

Jun 15, 2007

hi guys! I have a table with 3 columns but i realized that i need to add 1 column between column 2 and 3. Can anybody please help me on how to do that? Thanks in advance!

View 2 Replies View Related

Adding New Column

Jul 5, 2007

ALTER TABLE BFTITLE ADD COLUMN [RATETYPE] [NUMERIC] (10) NOT NULL ;

syntax is not working so can any one tell the right syntax

View 3 Replies View Related

Adding A New Column

Mar 20, 2007

Sanjeev Dhiman writes "Sir,
How I can add a new column between existing two column...?


Sanjeev Dhiman"

View 2 Replies View Related

About Adding A New Column...

Sep 12, 2005

Suppose I have a table with the following columns: Year, SalesInEurope,SalesInAmerica, TotalSales. I want to add a new column calledSalesInAsia, say, but I want it to appear before TotalSales. How canthis be achieved?Thanks,Bruno

View 13 Replies View Related

Adding 0 To A Column

Oct 26, 2006

Hi

I have a column with times in

845

930

1015

1145

I need to update the column to look like this:

08:45

09:30

10:15

11:45

thanks in advance

rich

View 1 Replies View Related

Adding Column In Resultset From SP

Dec 27, 2006

I have a sp: mysp_getstuff it contains the following:SELECT  Adress,City FROM tblUserData WHERE UserName='john'as you can see it returns 2 columns.I also have another SP: mysp_GetNr. This sp returns an integer.I want to call mysp_getnr from mysp_getstuff and add the result to the 2 columns in a column named 'Number'So the resultset from mysp_getstuff should be:Adress, City, Number (in which the number column contains the result from mysp_GetNr)How can I do that?

View 1 Replies View Related

Adding All Values In One Column

Oct 13, 2005

I posed this problem a few days ago, but havent been able to generate the results i need. Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:Lastname      totalsales  orderID-----------------------doe               1403         510doe                500          680 doe                 200          701using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a newfield?netsports

View 8 Replies View Related







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