0

I Have this two array

DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';

DECLARE @Marks NVARCHAR(300)= '0,70,52,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';

And I Have This Table

Grade

PersonId     Marks
1             10
2             9
3             15
4             26

I want to insert the values of the array to the table using BULK insert

how to do that ?

6
  • @YogeshSharma I want to loads data from Array to the table Commented Oct 25, 2017 at 6:04
  • you could simply do that stackoverflow.com/questions/46902892/… which is showing in example. Commented Oct 25, 2017 at 6:08
  • Bulk Insert - what exactly do you mean? This - learn.microsoft.com/en-us/sql/t-sql/statements/… or not? Commented Oct 25, 2017 at 7:01
  • @gotqn yess I mean this Commented Oct 25, 2017 at 7:03
  • Could you show a little bit more from your file? I want to see its structure and how data is stored. Commented Oct 25, 2017 at 7:04

2 Answers 2

1

Try the following tsql Query :-

DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
DECLARE @Marks NVARCHAR(300)= '0,70,52,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';

INSERT INTO Grade (PersonId,Marks)
SELECT PersonId,
       Marks
FROM
(
    SELECT aSplit.a.value('.', 'NVARCHAR(MAX)') PersonId,
           ROW_NUMBER() OVER(ORDER BY
                            (
                                SELECT 1
                            )) AS row_no
    FROM
    (
        SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS PersonId,
               CAST('<X>'+REPLACE(@Marks, ',', '</X><X>')+'</X>' AS XML) AS Marks
    ) AS A
    CROSS APPLY PersonId.nodes('/X') AS aSplit(a)
) AS P
FULL OUTER JOIN
(
    SELECT bSplit.a.value('.', 'NVARCHAR(MAX)') Marks,
           ROW_NUMBER() OVER(ORDER BY
                            (
                                SELECT 1
                            )) AS row_no
    FROM
    (
        SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS PersonId,
               CAST('<X>'+REPLACE(@Marks, ',', '</X><X>')+'</X>' AS XML) AS Marks
    ) AS A
    CROSS APPLY Marks.nodes('/X') AS bSplit(a)
) AS M ON P.row_no = M.row_no;

Result :

PersonId Marks
1        0
2        70
3        52
4        5
5        8
6        9
7        4
8        6
9        7
10       3
11       5
12       2
13       7
14       1
15       9
16       4
17       0
18       2
19       5
20       0
Sign up to request clarification or add additional context in comments.

2 Comments

where is the bulk here ??
@Moh just uncomment the insert statement..., it will load the data from ur array in Grade table which u want.
0

The bulk insert utility is used for importing data from external files, not SQL variables. If you are going to manipulated a large amount of data and you are concerned about performance you can read more about database recovery models where you can change your Full recovery model to Bulk logged for example.

I will advice to talk with superior before changing such settings in your are working in production environment.

4 Comments

Thanks for clarification But my trainer asked me to import the data from array , and from 5 days ago I'm searching for a way to do that but I don't found any way to do that @gotqn
So, your task is saying exactly that you have to use BULK INSERT and the data must be present in the SQL Server - you are not allowed to copy it in a file, for example?
@Moh Could you paste me the original text of the task. I am guessing you my not understand it correctly.
The task written in Arabic so I don't think you will understand it :D simply I have two array and I want to import the data from them to the table using BULK insert

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.