Joseph Michael Pesch
VP Programming

SQL Server Replace Many Characters at Once (NumberOnly Function)

by 26. March 2010 15:34

  create table #replace(val char(1))

  insert into #replace values('_')

  insert into #replace values('-')

  insert into #replace values('a')

  insert into #replace values('b')

  insert into #replace values('c')

  insert into #replace values('d')

  insert into #replace values('e')

  insert into #replace values('f')

  insert into #replace values('g')

  insert into #replace values('h')

  insert into #replace values('i')

  insert into #replace values('j')

  insert into #replace values('k')

  insert into #replace values('l')

  insert into #replace values('m')

  insert into #replace values('n')

  insert into #replace values('o')

  insert into #replace values('p')

  insert into #replace values('q')

  insert into #replace values('r')

  insert into #replace values('s')

  insert into #replace values('t')

  insert into #replace values('u')

  insert into #replace values('v')

  insert into #replace values('w')

  insert into #replace values('x')

  insert into #replace values('y')

  insert into #replace values('z')

  declare @tmp varchar(100)

  select @tmp = '-_abcdefghijklmnopqrstuvwxyz0123456789'

  select @tmp = replace(@tmp, val, '') from #replace

  select @tmp

  drop table #replace

Tags:

SQL Server

SQL Random (rand) Number Generator

by 18. March 2010 13:53

  declare

    @RandomNumber float

  , @RandomInteger int

  , @MaxValue int

  , @MinValue int

 

 

  set @MaxValue = 6

  set @MinValue = 1

  set @RandomNumber = RAND()

  set @RandomInteger = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValue

 

 

 

  select @RandomNumber as RandomNumber

       , @RandomInteger as RandomInt

       , @RandomInteger + round(@RandomNumber, 3) as RandomDecimal

 

Sample results running above SQL inside 10 iteration loop:

 

Tags:

SQL Server

SQL Table/Column Schema Select

by 26. February 2010 15:28

select
  sch.name [Schema Name]
, tbl.name [Table Name]
, col.name [Column Name]
, typ.name
  + case when col.system_type_id = 167 then ' (' + convert(varchar, col.max_length) + ')' else '' end
  + case when col.precision + col.scale <> 0 then ' Precision: ' + convert(varchar, col.precision) + '; Scale: ' + convert(varchar, col.scale) else '' end
  [Column Type]
, case col.is_nullable when 1 then 'true' else 'false' end [Allow Null]
from sys.tables tbl
inner join sys.schemas sch
on tbl.schema_id = sch.schema_id
inner join sys.columns col
on tbl.object_id = col.object_id
inner join sys.types typ
on col.system_type_id = typ.system_type_id
where tbl.type = 'U'
order by
  sch.name
, tbl.name
, col.name
 

Tags:

SQL Server

SQL Server Common Table Expression (CTE)

by 12. November 2009 18:00

Must have semi-colon on last statement before the "WITH" keyword that begins the Common Table Expression (see sample below on "select @Dummy = 0;" line).  Multiple CTE's must be comma delimited under the single "WITH" keyword (i.e. only the first CTE begins with the "WITH" statement, the subsequent ones begin with a comma).

declare

  @BegDate datetime

, @EndDate datetime

, @Dummy int

 

select

  @BegDate ='2/1/2009'

, @EndDate ='2/28/2009 11:59PM'

 

select @Dummy = 0;-- Prepare for the WITH CTE statement

 

with HistoryMax(ModificationID, HistoryDate)as

(

  select ModificationID,max(HistoryDate) HistoryDate

  from LMM.TermsHistory

  where HistoryDate <= @EndDate

  groupby ModificationID

)

, TermSnap(HistoryID)as

(

  select HistoryID

  from LMM.TermsHistory h

  innerjoin HistoryMax hm on h.ModificationID = hm.ModificationID

  and h.HistoryDate = hm.HistoryDate

)

, FilterSet(ModificationID, ModificationStatusCodeDate)as

(

  select ModificationID,max(ModificationStatusCodeDate) ModificationStatusCodeDate

  from LMM.TermsHistory

  where ModificationstatusCode = 600

  groupby ModificationID

)

 

select

  datename(month, h.ModificationStatusCodeDate) ClosedMonth

,sum(isnull(fc1.FeeAmount, 0)+isnull(fc2.FeeAmount, 0)) FeeCollected

