Creating tablename in real-time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Creating tablename in real-time

Author

Topic

algorithm
Starting Member

6 Posts

Posted - 2008-07-10 : 10:30:53

Hi - I'd appreciate some direction on this one.

I would like to have a sort of loop, where, for each run of a loop, the tablename changes, e.g. Table1, Table2, Table3 etc.

This is vaguely what I have;

INSERT INTO Tablename1
SELECT * FROM Other_Tables
WHERE (Iterating Condition)

Is this possible?

Many thanks for your help/guidance.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 10:35:19

Well it is, but you'd have to use dynamic sql.

You'd do something like....



DECLARE @sql VARCHAR(8000)
DECLARE @tableName VARCHAR(1000)

<your loop>

<Set @tableName somehow>

SET @sql = '
INSERT INTO ' + @tableName + '
SELECT * FROM Other_Tables
WHERE (conditions)'

EXEC (@sql)

<end loop>

-------------
Charlie

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-10 : 10:37:12

declare @counter int
declare @sql nvarchar(1000)
select @counter=3

while @counter>0
begin
select @sql='select 1 as ''Number'' into tbl' + cast(@counter as varchar(4))
exec sp_executesql @sql
set @counter=@counter-1

END

Mike
"oh, that monkey is going to pay"

MakeYourDaddyProud

184 Posts

Posted - 2008-07-10 : 10:37:47

BAD DESIGN - I am sure you could approach your task another way!!

If you really want to do this, sp_executesql takes a string argument which could incorporate your INSERT thus...

declare @c nvarchar(5)
exec sp_executesql 'INSERT INTO Tablename' + @c1 + 'SELECT * FROM Other_Tables WHERE (Iterating Condition)'

I cannot stress the importance of a better design than what you are attempting...

Daniel

>>>>> THE Whammy Bar String Trasher <<<<<

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 10:39:00

And you probably don't want to do this.

Have you thought about a process keyed table instead (a normal table with an extra column which you can use to identify different processes which use it.

You'd have


<loop>

INSERT INTO processKeyed
SELECT
<loop process key> -- your tablename1 or whatever
, *
FROM
Other_Tables
WHERE (Iterating Condition)

<endloop>

Then you can select from that table with the process key column in a where clause to only get info inserted by that particular loop.

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources