Prev: NORMDIST in T/SQL
Next: creating my sp_help
From: Michael Cole on 14 Jul 2010 03:57
(Firstly, no critisms of the design - its not mine and its what I have
to work with)

I have the following: -

CREATE TABLE [dbo].[Counter](
[LastIndex] [int] NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Values](
[Value] [VarChar] (5) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[IndexedValues](
[Index] [int] NULL,
[Value] [VarChar] (5) NULL
) ON [PRIMARY]

GO

INSERT INTO [Counter] ([LastIndex]) VALUES (0)
INSERT INTO [Values] ([Value]) VALUES ('a')
INSERT INTO [Values] ([Value]) VALUES ('b')
INSERT INTO [Values] ([Value]) VALUES ('c')

SELECT * FROM [Counter]
(Returns 1 row of LastIndex = 0)
SELECT * FROM [Values]
(Returns 3 rows of Value = a, b, c)
SELECT * FROM [IndexedValues]
(Returns no rows)

I want a single line INSERT INTO [IndexedValues] query that will also
update the [Counter] table such that

SELECT * FROM [Counter]
(Returns 1 row of LastIndex = 3)
SELECT * FROM [Values]
(Returns 3 rows of Value = a, b, c)
SELECT * FROM [IndexedValues]
(Returns 3 rows of {Index,Value} = {1,a}, {2,b}, {3,c})

Something like

INSERT INTO [IndexedValues]
SELECT
(UPDATE [Counter] SET [Index] = [Index] + 1 OUTPUT
INSERTED.[Index])
, [Value] FROM [Values]

but one that works.

Does anyone have any ideas here?

--
Michael Cole


From: fromeo on 14 Jul 2010 09:50
Hi,

You need to look at using Composable DML.
Here's a useful example:

http://weblogs.sqlteam.com/peterl/archive/2009/04/08/Composable-DML.aspx

> Firstly, no critisms of the design - its not mine and its what I have to work with

Point taken.
However, it's never a good idea to use keywords for columns and tables
i.e. VALUES, INDEX etc.

F.
 | 
Pages: 1
Prev: NORMDIST in T/SQL
Next: creating my sp_help