0

How can I Automatically generate sql insert scripts when data is available in some specific template in microsoft excel?Say for example i have four columns in the excel file as table_name and the others as column_1,column_2 and column_3 and their respective values. Now based on the values present in these columns the insert scripts has to be generated, like insert into table_name (column_1,column_2,column_3) values('v1','v2','v3'); . Suggest me the best way by which i can do this? Instead of microsoft excel other options are also fine.

Toto
  • 89,455
  • 62
  • 89
  • 125
Karthik
  • 629
  • 2
  • 8
  • 12

1 Answers1

1

For Excel to create insert statements:

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

or

"insert into  product (product_id,name,date,price) values("&A1&",'" &B1& "','" &C1& "'," &D1& ");"
Mohan Srinivas
  • 302
  • 3
  • 13
  • the column name and table name are not always same and it is going to vary – Karthik Jun 19 '17 at 06:54
  • are the table name and the column names always in the same columns/cells? if you don't wanna create the statements with a statement like above, you could also create i.e. a powershell which reads through the excel, creates the insert statements and executes them against the database – Esteban P. Jun 19 '17 at 07:01
  • @EstebanP. can u please send me some reference links? where i can get to know how to proceed with the powershell thing. – Karthik Jun 19 '17 at 07:20
  • @Karthik Link for reading through Excel: https://stackoverflow.com/questions/19211632/read-excel-sheet-in-powershell Link for inserts through Powershell script: https://stackoverflow.com/questions/22974931/using-powershell-insert-string-values-in-sql-server-db – Esteban P. Jun 19 '17 at 07:41