Creating table where column_names are results from query from other table

How I can create table where column name is result of query of second table?

Next cod doesnt work:

Use Pubs
declare @.naz varchar(40)
declare naziv cursor
for
select top 1 au_lname from authors

open naziv
FETCH NEXT FROM naziv INTO @.naz
close naziv
deallocate naziv
GO

CREATE TABLE #t1
(@.naz varchar(20),
quote int
)
GO

DROP TABLE #t1

use dynamic sql, although it won't work for temp tables.

EXECUTE('create table t1 (' + @.naz + ' varchar(20), quote int)'

Also, you don't need a cursor to do what you're doing above, it's overkill.

|||You don't need a cursor if you are only returning one row. Otherwise, you will need a cursor to exec the dynamic sql. If you just want the code to execute to create a number of tables, then you can do all the above in a stored procedure and have it output the code, without resorting to dynamic SQL (as you will be doing the executing). Thus:

declare @.dynsql varchar(100)
set @.dynsql = 'CREATE TABLE #t1 ( ' + @.naz + ' varchar(20), quote int ) GO'

sp_executesql @.dynsql

If you want to be really clever, and want to be able to dynamically set the type, size and precision of the column(s) to create, query INFORMATION_SCHEMA.COLUMNS for this information

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = @.naz

You will probably want to specify the tablename of the originating column that you are copying in the new table in the above query aswell.

HTH

For more SQL tips, check out my blog:
|||Use ALTER TABLE with dynamic SQL to add the columns. The base definition will be used in the CREATE TABLE statement.

Creating Dynamic Variables

Is there any way I can create variables dynamically in SQL server procedures?

e.g., I initially declare variable
@.col1='1'
@.col2='0'

and in a for loop with variable @.i,

I have @.str='@.col'+@.i
if @.i=1 then the @.str='@.col1'
and try to insert the value this variable holds into a table as

insert into table1 values(@.str)

Here I want to insert the value of @.col1(which is 1) and NOT '@.col1'

I tried so many time unsuccessfully.Has anyone done this kind of stuff before?any work arounds for this?

appreciate u'r help..

Thanks!Chances are you can accomplish your objectives using standard SQL, with maybe a little help from temporary tables, table variables, or case statements. I don't understand what you are trying to do, but your code looks like SQL written by a VB programmer (loops, inserting parameters, etc...). These are legitimate tools for SQL, but most procedures don't require them.

Using a dynamic SQL statement may be an option, either to assign the value to @.str or for the insert statement, but dynamic SQL is tricky because it executes in a distinct environment and your variables go out of scope.

If you could briefly describe your application, relevant table structures, and the task you are trying to perform, I may be able to give your some programming algorythms that are more appropriate for SQL.

blindman