Index in an SQL make table query (1 Viewer)

[seizure]

Hi,

Was wondering, is it possible to add an index field into a make table query? And if so, how do you do it in SQL?

Thanks

 

[pdx_man]

You cannot do it in the same instance of the table being created with the INTO statement, but you can add the index after it is created, or define the table previously with an index and then do an INSERT INTO

SELECT *
INTO MyDB.dbo.MyTable
FROM MyDB.dbo.MyOtherTable

CREATE [CLUSTERED] INDEX idx_MyIndex ON MyDB.dbo.MyTable (MyField)

OR

CREATE TABLE MyDB.dbo.MyTable(
MyField1 VARCHAR(8),
MyField2 SMALLDATETIME
)

CREATE [CLUSTERED] INDEX idx_MyIndex ON MyDB.dbo.MyTable (MyField1 )

INSERT INTO MyDB.dbo.MyTable (MyField1, MyField2)
SELECT FirstField,
SecondField
FROM MyDB.dbo.MyOtherTable

 

[seizure]

Fantastic! Thanks for that. I'll give it a go.

 

[seizure]

Hi,

I've given your suggestion a go. Here's my code:

strSQL = "SELECT tblLog.Date, tblLog.Comments, tblLog.Client, tblLog.[Number of Accounts], tblLog.[User ID], tblLog.[Value of Accounts], tblAction.[Action Description], tblLog.[File Location], tblLog.[Creditor ID], '' AS ID INTO tblSearchResults FROM tblLog INNER JOIN tblAction ON tblLog.Action = tblAction.ActionId WHERE (((tblLog.[User ID]) Like 'User12'));"

DoCmd.RunSQL (strSQL)

strSQL2 = "CREATE INDEX idxID ON Tracking.tblSearchResults (ID);"

DoCmd.RunSQL (strSQL2)

It appends the data in OK, creating the ID field, but the second SQL string doesn't add an index into the ID field although it does execute without error.

I've looked around, but I can't seem to work out why it isn't working. Any suggestions?

Cheers

 

[seizure]

Having done a bit more digging..... CREATE INDEX doesn't actually add a physical index, does it.

That's OK, but how would you then query the table for the records based on the index number that has just been created?

Cheers

 

[seizure]

This is what I needed:

ALTER TABLE tblSearchResults ADD COLUMN ID AUTOINCREMENT

I had to take the blank column called "ID" out of the first SQL string, but that allowed me to run the code above as a second SQL string, thus giving me a physical ID field.

Cheers