CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

How to insert data in table using Insert Query from values provided in parameters

I have this table structure and I want to write Insert Query that'll insert data into the table from the values provided in parameters

CREATE TABLE [dbo].[EMPLOYEE](
    [ID] [int] NULL,
    [EMPLOYEE_NAME] [varchar](50) NULL,
    [DEPARTMENT_ID] [int] NULL,
) ON [PRIMARY]

DECLARE @ID VARCHAR(20) = '1, 2';
DECLARE @Name VARCHAR(50) = 'Asim Asghar, Ahmad'
DECLARE @DeptID VARCHAR(20) = '5, 12';

INSERT INTO EMPLOYEE VALUES (@ID, @Name, @DeptID)

Based on the data provided above it should add 4 rows with following data 

1  Asim Asghar  5
2  Ahmad        5
1  Asim Asghar  12
2  Ahmad        12

Hope someone can help

Upvotes: 0

Views: 1829

Answers (3)

Thangadurai.B
Thangadurai.B

Reputation: 561

Try this, You need this function for splitting by char using dynamic delimiter.

CREATE FUNCTION UDF_SPLIT_BY_CHAR(@STRING VARCHAR(8000), @DELIMITER CHAR(1))     
RETURNS @TEMPTABLE TABLE (S_DATA VARCHAR(8000))     
AS     
BEGIN     
        DECLARE @IDX INT=1,@SLICE VARCHAR(8000)     

        IF LEN(@STRING)<1 OR @STRING IS NULL  RETURN     

        WHILE @IDX<> 0     
        BEGIN     
            SET @IDX = CHARINDEX(@DELIMITER,@STRING)     
            IF @IDX!=0     
                SET @SLICE = LEFT(@STRING,@IDX - 1)     
            ELSE     
                SET @SLICE = @STRING     

            IF(LEN(@SLICE)>0)
                INSERT INTO @TEMPTABLE(S_DATA) VALUES(@SLICE)     

            SET @STRING = RIGHT(@STRING,LEN(@STRING) - @IDX)     
            IF LEN(@STRING) = 0 BREAK     
        END 
    RETURN     
END

Declare @EMPLOYEE TABLE
(
    [ID] [int] NULL,
    [EMPLOYEE_NAME] [varchar](50) NULL,
    [DEPARTMENT_ID] [int] NULL
)

DECLARE @ID VARCHAR(20) = '1, 2'
        ,@Name VARCHAR(50) = 'Asim Asghar, Ahmad'
        ,@DeptID VARCHAR(20) = '5, 12';

    insert into @EMPLOYEE
    (
        [ID],[EMPLOYEE_NAME],[DEPARTMENT_ID]
    )
     Select a.S_DATA,b.S_DATA,c.S_DATA
       from dbo.UDF_SPLIT_BY_CHAR(@id,',') a
 left join  dbo.UDF_SPLIT_BY_CHAR(@Name,',') b on 1=1
 left join  dbo.UDF_SPLIT_BY_CHAR(@DeptID,',') c on 1=1

Upvotes: 0

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131729

The question's query is trying to insert a single row, using strings values for the ID and DeptID fields. This will fail with a runtime error.

One can use the table value constructor syntax to insert multiple rows in a single INSERT statement :

INSERT INTO EMPLOYEE 
VALUES
(1,  'Asim Asghar',  5),
(2,  'Ahmad',        5),
(1,  'Asim Asghar',  12),
(2,  'Ahmad',        12)

The values can come from parameters or variables.

Using duplicate IDs and names in an Employee table hints at a problem. Looks like the intent is to store employees and their department assignments. Otherwise why insert 4 rows instead of 8 with all possible combinations?

Employee should be changed to this :

CREATE TABLE [dbo].[EMPLOYEE]
(
    [ID] [int] primary key not null,
    [EMPLOYEE_NAME] [varchar](50)
)

And another table, EmployeeAssignment should be added 

CREATE TABLE [dbo].[EMPLOYEE_ASSIGNMENT]
(
    Employee_ID int not null FOREIGN KEY REFERENCES EMPLOYEE(ID),
    [DEPARTMENT_ID] [int] not NULL,
    PRIMARY KEY (Employee_ID,Department_ID)
)

The data can be inserted with two INSERT statements :

INSERT INTO EMPLOYEE 
VALUES
(1,  'Asim Asghar'),
(2,  'Ahmad'),

INSERT INTO EMPLOYEE_ASSIGNMENT
VALUES
(1,  5),
(2,  5),
(1,  12),
(2,  12)

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16918

You can not pass multiple values together through a variable at a time. The script should be as below considering one person at a time-

CREATE TABLE [dbo].[EMPLOYEE](
[ID] [int] NULL,
[EMPLOYEE_NAME] [varchar](50) NULL,
[DEPARTMENT_ID] [int] NULL,
) ON [PRIMARY]

DECLARE @ID INT = 1;
DECLARE @Name VARCHAR(50) = 'Asim Asghar';
DECLARE @DeptID INT = 5;

INSERT INTO EMPLOYEE(ID,EMPLOYEE_NAME,DEPARTMENT_ID)  VALUES (@ID, @Name, @DeptID)

Then you can change the values for next person and execute the INSERT script again. And from the second execution, you have to skip the Table creation script other wise it will through error.

Upvotes: 1

Related Questions