Insert multiple rows into multiple table without repeating "INSERT INTO"

Question

I was given a set of data in EXCEL (600+ rows) that I need to insert into 3 different tables (Membership, Account, Participant). The data look something like this:

ID    FirstName    LastName    DOB        Account_Type  Status    Participant_Code    Participant_Type
1     John         Smith       5/5/1960   Gold          Active    002                 A
2     Hello        World       4/9/1975   Platinum      Inactive  002                 A
.
.
.
600

I need to:

INSERT INTO Membership (ID, FirstName, LastName, DOB)
VALUES (1, 'John', 'Smith', '5/5/1960')

INSERT INTO Membership (ID, FirstName, LastName, DOB)
VALUES (2, 'Hello", 'World", '4/9/1975')

INSERT INTO Account(ID, Type, Effective_Date, Status)
VALUES (1, 'Gold', GetDate(), 'Active')

INSERT INTO Account(ID, Type, Effective_Date, Status)
VALUES (2, 'Platinum', GetDate(), 'Inactive')

INSERT INTO Participant(ID, Code, Type)
VALUES (1, 002, 'A')

INSERT INTO Participant(ID, Code, Type)
VALUES (2, 002, 'A')

I do not want to repeat 600 * 3 INSERT statement. What are my options?

Edit: Sorry, forgot to mention - Bulk insert is not an option due to the limitation on my work station.

Update: Donal provided me with a good starting point. Even though I still need 600+ INSERT statements, with the help from Donal, at least I don't have to write the statement myself. Also, I have modified my code to make it more efficient:

First, I CREATE and INSERT these 600 rows into a temp table.

CREATE TABLE #temp_table (
ID               INT,
FirstName        VARCHAR(50),
LastName         VARCHAR(50),
DOB              DATE,
Account_Type     VARCHAR(10),
Status           VARCHAR(8),
Participant_Code CHAR(3),
Participant_Type CHAR(1)
)

INSERT INTO #temp_table (1, 'John', 'Smith, '5/5/1960', 'Gold', 'Active', '002', 'A')
.
.
so on

Then, for the specific table, I can just use the INSERT INTO... SELECT statement

INSERT INTO Membership
SELECT ID, FirstName, LastName, DOB FROM #temp_table

INSERT INTO Account
SELECT ID, Account_Type, Status, GetDate() FROM #temp_table

INSERT INTO Participant
SELECT ID, Participant_Code, participant_type FROM #temp_table

@xQbert I have done my research before posting my question here. The link you showed me only reduces the VALUES keyword, but I still need to enter the data 600 * 3 times — C.J.
– C.J., Commented Aug 12, 2014 at 17:49
It reduces the Insert into tableName (field names) values. the data still has to be listed; else how would it be inserted... — xQbert
– xQbert, Commented Aug 12, 2014 at 17:58
@xQbert Just found out your method doesn't work in SQL Server 2000 either... — C.J.
– C.J., Commented Aug 13, 2014 at 14:53

Donal · Accepted Answer · 2014-08-12 17:58:46Z

2

In Excel you can generate your SQL statements dynamically. You create a formula that has the sql in a string and concatenate the values dynamically. For example- see here.

edited Aug 12, 2014 at 17:58

answered Aug 12, 2014 at 17:46

Donal

33.2k10 gold badges69 silver badges75 bronze badges

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

5 Comments

C.J. Over a year ago

Sorry, forgot to mention - Bulk insert is not an option due to the limitation on my work station.

Donal Over a year ago

so, in Excel you can generate your SQL statements dynamically. You create a formula that has the sql in a string and concatenate the values dynamically.

Donal Over a year ago

something like this: chandoo.org/wp/2008/09/22/…

C.J. Over a year ago

Can you change your answer that that is reflecting your comment then I can give you an up vote.

xQbert Over a year ago

How does this not "repeat 600 * 3 INSERT statement. What are my options?"

Community · Accepted Answer · 2017-05-23 12:05:01Z

1

You can do something like this

INSERT INTO Membership (ID, FirstName, LastName, DOB)
VALUES (1, 'John', 'Smith', '5/5/1960'),
       (2, 'Hello', 'World', '4/9/1975')

INSERT INTO Account(ID, Type, Effective_Date, Status)
VALUES (1, 'Gold', GetDate(), 'Active'),
       (2, 'Platinum', GetDate(), 'Inactive')

INSERT INTO Participant(ID, Code, Type)
VALUES (1, 002, 'A'),
       (2, 002, 'A')

Edit

To avoid so much manual typing you can use following methods for specific DBMS

For mysql you can export file as .csv file and import using this method

For postgresql you can import .csv file run following

>>psql mydatabase
>>COPY mytable FROM '/myfile.csv' USING DELIMITERS ',';

For microsoft sql server (2000/2005) this knowledge base article would be useful. Another easy method can be found here
For oracle database this article is worth reading.

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Aug 12, 2014 at 17:55

Himanshu Lakhara

801 gold badge1 silver badge8 bronze badges

3 Comments

C.J. Over a year ago

Thank you for trying, but like I mentioned in my comment, it only reduces the VALUES keyword, but it doesn't help me to reduce the need to enter the data 600 * 3 times

Himanshu Lakhara Over a year ago

@C.J. You can export your excel file as .csv and depending on database you can import this file. for mysql see this

Himanshu Lakhara Over a year ago

@C.J. For sql server you can use this KB article. (support.microsoft.com/kb/321686)

Collectives™ on Stack Overflow

Insert multiple rows into multiple table without repeating "INSERT INTO"

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related