Using formula to create insert sql

[gmoorthy]

I am pretty raw in Excel formula and i need to know if there an easy way to do this in excel:

I have columns with data in a sheet which are as follows:


ID Code Class Datetstamp

1 XYZ TEW currentdate
1 ABC ROP Currentdate



I want an insert statement created from this as follows


insert into table name ( id, code, class,Datestamp) values from ( column A (id) , Column b (code) , column c (class , column D (datestamp)

 

[SkipVought]

hi,

what database are you updating?

I think that you want to use VBA forum707, via ADO Data Objects maybe?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jrbarnett]

Skip

I have used this technique successfully to generate SQL scripts from data in Excel for running later. Something like:

="insert into mytable (id, class, code, datestamp) values (" & A2 & ",'" & B2 & "','" & C2 & "','" & Year (D2) & "-" & Month (D2) & "-" & day (D2) & "')"

will do it. You then copy the resulting scripts to the clipboard and can paste direct into SQL Server management studio or a text file for generating a script.

ADO or integration services can be used to import data into SQL server, but this is simpler with less backend setup required.

John

 

[Andrzejek]

Try something like this:
[tt]
=CONCATENATE("Insert Into MyTable() Value(",A8,",",YEAR(D8),"/",MONTH(D8),"/",DAY(D8),")")[/tt]

Or you can do a little VBA to do it better.

Have fun.

---- Andy