I have a temp table that's populated with an insert query in as tored
procedure. The temp table has a uniqueID as the primary key.
In that table I have a column SortOrder.
What I want to do is to create a sequential number in SortOrder but
only for records matching a WHERE statement, for example:
(pardon the shorthand...)
Insert *.tblPermanent into tblTemp
If myField = 1 then
SortOrder = 1(2,3,4,5,.....etc.)
else
SortOrder = 0
Thanks
lq
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
What I need to do is add a lot number for each acct/trans code type. This is merely a sequential number for the transaction. This changes the output as follows:
I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.
I am using sybase aSE12.5. I have a table with only one column withdatatype char(2). when i query this table to select all the records, ishould get these records in the ascending order and they should be numbered, i.e, the o/p should look something like thiscolumn_name------ --------1 AB2 AC3 ADand so on.I cannot add an extra column and i need this to be done in a single query.--Message posted via http://www.sqlmonster.com
I have a column within a table which is already truncated/deleted all records within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records arbitrary numbers (doesn't mater) up to 7 characters.
what SQL statement I need to write that will automatically polulate the newly empty table with A000001,A0000002,A0000003, or any form for that matter etc so that I can sort number the records within the table.
I have approximately 50000 records which I need to sequentially entered and I really dont want to number the column manually via hand editing.
I have a database that is pre-populated with sequential part numbers.As people reserve the parts I update a flag to show the # is no longeravailable. Now they want the ability to take out a block of "x"number of sequential part numbers - say for example 5.If my database had the following numbers available:101104105110111112113114It should return 110 thru 114 and then I would write an update queryto change the flags to 1 (checked out).I have only been able to return the first "x" number of records - havenot been able to make sure they are stepped sequentially - with thefollowing:SELECT ID_ITEM From PARTNO_CHKOUT_SPECIAL M Where (Select Count(*)FROM PARTNO_CHKOUT_SPECIAL NWHERE N.ID_ITEM <= M.ID_ITEM) >= 0 AND TYPE_REC=1 ANDFLAG_CHECKED_OUT=0 {maxrows 5}The above would return 101, 104, 105, 110, 111I tried using an (N.ID_ITEM+1)-M.ID_ITEM=0 to try stepping and geterrors, probably incorrect syntax. Can I do this in an SQL statement?
I'm trying to create a column of numbers that increment by one.
I'm not able to use a #temptable in the application I'm using so I cannot use IDENTITY(int,1,1).
I want to add an Id column to this query:
Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd cross join INFORMATION_SCHEMA.COLUMNS ic where sd.name = 'ODS1stage' order by TABLE_SCHEMA,TABLE_NAME
How can I accomplish this without creating a temp table? I would just alter the table and insert the numbers but there are 2000 rows.
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Hi all,I might be getting this all wrong but bear with me. I need to create some kind of Unique field in my DB that is nonsequential. This is because I need it to be difficult to guess ids if you have an example in front of you.I have looked at 8digit EAN codes which include a check digit system.( I use a base digit of the row_id for these) Can anyone tell me how many uniques I can get out of this system?For my ID: I have looked at something along the lines of:
Hex(row_id) + "T" + Hex( Trimmed(EAN) ) The "T" serves to split the numbers for when I am converting back. So for example:row_id EAN_code Hex(row_id) + "T" + Hex( Trimmed(EAN) ) ------------------------------------------------------------------------------------------ 3166 00031663 C5ET7BAF 3167 00031673 C5FT7BB9 3168 00031686 C60T7BC6
Is this too easy to guess (once you can tell there are two hex numbers there?) What do people think? Thanks,Pete
I have a table which is already populated with data (Microsoft SQL 2008). I have now created a new column (int) which i want to populate with sequential numbers so that the new column created will serve let me know how many records exist in the table at a glance.
what SQL statement I need to write that will automatically polulate the newly created column with 1,2,,3,4,5 etc so that I can sort of number the records within the table.
I have 50000 records which I need to number and I really dont want to number the column manually via hand editing.
I'm trying to find a decent way of multiplying a set of numbers in a column without using a cursor in T-SQL.
There is no 'Product' aggregate function that I'm aware of in SQL 7 or 2000. The workaround I'm currently using is this :
SELECT EXP(SUM(LOG(ColumnName))) FROM tblName
This works fine, except when negative numbers are introduced. The LOG function does not allow negative numbers and therefore returns a domain error and the negative number is eliminated from the aggregate.
I could use a cursor to do the multiplication, however, this is proving too slow for the bulk calculations involved.
If anyone has any ideas or suggestions, then that would be much appreciated.
Insert INTO #EmployeeList('Cary zzz',null); Insert INTO #EmployeeList('01 Jack',null); Insert INTO #EmployeeList('02 Tommy',null); Insert INTO #EmployeeList('03 Ricardo',null); Insert INTO #EmployeeList('04 Jack',null); Insert INTO #EmployeeList('Les zzz',null); Insert INTO #EmployeeList('05 Tim',null);
The final data looks like this :
Cary zzz NULL 01 Jack NULL 02 Tommy NULL 03 Ricardo NULL 04 Jack NULL Les zzz NULL 05 Tim NULL
1. I want to delete all rows which have 'zzz' in it. 2. I want to remove the numbers from the empname column
Code Block Expected Output :
Jack NULL Tommy NULL Ricardo NULL Jack NULL Tim NULL
I have a simple question to ask; I need to create a column with the data from my DOB column (which has the smalldatetime type attached to it). I know how to do that but I am not too sure how to convert the data from that column int normal character for example when I copy it into my newly created column and change the type to varchar I get this jan 16 1979 from this date 1979/01/16. But I actually want the data to look like this 19790116, so in effect I just want to take out the slashes.
how do I gradually add o substract numbers in a column for example if I have
total deposito test
120
120
80 40
77
117
7
124
4 120
i need in the test column: if the number is in the total column the number in test is added if the number is in the deposito column the number in test is substracted
i have this query:
SELECT gir_cantidad as total,null as deposito,'operation' as test
FROM giros inner join corresponsales on cor_corresponsal_id = gir_corresponsal_id where gir_fecha >= '11/21/2007' and gir_fecha <= '11/22/2007 23:59:59' and gir_fecha_anul is null and gir_agencia_id = 1
UNION
select null as total,paa_valor as deposito,'operation' as test from pagosagencia where paa_fecha >= '11/21/2007' and paa_fecha <= '11/22/2007 23:59:59' and paa_agencia_id= 1 order by gir_fecha
In column test i need the functionality to add and subtract the columns total and deposito
After moving our database between servers, one table had a hiccup in it's identity column. The number jumped from 761 to 1886863475 on the next insert. This is a production server and I didn't catch it until yesterday. So I have several days worth of numbers in a central table. I can't clean them out, but I wondered if I could do the following:
DBCC CHECKIDENT ('table_name', RESEED, 800)
I tested it on our development server and it doesn't seem to cause any problems. I know that when we get to 1.8 billion on the identity column again we'll get an error, but I'm willing to risk it.
A few questions. 1) Am I insane for trying this? 2) Has anyone else ran into a similar problem and what did you do to fix it? 3) If I do this, is there anything in particular to watch out for? 4) What caused the jump in the first place?
My other option is to change the datatype from int to decimal(19) or something along those lines. This will upset our programmers and I'll have to change all my foreign keys (not a big deal, just a pain).
I have a table of id numbers that I wish to mask. My thought was to create a new column for this new id number and populate it with a unique sequential value - start at 1 and go as high as needed. My problem is that I cannot recall how to populate that column with a number...
I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week.Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server e.g,
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
I want to grab the resultant pivoted table back into excel. how to do it?
USE [Database_ABC] GO DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
One more question about this Custom Calendar table I'm creating. I have a column called "IsWorkdays" which indicates if the day represented by a row is a workday or not. For our purposes, I also need to create a row that accumulates those numbers by month. So, if it is the 3rd workday of the month, this column would have a 3. This is beyond my current T-SQL ability. Does anyone know how to do this?
