Skip to main content

INSERT INTO

  • February 3, 2015
  • 14 replies
  • 0 views
D

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.

    14 replies

    Blair McDonald
    Forum|alt.badge.img+1

    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;

    Chris Glazier
    Forum|alt.badge.img+2

    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.

    D

    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')

    D

    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')

    D

    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

    Chris Glazier
    Forum|alt.badge.img+2

    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.

    D

    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!

    D

    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!

    D

    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!

    D

    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.

    D

    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.

    Lanter Werner
    • Lanter Werner
    • Participating Frequently
    • April 20, 2015

    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

    D

    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.

    Lanter Werner
    • Lanter Werner
    • Participating Frequently
    • June 3, 2015

    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

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement