2

I have two Excel sheets, one containing customer information :

+-------+-------+-------+--------+
| Fname | Lname | Birth | Gender |
+-------+-------+-------+--------+

and a second one with transaction logs formatted as follows :

+------+--------+
| Date | Amount |
+------+--------+

My SQL table is empty and has the following fields :

+----+------------+-----------+--------+-------+-------+------+
| ID | First Name | Last Name | Gender | Birth | Spent | Date |
+----+------------+-----------+--------+-------+-------+------+

I'm looking for a way to merge the two sheets and transfer the data into the table but cannot figure out an efficient way to do it.

ilomax
  • 568
  • 1
  • 4
  • 24
  • How many records? It's a one shot activity or a periodic one? – etsa Aug 03 '17 at 10:07
  • 2
    How are you matching the records in the first sheet to the second? There doesn't appear to be anything to link the two together. – Aidan Aug 03 '17 at 10:09
  • What is joining column? – Kannan Kandasamy Aug 03 '17 at 10:10
  • @etsa Both sheets contain 7 rows, one for each individual sale. They are randomly dated as I only wanted to try and "reassemble" the table with them as an exercise. – ilomax Aug 03 '17 at 10:31
  • @Aidan the only similiraity is the placement in the sheets. Sale 1 corresponds to customer 1, sale 2 is customer 2's etc.. – ilomax Aug 03 '17 at 10:34
  • @ilomax I would suggest that each user is given a CustomerID, and that an ID is applied to each transaction. Otherwise you have to create a duplicate customer record every time someone makes a new transaction. Depending on the order of the rows to match up is super not recommended. – Aidan Aug 03 '17 at 10:48
  • @Aidan I get what you mean. As I said above, my goal was to see if I could fill a table with data contained on several sheets. Now that you mention it, it is slightly illogical and useless not to have any matching criteria. I just overthought the thing and maybe figured something that is very unlikely to happen in a real-life professional scenario. – ilomax Aug 03 '17 at 10:56
  • @ilomax Oh, OK, you're just messing around to see if it can be done, rather than implementing this actual structure somewhere. I wouldn't be too worried about it, if I were you, just keep it in mind for the future. As for your question, you're probably looking for something like BULK INSERT. https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql – Aidan Aug 03 '17 at 11:08
  • @Aidan I've already used `BULK INSERT` to transfer data from a .txt. SSMS has a wizard which can be used to create a table directly from an Excel sheet. But I'm indeed just playing around, trying to get as familiar and at-ease as possible with SQL Server and the language before I actually get to work with it. – ilomax Aug 03 '17 at 12:41

1 Answers1

0

You can use BULK INSERT to insert the data from the excel files into a table. What i would recommend you do is create 2 temporary tables; #Customers and #Transactions. Insert your customers file data into #Customers, your transaction data into #Transactions.

Then, insert data into your CustomersAndTransactions table by selecting your data from the two temporary tables using a join.

You can read up on BULK INSERT here: https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql

There are also plenty of good questions about BULK INSERT on StackOverflow already, such as this one which should help you avoid a few problems: Import CSV file into SQL Server

Aidan
  • 171
  • 3
  • 12