0

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

Improve this question
5
  • Parameters aren't designed nor intended to hold multiple values... you need to set them for each insert. Commented Jun 12, 2019 at 8:46
  • 2
    How do you expect to insert 4 rows when you have a single INSERT? Your code tries to insert strings into numeric fields too Commented Jun 12, 2019 at 8:47
  • @PanagiotisKanavos I understand, but apparently I am able to retrieve data from Select statement by providing multiple values in parameter, so it means that can't done in Insert query ? Commented Jun 12, 2019 at 8:54
  • @PanagiotisKanavos please checkout this link stackoverflow.com/questions/56555418/… Commented Jun 12, 2019 at 8:55
  • @DoonieDarkoo that's not what you asked though. Commented Jun 12, 2019 at 9:09

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

The parameter types for ID and DeptID should be changed to int too
@mkRabbani so if I have to add multiple values I'll need to pass each value after inserting one row then move to next ?
@DoonieDarkoo you'll probably have to explain what you want to do because the values are essentially random (why 4 instead of 8 rows?) and displaying duplicate IDs in a table named Employee hints at a problem. Are you trying to store employees and department assignments? That requires two tables, one Employee and one EmployeeAssignment
@mkRabbani See I have 2 employees and both are enrolled in 2 departments so I have to assign both employees these 2 departments. So I need 4 rows obviously 1 employee with 2 rows. Consider Id as Employee Code which cannot be other than provided.
@DoonieDarkoo yes you are correct. If you wants all values to be inserted together, please check Panagiotis's answer.
0

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)
Improve this answer

3 Comments

I get your point but the thing is that I'll get values from developer in this form (comma separated mulitiple) could 100 or more at the same time. I want to insert those rows into the table. He cannot apply loop in code as it'll call procedure 100 times causing too much time to execute all queries.
@DoonieDarkoo there's no reason to loop. Insert all rows in Table1, then all rows in Table2. No CSVs - they are meaningless. As for how to send those 100 rows, that's a different question altogether. TVPs are one option, although they are the slowest. BULK insert operations are the fastest. In C# you can use the SqlBulkCopy class to bulk insert rows into tables
@DoonieDarkoo as for I'll get values from developer in this form (comma separated mulitiple) that's a bug that the developer has to fix. What are you going to do with fields that contain commas or dots? Even if you use TVPs (table valued parameters), the client code has to change and send the rows as a table parameter. In C#, they'd be sent as a DataTable.
0

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
Improve this answer

3 Comments

That's probably the slowest way to split a string in older versions of SQL Server. It's no longer necessary since SQL Server 2016 and STRING_SPLIT. Passing values as strings is a bad idea in the first place though and easily avoided
If I put null in @deptID like this @DeptID VARCHAR(20) = '5, 12'; it adds nothing
I've modified code, now you can Pass null values. if you work on 2016 or greater version of SQL Server, you can use "STRING_SPLIT".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.