CODEWITHSUNDEEP
sqlsql-server-2000
C.J.
C.J.

Reputation: 3527

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

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

Upvotes: 0

Views: 973

Answers (2)

Himanshu Lakhara
Himanshu Lakhara

Reputation: 80

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

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

  2. For postgresql you can import .csv file run following

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

  3. For microsoft sql server (2000/2005) this knowledge base article would be useful. Another easy method can be found here

  4. For oracle database this article is worth reading.

Upvotes: 1

Donal
Donal

Reputation: 32713

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.

Upvotes: 2

Related Questions