,sum(isnull(fp1.FeeAmount, 0)+isnull(fp2.FeeAmount, 0)) FeePending

from

  LMM.TermsHistory t

  innerjoin TermSnap ts

  on t.HistoryID = ts.HistoryID

  innerjoin FilterSet h

  on t.ModificationID = h.ModificationID

  leftouterjoin LMM.ModCalcFee fc1

  on fc1.RecordTypeCode = 100

     and fc1.FeeCollectedFlag = 1

     and t.ModificationID = fc1.ModificationID

  leftouterjoin LMM.ModCalcFee fc2

  on fc2.RecordTypeCode = 200

     and fc2.FeeCollectedFlag = 1

     and t.ModificationID = fc2.ModificationID

  leftouterjoin LMM.ModCalcFee fp1

  on fp1.RecordTypeCode = 100

     and fp1.FeeCollectedFlag = 0

     and t.ModificationID = fp1.ModificationID

  leftouterjoin LMM.ModCalcFee fp2

  on fp2.RecordTypeCode = 200

     and fp2.FeeCollectedFlag = 0

     and t.ModificationID = fp2.ModificationID

where

  h.ModificationStatusCodeDate between @BegDate and @EndDate

  and t.ModificationStatusCode between 600 and 899

groupby

  datename(month, h.ModificationStatusCodeDate)

 

Tags: ,

SQL Server

SQL 2005 Select Into Variable with Multiple Rows

by 10. November 2009 14:50

When using a select statement to set a variable, and the select statement returns multiple rows, the last row returned will be the value stored in the variable. You can use sorting or a TOP statement to change this behaviour (as shown in samples below).
 

createtable #tmp(val int) 

insertinto #tmp values(1)
insertinto #tmp values(2)
insertinto #tmp values(3)
insertinto #tmp values(4)
insertinto #tmp values(5) 

declare @val int

selecttop 1 @val = val
from #tmp
orderby val asc select @val /* returns 1 */ 

select @val = val
from #tmp
orderby val asc select @val /* returns 5 */ 

select @val = val
from #tmp
orderby val desc select @val /* returns 1 */ 

droptable #tmp

Tags: ,

SQL Server

Compare Two Tables in SQL Server

by 21. October 2009 16:26

Code excerpt from: http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

 

 

 

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...

FROM

(

  SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...

  FROM A

  UNION ALL

  SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...

  FROM B

) tmp

GROUP BY ID, COL1, COL2, COL3 ...

HAVING COUNT(*) = 1

ORDER BY ID

Tags: ,

SQL Server

SQL Beginning of Day and End of Day (getdate() Date Only)

by 16. August 2009 21:05

declare
@ProcessDate datetime
, @ProcessDateMin datetime
, @ProcessDateMax datetime

set @ProcessDate =getdate()

set @ProcessDateMin =convert(datetime,floor(convert(float, @ProcessDate)))

set @ProcessDateMax =dateadd(hour, 23,dateadd(minute, 59,dateadd(second, 59, @ProcessDateMin)))


select @ProcessDate ProcessDate, @ProcessDateMin ProcessDateMin, @ProcessDateMax ProcessDateMax

ProcessDate             ProcessDateMin          ProcessDateMax
----------------------- ----------------------- -----------------------
2009-08-16 15:18:16.233 2009-08-16 00:00:00.000 2009-08-16 23:59:59.000

Tags: ,

SQL Server

SQL Audit Table Methodology

by 6. August 2009 21:35

Tags: ,

SQL Server

SQL Server 2005 Pivot Table

by 7. April 2009 14:21

declare @example table

(

  GroupID integer

, ItemID  integer

, Value1  varchar(10)

, Value2  varchar(10)

)

insertinto @example

select 1, 1,'First',   'James'     union all

select 1, 2,'Last',    'Smith'     union all

select 1, 3,'Country','UK'        union all

select 1, 4,'Hobby',   'Rugby'     union all

select 2, 1,'First',   'Alan'      union all

select 2, 2,'Last',    'Jackson'   union all

select 2, 3,'Country','USA'       union all

select 2, 4,'Hobby',   'Fishing'

--select * from @example

 

select

  GroupID

,max([First])+' '+max([Last])as [Name]

,max([Country])as Country

,max([Hobby])as Hobby

from

  @example

pivot

(

  max(Value2)for Value1 in([First], [Last], [Country], [Hobby])

) pvt

