Copy Table Structure With SQL

Keywords: 	 Copy Table Structure SQL

---

Here is a neat trick I discovered. I have many data tables ( mostly in ACCESS, but this pertains to other ODBC sources ) with 100,000 + records in them. Increasingly I have needed to create a table with the same structure only, no data.

I had been using ADO to open the table, loop through the fields and put together a CREATE TABLE string, the execute it... now I can simply:

---

file = "F:\WBDEMO\TESTVS.MDB"
table = "AL_FLD"
new = "New_Table"
Conn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%file%;Jet OLEDB:Engine Type=4"
DB = ObjectOpen("ADODB.Connection")
DB.Open(Conn)

; If you are using a regular ODBC Source, this should work
;DB.Execute("Create %new% as Select * from %table% Where 0 = 1;")

; Here, we are using ACCESS, so use their Make-Table statement
DB.Execute("SELECT * INTO %new% FROM %table% WHERE 0 = 1;")
DB.Close()
ObjectClose(DB)
exit

---

This code uses a OLE DB Provider, but would work with ADO or DAO.

stan littlefield

---

Article ID:   W14666

Filename:   Copy Table Structure With SQL.txt