Bulk insert sql server 2012 reading from array

Question

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 ?

you could simply do that https://stackoverflow.com/questions/46902892/string-split-in-sql-server-2012 which is showing in example. — Yogesh Sharma, Oct 25 '17 at 06:08
`Bulk Insert` - what exactly do you mean? This - https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql or not? — gotqn, Oct 25 '17 at 07:01
Could you show a little bit more from your file? I want to see its structure and how data is stored. — gotqn, Oct 25 '17 at 07:04
@gotqn I don't want to load data from file ,I want to loads it from ther array — Moh, Oct 25 '17 at 07:06

Abdul Rasheed · Answer 1 · 2017-10-25T06:52:45.843

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

@Moh just uncomment the insert statement..., it will load the data from ur array in Grade table which u want. — Yogesh Sharma, Oct 25 '17 at 06:51

score 0 · Answer 2 · answered Oct 25 '17 at 07:13

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.

answered Oct 25 '17 at 07:13

gotqn

42,737
46
157
243

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 – Moh Oct 25 '17 at 07:27
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? – gotqn Oct 25 '17 at 07:29
@Moh Could you paste me the original text of the task. I am guessing you my not understand it correctly. – gotqn Oct 25 '17 at 07:33
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 – Moh Oct 25 '17 at 07:37

Bulk insert sql server 2012 reading from array

And I Have This Table

2 Answers2