groupby GroupID

 

/*

GroupID     Name         Country Hobby

----------- ------------ ------- ----------

1               James Smith   UK        Rugby

2               Alan Jackson USA       Fishing

*/

 

Tags: ,

SQL Server

SQL GoTo Statement

by 13. March 2009 18:06

declare @i int, @var int set @i = 1
while @i <= 10 begin
  printconvert(varchar(10), @i)+': while'

set @var = 1 goto Code1 ContinueWhile:


set @i = @i + 1
end
goto EndProcess Code1:

printconvert(varchar(10), @i)+': Code: '+convert(varchar(10), @var)
if @var < 5 begin
set @var = @var + 1 goto Code1 end


goto ContinueWhile EndProcess:

print'Done'

Tags: ,

SQL Server

SQL Cube and Rollup Samples on Northwind Database

by 13. February 2009 21:19

Tags: ,

SQL Server

TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.

by 11. February 2009 14:27

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.)

This error was encountered on a web application running a batch upload of records into a SQL server database.  Each iteration in the master batch upload process contained approximately 50+ individual SQL transactions.  The issues appears to be that the web server was running out of dynamic ports due to the large volume of individual connections to the SQL server. 

http://support.microsoft.com/kb/328476

Adjust the MaxUserPort and TcpTimedWaitDelay settings

Note that the MaxUserPort and TcpTimedWaitDelay settings are applicable only for a client computer that is rapidly opening and closing connections to a remote computer that is running SQL Server and that is not using connection pooling. For example, these settings are applicable on an Internet Information Services (IIS) server that is servicing a large number of incoming HTTP requests and that is opening and closing connections to a remote computer that is running SQL Server and that is using the TCP/IP protocol with pooling disabled. If pooling is enabled, you do not have to adjust the MaxUserPort and TcpTimedWaitDelay settings.
 

The following changes to the registry key are used to override the default values:           HKEY_LOCAL_MACHINE\System\CurrectControlSet\services\Tcpip\Parameters            Value Name: MaxUserPort          Data Type: REG_DWORD          Value: 30000 (Decimal)           Value Name: TcpTimedWaitDelay           Data Type: REG_DWORD          Value: 30 (Decimal)

 

Tags: ,

ASP.Net | SQL Server

SQL Server Raise Error (raiserror)

by 10. December 2008 14:23

raiserror
(
  N'There is already an active Modification record for Servicer Loan Number: %s, you must close that modification before starting a new one.',
  16,-- Severity.
  1,-- State.
  @ServicerLoanNumber -- substitution argument.
)

NOTE: Use %s for string substitution, %n for number substitution, etc.

Tags:

SQL Server

SQL Server Date Format via Convert Function

by 3. December 2008 16:47

To get month abbreviation + year (e.g. 'Jan 2009'): selectreplace(convert(varchar(11),getdate()+5, 106),right('0'+convert(varchar(2),datepart(day,getdate()+5)), 2)+' ','')

To get date range of today:

select * from sometable where somedate between convert(varchar(10), getdate(), 101) and convert(datetime, (convert(varchar(10), getdate(), 101) + ' 23:59:59.998'))

 

Standard Date Formats
Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)
Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972
DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998
DD Mon YY 1 - SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1
DD Mon YYYY 1 - SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1
Mon DD, YY 1 - SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1
Mon DD, YYYY 1 - SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1
HH:MM:SS - SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default +
milliseconds
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006
YY/MM/DD - SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23
YYYY/MM/DD - SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23
YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H) - SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical
(with milliseconds)
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM

 

Extended Date Formats
Date Format SQL Statement Sample Output
YY-MM-DD
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]
99-01-24
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]
1999-01-24
MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]
08/99
MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] 12/2005
YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] 99/08
YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] 2005/12
Month DD, YYYY 1 SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] July 04, 2006 1
Mon YYYY 1 SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] Apr 2006 1
Month YYYY 1 SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] February 2006 1
DD Month 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month] 11 September 1
Month DD 1 SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] September 11 1
DD Month YY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] 19 February 72 1
DD Month YYYY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] 11 September 2002 1
MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]
12/92
MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] 05-2006
YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]
92/12
YYYY-MM SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] 2006-05
MMDDYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY] 122506
MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY] 12252006
DDMMYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY] 240702
DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY] 24072002
Mon-YY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY] Sep-02 1
Mon-YYYY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY] Sep-2002 1
DD-Mon-YY 1 SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY] 25-Dec-05 1
DD-Mon-YYYY 1 SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY] 25-Dec-2005 1

