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.
