Generate SQL Insert Script from Excel Worksheet

Generate sql insert script from excel worksheet

I think importing using one of the methods mentioned is ideal if it truly is a large file, but you can use Excel to create insert statements:

="INSERT INTO table_name VALUES('"&A1&"','"&B1&"','"&C1&"')"

In MS SQL you can use:

SET NOCOUNT ON

To forego showing all the '1 row affected' comments. And if you are doing a lot of rows and it errors out, put a GO between statements every once in a while

Generating SQL INSERT from data in Excel file

@Al2110

based on your query, To generating SQL insert from data in excel file that show in below:

insert into customers values('" &B3 &"','" &C3& "','"&D3&"');

I hope above information will be useful for you.

Thank you.

How do I convert Excel data into a SQL insert?

You have different options depending on which server you are using, but with your current info you could do the following.

1. click on the first empty column in the first row i.e. if data is in row 2 and all columns go from a to k, then you select L2

2. Paste the following code

   ="INSERT INTO Address (AddressTypeID, Address1,City, State, Zip, Phone, PersonName, DisplayName, IsDefault, StatusID, UserID) VALUES '"&A2&"', '"&B2&"', '"&C2&"', '"&D2&"', '"&E2&"', '"&F2&"', '"&G2&"', '"&H2&"', '"&I2&"', '"&J2&"', '"&K2&"'"
   

3. Check to see if the query is correct, it's calculated with the fields from a2-k2)

4. copy -> paste special values only to the next column, so you keep your code, but you can select all rows with the calculated results to your sql query tool

SQL insert using Excel file

=" INSERT INTO Table_Product (TBNID, PRODUCT , PRICE) values ('"&H2&"','"&I2&"','"&J2&"') ;"

just change the Cell locations then drag down.

SQL DATETIME Insert from Excel?

You are right - Excel formats only changes the way the numbers are displayed, not the underlying value of the cell. In this case, the value of the cell is an excel date-time value, which is the # of days since 1/1/1900, with decimals for the time.

I'd recommend using Excel's TEXT function to convert Excel's numeric date-time value to a text string that can be inserted into SQL:

Instead of:

INSERT INTO TABLE VALUES ('"&A1&"', Etc....)"

Try:

INSERT INTO TABLE VALUES ('"&TEXT(A1,"YYYY-MM-DD HH:MM")&"', Etc...)"

Using Excel to generate SQL - problem handling date field

Try the TEXT() function...

TEXT(AC2, "dd/mm/yyy hh:mm:ss")

This returns a string from a value in the specified format

="INSERT INTO TBL_CUSTOMER
                      (FIELD, FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, FIELD, FIELD, 
                      FIELD, FIELD, CUSTOMER_DATE_REGISTERED)
VALUES 
    ('"&D2&"','"&E2&"','"&F2&"','"&G2&"','"&H2&"','"&I2&"','"&J2&"','"&K2&"','"&L2&"','"&M2&"','"&N2&"','"&O2&"','"&P2&"','"&Q2&"','"&R2&"','"&S2&"','"&T2&"','"&U2&"','"&V2&"','"&W2&"','"&X2&"','"&Y2&"','"&Z2&"','"&AA2&"','"&AB2&"',
'"&TEXT(AC2, "dd/mm/yyy hh:mm:ss")&"')"

---

Related Topics