1 To make the month name in upper case, simply use the UPPER string function.

Tags:

SQL Server

Oracle Equivelant of SQL sysobjects and syscolumns

by 22. October 2008 15:36

Tables: all_tables Columns: all_tab_columns

 

Tags:

Oracle | SQL Server

SQL Transfer Object Between Schemas

by 14. September 2008 02:36

alterschema NewSchemaHere transfer ExistingSchemaHere.ObjectNameHere

Example: alter schema dbo transfer jpesch.tbl_SomeTable

This would transfer the table (tbl_SomeTable) from jpesch to dbo

 

Tags:

SQL Server

SQL Server Convert UTC to Local Time

by 27. August 2008 19:06

The following SQL statement assumes you have a variable named @UTCDate that contains a UTC date value, the result will be the conversion of that UTC date value into a local time zone value (based on the server configuration settings from which you are running the statement).

select dateAdd(hour, datediff(hour, getutcdate(), getdate()), @UTCDate)

Side Note: ASP.Net Authentication, Workflow Foundation Persistence, etc. stores dates (such as aspnet_Membership.LastLoginDate, aspnet_Users.LastActivityDate, dbo.nextTimer, etc. in UTC time).

Tags:

SQL Server

SQL Server 2005 Authentication Mode

by 15. August 2008 14:44

Youcan change dht authentication mode between "Windows Authentication" and "Mixed Mode" after installation by changing a single registry value (shown below).  Value of 1 = "Windows Authentication Mode" value of 2 = "Mixed Authentication Mode".

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer]
"LoginMode"=dword:00000001

Additionally, to enable the sa login by using Transact-SQL
Execute the following statements to enable the sa password and assign a password.

ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = '';
GO

To enable the sa login by using Management Studio
In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
On the General page, you might have to create and confirm a password for the sa login.
On the Status page, in the Login section, click Enabled, and then click OK.

Tags:

SQL Server

SQL Server Identity Columns vs. Oracle Sequence Numbers

by 12. August 2008 21:28

In SQL server you create an identity column like the sample below which creates an identity column with seed value of 1 and increment value of 1.  Then when you insert records the column value is incremented and set automatically.

SQL Server Sample:
create table #MyTable(RecID int identity(1,1), MyData varchar(50))
insert into #MyTable(MyData) values('This is a test')
insert into #MyTable(MyData) values('This is another test')
select * from #MyTable
drop table #MyTable

Not so in Oracle.  There you create the integer column; however, you must manually increment and set the column value as part of your insert statement.  You can use a Sequence object (after you create one) as shown below.

create sequence MySequence minvalue 1 maxvalue 999999999999 start with 1 increment by 1 cache 20
create table imdw.MyTable(RecID int, MyData varchar2(50))
insert into imdw.MyTable(RecID, MyData) values(MySequence.nextval, 'This is a test')
insert into imdw.MyTable(RecID, MyData) values(MySequence.nextval, 'This is another test')
select * from imdw.MyTable
drop table imdw.MyTable
drop sequence MySequence

In both cases you should get the following result set:

 RecID MyData 
 1 This is a test 
 2 This is another test

Tags:

Oracle | SQL Server

SQL Server Split Function

by 12. August 2008 18:39

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (recid int identity(1,1), item VARCHAR(8000))
BEGIN
-- Adapted from: http://searchvb.techtarget.com/tip/0,289483,sid8_gci932171,00.html
-- Added recid (identity column)
-- Tests
/*
  select * from fnSplit('12345', ',')
  select * from fnSplit('1,22,333,444,,5555,666', ',')
  select * from fnSplit('1##22#333##444','##')  --note second item has embedded #
  select * from fnSplit('1 22 333 444  5555 666', ' ')
*/
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END
IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO


Sample Usage via a Cursor:


declare
@recid int
, @item varchar(8000)
, @SampleData varchar(50)
set @SampleData = 'Item1,Item2,Item3,Item4,Item5'
declare split cursor for select * from dbo.fnSplit(@SampleData, ',')
open split
while 1=1 begin
fetch next from split into @recid, @item
if @@fetch_status <> 0 break
-- Do something here...
select @recid, @item
end
close split
deallocate split

Tags:

SQL Server