I have a Folder table which contain the following columns:
id | name | state | imageFolder -------------------------------------------1 NameTest 1 2 And I have another one called Images which contains:
id | url -------------------------------------------- 2 https://www.someImageUrlI want to insert an url in the folder table in a query and put it in the images and get the id in the same query.
Actually, I am working on an Android project, I have to add a new folder and upload an image to it so that’s why I need this.
Is there a way to do it?
Advertisement
Answer
You’ll want to use SCOPE_IDENTITY() to capture the key value being generated by the IMAGE table. Some people use @@IDENTITY, do not do that, it has a global scope and you can sometimes get back the wrong ID.
Then use that value to insert into the FOLDER table. It feels a little backwards to insert into the IMAGE table first but that’s how you get the ID you want. Here is a fully functional example:
IF OBJECT_ID('IMAGE') IS NOT NULL DROP TABLE [IMAGE]CREATE TABLE [dbo].[IMAGE]( [id] [int] IDENTITY(1,1) NOT NULL, [url] [varchar](50) NOT NULL, CONSTRAINT [PK_IMAGE] 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]GOIF OBJECT_ID('FOLDER') IS NOT NULL DROP TABLE [FOLDER]CREATE TABLE [dbo].[FOLDER]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NOT NULL, [state] [int] NOT NULL, [image_id] [int] NOT NULL, CONSTRAINT [PK_FOLDER] 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]GODECLARE @IMAGE_ID INTINSERT INTO IMAGE(url)VALUES ('http://www.example.com')SET @IMAGE_ID = SCOPE_IDENTITY() --This is the key partINSERT INTO FOLDER(NAME, STATE, IMAGE_ID)VALUES ('NameTest', 1, @IMAGE_ID)GOSELECT * FROM IMAGESELECT * FROM FOLDERResults:
