I am trying to create a script that will look at every column in my SQL table and loop through to insert a row into a different table of every column name.
Basically inverting a table to a different table.
I’m unfamiliar with PIVOT
Sounds like you’re trying to pound a square peg into a round hole. Might be easier to learn how to do a pivot query instead.
I am actually in need of the 2 tables…
Something you only need to do once?
Or ongoing? Because if ongoing, you’re probably better off using a trigger on the table to automatically do it.
just once
Use system.dataset.getColumnHeaders:
pyDataSet = system.db.runQuery("SELECT * FROM MyTable WHERE 1=0")
normalDataSet = system.dataset.toDataSet(pyDataSet)
for col in system.dataset.getColumnHeaders(normalDataSet):
system.db.runPrepUpdate("INSERT INTO MyColTable (ColumnName) VALUES (?)", [col])