create tables from a query result

Question

create tables from a query result

wdmm

Old Hand

Points: 321
More actions
July 6, 2016 at 2:59 pm

#400368

i need to create tables from a query result based on the returned query value; i tried the code below
declare @tableName varchar(1000)
declare @rowCount int = 0
set @rowCount = (select count(cd) from mydatabase..mytable)
--select @rowCount --300 rows
declare @demoId int = 0
while @demoId <= @rowCount
begin
set @tableName = (select cd from mydatabase..mytable) --examples: 101 and 1045
set @tableName = 'create table tblD' + @tableName + ' (dv varchar(20) NULL, zd char(5) NULL, ui int NULL)'
exec (@tableName)
--example would be a table name of tblD101, tblD1045
end
the above fails with:
Msg 512, Level 16, State 1, Line 13
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Line 13

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

wdmm (7/6/2016)
i need to create tables from a query result based on the returned query value; i tried the code below
declare @tableName varchar(1000)
declare @rowCount int = 0
set @rowCount = (select count(cd) from mydatabase..mytable)
--select @rowCount --300 rows
declare @demoId int = 0
while @demoId <= @rowCount
begin
set @tableName = (select cd from mydatabase..mytable) --examples: 101 and 1045
set @tableName = 'create table tblD' + @tableName + ' (dv varchar(20) NULL, zd char(5) NULL, ui int NULL)'
exec (@tableName)
--example would be a table name of tblD101, tblD1045
end
the above fails with:
Msg 512, Level 16, State 1, Line 13
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Line 13

Look at this line (and the one after it)....

set @tableName = (select cd from mydatabase..mytable) --examples: 101 and 1045

You have more than 1 row in the select statement. Which row do you want? Hence the error.

Perhaps if you can post some details about the tables in question and what you are trying to do we can help you find a better way to do this. Looping really should be avoided when possible.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/