Select into

[Lafor]

Hi!

I would like to create a table with a variable table name on the fly.. something like this

select * into mytable & suser_sname() from etc...

Any ideas?

[RhinoBull]

strTable = CreateNewTableName(strUser)
strSQL = "select * into " & strTable & " from ..."
adoConn.Execute strSQL

CreateNewTableName could be some function that creates new table name for you. Although, you may need to pass say current user name to it.

[Lafor]

I can't do it like that...

I have a stored procedure that's populating a temp table a la
#tblxxx... At the end of this stored proc, I want to select from
that temp table into a table with the user name appended to it..

If I wait to do it when the stored proc returns to vb, then I would no longer have access to that #tbl. I have to create that #tbl temp table this way because of the multiple users that will be accessing the stored proc at the same time...

Thnks

[szlamany]

Won't this work in your sproc?

Declare @QueryString varchar(100)
Set @QueryString="Insert into Table" + @UserName+" Select ..."
Exec(@QueryString)

[RhinoBull]

Originally posted by Lafor
I can't do it like that...

I have a stored procedure that's populating a temp table a la
#tblxxx...

In this case just pass your new table name to that SP as parameter:

adoConn.Execute "sp_name tblName" - check syntax here as it's all free hand typing.