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

File Created: 2001:02:26:12:08:14

Last Updated: 2001:02:26:12:08:14

	ADO DAO
	Collections
	Constants
	Crystal Reports
	DSOFILE
	Errors
	ImageMagick
	IMAPI
	LogParser
	Lotus Notes
	MDAC
	Netscape
	OLE and Outlook
	OLE TUTORIAL
	OLE with Access
	OLE with Acrobat Viewer
	OLE with Excel
	OLE with Google Earth
	OLE with MSIE
	OLE with PowerPoint
	OLE with Word
	OLE with XML
	OpenOffice
	PDF Conversion
	RegExp
	Samples from Users
	SAPI
	Shell
	SQLite
	Task Scheduler
	Textract
	UDFs
	WIA
	WIL Type Viewer
	WindowsInstaller
	WinHttpRequest
	Winsock
	WSH
	Zip

Database Search

TechHome

Copy Table Structure With SQL