INSERT INTO | Rocket Forum

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

I'm a little confused about the statement "i need the JobId for selecting in another table". Is JobId a key assigned an artificial incrementing value? If it has just been created on-the-fly by your insertion, how can it already exist to be "selected" in another table?

In terms of determining the new value just inserted for JobId, is the inserted JobID getting a new value, higher than any existing value for that column? If so, and assuming that there is no COMMIT automatically performed after your INSERT, you might try querying the table to determine the MAX value for JobId, with something like this:

Select max(JobId) from jobs

into :my-new-jobid;

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

You can not automatically return the JobId because it is defined as a unique_identifier and not an IDENTITY column.

If you specified it as an identity column instead then you could find out the value inserted using

exec sql SELECT @@IDENTITY into :my-new-jobid end-exec

or

exec sql SELECT SCOPE_IDENTITY() into :my-new-jobid end-exec

An article that I found that explains it pretty well can be found here:

If you created your insert statement as a stored procedure and called it then you could return this information using the OUTPUT phrase of the INSERT statement.

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

Hallo Chris,

the articel is really very pretty, but how can i code the declare in cobol?

DECLARE @GUID uniqueidentifier

SET @GUID = NEWID()

INSERT Item VALUES (@GUID,'Yak Hoof')

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

Hallo Chris,

the articel is really very pretty, but how can i code the declare in cobol?

DECLARE @GUID uniqueidentifier

SET @GUID = NEWID()

INSERT Item VALUES (@GUID,'Yak Hoof')

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

Hallo Blair,

the insert does not work because:

The uniqueidentifier is invalid for the max-operator

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

You could use dynamic sql and a temporary table to do this.

Something like this:

01 sql-statement pic x(100) value spaces.
01 guid-field pic x(50) value spaces.

move "create table #mytemp (GUID uniqueidentifier)"
to sql-statement

exec sql execute immediate :sql-statement end-exec
exec sql insert into #mytemp values (NEWID()) end-exec
exec sql select GUID into :guid-field from #mytemp end-exec

After this the host variable guid-field will contain the unique guid that you can then insert into your table and will then be available to other statements as well.

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

Hallo Chris,

thank you very much!

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

Hallo Chris,

thank you very much!

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

Hallo Chris,

thank you very much!

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

can you point me to an example of the OUTPUT phrase for the Insert into for embedded SQL? I'm inserting a record with an identity column and then wan to to inset records into other table with the identity value as a field so I can view them linked.

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

can you point me to an example of the OUTPUT phrase for the Insert into for embedded SQL? I'm inserting a record with an identity column and then wan to to inset records into other table with the identity value as a field so I can view them linked.

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

This example works in my environment.

exec sql delete VER01_Key *> Delete Record in Key-Table

end-exec.

EXEC SQL INSERT VER01

OUTPUT INSERTED.Nummer

INTO VER01_Key

VALUES (98765, 'Test OUPUT')

END-EXEC.

Table VER01 has 2 fields: Nummer (int), Name varchar(50).

In a multi user/tasking environment you should add a unique task-id field to the key table.

Werner Lanter

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

Do you know if it is possible to return the OUTPUT value to a host variable in the program, or does it have to go to a SQL variable, and then you do a SELECT on that?

INSERT INTO VER01

OUTPUT INSERTED.Nummer INTO :WS-NUMMER

VALUES (98765, 'Test OUTPUT')

I have tried this and I am getting a weird SQL error.

I have the follwing question:

I have to insert a new row into the table JOBS and then i need the JobId for selecting in another table:

DDL of JOBS

USE [IBOS2_UniDex]
GO
/****** Object: Table [dbo].[Jobs] Script Date: 02/03/2015 14:18:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Jobs](
[JobId] [uniqueidentifier] NOT NULL,
[Sender] [nvarchar](max) NULL,
[Recipient] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.Jobs] PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Default [DF__Jobs__JobId__1A14E395] Script Date: 02/03/2015 14:18:04 ******/
ALTER TABLE [dbo].[Jobs] ADD DEFAULT (newsequentialid()) FOR [JobId]
GO

Jobs in my program:

EXEC SQL DECLARE
Jobs TABLE
( JobId uniqueidentifier(36)
NOT NULL
,Sender ntext(41823)
,Recipient ntext(41823)
) END- EXEC.

on inserting a new job i declare:
EXEC SQL
INSERT INTO Jobs
(JobId
,Sender
,Recipient
) VALUES
(NEWID()
,:Jobs- Sender:Jobs- Sender- NULL
,:Jobs- Recipient:Jobs- Recipient- NULL
)
END- EXEC

after this, how can i find out the JobId, beacause the insert statement does not give back the JobId.

INTO in the OUTPUT clause points to a database table. Here my complete example:

CREATE TABLE [dbo].[Identity_Nummer](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Nummer] [int] NOT NULL,

[Name] [varchar](50) NOT NULL,

CONSTRAINT [PK_Identity_Nummer] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

*> create temporary table (once after database connectiion)

exec sql

CREATE TABLE #Identity_ID(ID int NOT NULL)

end-exec

.....

exec sql delete #Identity_ID *> delete existing records if any

end-exec.

EXEC SQL INSERT Identity_Nummer (Nummer, Name)

OUTPUT INSERTED.ID INTO #Identity_ID (ID)

VALUES (0, :Str)

END-EXEC.

exec sql select ID

into :RecID

from #Identity_ID

end-exec.

Integer RecId contains the value of the IDENTITY column ID in table Identity_Nummer.

Werner Lanter

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register:

Scanning file for viruses.

This file cannot be downloaded