Single column table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Single column table

Author

Topic

Stumbler
Starting Member

23 Posts

Posted - 2009-04-19 : 08:06:42

I use a single column table to store an identity value for a ticket.
How can I add a row to that this table?

This is my table:


USE [myDB]
GO
/****** Object:  Table [dbo].[ticket_number]    Script Date: 04/19/2009 14:03:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ticket_number](
	[ticket] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

I want to add a row with a stored procedure.
Who can help me?

Hans, Belgium

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-19 : 08:12:34

That makes no sense.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Stumbler
Starting Member

23 Posts

Posted - 2009-04-19 : 08:19:21

quote:
Originally posted by webfred

That makes no sense.

Greetings
Webfred

Why?
I would use this technique to make sure that i create unique ticket numbers in a multi user invironnement. The ticket number is than used in other tables.

Hans, Belgium

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-19 : 08:28:34

You can not insert a value into column ticket because you want it to generate an automatic number.
So you need a second column to get able to insert a value.
For example:


CREATE TABLE [dbo].[ticket_number](
	[ticket] [int] IDENTITY(1,1) NOT NULL,
        [ticket_text] varchar(10) NULL
)

insert ticket_number
select 'Test'

This insert-statement will insert the value Test into column ticket_text while column ticket is automatic incremented.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Stumbler
Starting Member

23 Posts

Posted - 2009-04-19 : 09:58:27

Thanks for the tip.

Hans, Belgium

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2009-04-19 : 10:23:48

quote:
Originally posted by Stumbler

I use a single column table to store an identity value for a ticket.
How can I add a row to that this table?

Actually, it is possible to insert single rows into such a table:

INSERT [dbo].[test_number] DEFAULT VALUES

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-19 : 10:24:59

No need to add a second column.
See the use of "DEFAULT VALUES".

CREATE TABLE	dbo.TicketNumber
		(
			TicketID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
		)
GO

INSERT	dbo.TicketNumber
DEFAULT VALUES

SELECT	SCOPE_IDENTITY()

SELECT	*
FROM	dbo.TicketNumber
GO 5

--DROP TABLE	dbo.TicketNumber
GO

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-19 : 10:25:35

Thanks

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources