mass insert individual line SQL

Question

Here is my issues, I'm provided with a text file with around 900k line that looks like this.

INSERT INTO TABLE_A VALUES (VAL1, Val2, val3)
INSERT INTO TABLE_A VALUES (VAL1, Val2, val3)
INSERT INTO TABLE_A VALUES (VAL1, Val2, val3)
INSERT INTO TABLE_A VALUES (VAL1, Val2, val3)

When i run this into SQL server i get an out of memory error. Is there a way to split this into bulk other than Copy/Paste/Comment that would prevent Server to not execute this and prevent lost data because of poor selection during the select/copy/past/comment process that would also take forever!

Explore this link: https://stackoverflow.com/questions/12365992/sql-insert-multiple-rows — ChrisG, Jun 19 '18 at 13:38
Do you have access to the original table that this data was scripted from? — dfundako, Jun 19 '18 at 13:40
"When i run this into SQL server i get an out of memory error." - I am pretty sure you do not. I am pretty sure you get an OOM when trying to run it in SSMS and the OOM is a SSMS error. Why do you not use sqlcmd to run it from a command line? — TomTom, Jun 19 '18 at 13:50
You should ask for the file to come in a format that will facilitate bulk loading. It will run much faster and consume less server resources. Failing that I would consider a process to find and replace as necessary to get the bulk load ready file. — Joe C, Jun 19 '18 at 13:54
I've Done it using SQLcmd and it's going fine, Now i just need to figure out how to process special character like 'é è' Thank for the help!! — Gadjet, Jun 19 '18 at 15:26

score 0 · Answer 1 · answered Jun 19 '18 at 13:43

0

If your intention is to insert the same data over and over you can loop it with WHILE. If you are using meaningful data you can try BULK INSERT with excel file.

answered Jun 19 '18 at 13:43

dimo raichev

570
5
15

No the data on each row is not the same. – Gadjet Jun 19 '18 at 17:44
Try to add it in excel file and bulk insert it. It will save a lot of time i think. – dimo raichev Jun 20 '18 at 06:20

Wei Lin · Answer 2 · 2018-06-19T13:51:01.683

0

commit one time / every 5 insert

it looks like:

INSERT INTO TABLE_A VALUES (VAL1, Val2, val3)
INSERT INTO TABLE_A VALUES (VAL1, Val2, val3)
INSERT INTO TABLE_A VALUES (VAL1, Val2, val3)
INSERT INTO TABLE_A VALUES (VAL1, Val2, val3)
commit;

if it also throw memory exception then

can you provide how to run it. ex..use sql server manager studio to run sql script?

edited Jun 19 '18 at 13:51

answered Jun 19 '18 at 13:45

Wei Lin

3,591
2
20
52

score 0 · Answer 3 · answered Jun 19 '18 at 14:17

0

Use Word to do a FIND AND REPLACE on the text file, replacing all instances of ) with )^lGO^l Saving and running the new text file will commit each INSERT statement individually.

answered Jun 19 '18 at 14:17

cloudsafe

2,444
1
8
24

mass insert individual line SQL

3 Answers3