Basic Dynamic SQL question

Transact-SQL
1

I have N tables that is unknown before runtime with tablename always being tablename1, tablename2... tablenameN. The first column of each table is always Name. The challenge is to change that column name in each table to Name1, Name2.. NameN. I am new to dynamic sql, any help would be appreciated. Thanks

2

Something like this:

declare @i int = 1
declare @n int = 10

while @i <= @n begin
    declare @alter nvarchar(400) =
        'sp_RENAME ''tablename' + ltrim(str(@i)) + '.Name'', ''Name' + ltrim(str(@i)) + ''',''COLUMN'''
    print @alter
    exec sp_executesql @alter
    set @i += 1
end
1 Like
3

Thanking you for the swift reply. I'm going to have a go.

4

That worked, thank you very much. Appreciate it.

5

This is the code I have at the moment to bunch a group of tables into one mastertable joining on the filename column. The problem at the moment is that

select *
into dbo.mastertable

keeps repeating every time x increments by 1. Therefore SQL will throw an error complaining that the object mastertable is already in the database. How can I move it so that select * into dbo.mastertable is only run once while everything else loops?

DECLARE @x int = 1
DECLARE @y int = 100
Select @y = count(*)
from sys.tables
where sys.tables.name LIKE 'doc%'

DECLARE @sql AS NVARCHAR(MAX) = '
select *
into dbo.mastertable
from dbo.doc01 T1'
while @x <= @y-1
Begin

	SELECT @sql =  @sql  + ' inner join ' + 'dbo.doc0' 
	+ ltrim(str(@x+1)) + ' T'+ ltrim(str(@x+1))+ ' on'  + ' T3' + '.Filename3' + 
	' = ' + ' T' + ltrim(str(@x+1)) + '.Filename' + ltrim(str(@x+1)) + ' '

print @sql
exec sp_executesql @sql
set @x += 1

End

6

you declare the variable @sql before the loop. then in every iteration of the loop you append an INNER JOIN to it and then execute the result. When @x = 1, there is one inner join; when @x = 2 there are two inner joins, when @x = 99 there are 99 inner joins. That can't be what you want.

Why not have a separate variable -- say @sqlstart -- and use that in the loop.

DECLARE @sqlstart AS NVARCHAR(MAX) = '
	select *
	into dbo.mastertable
	from dbo.doc01 T1'
while @x <= @y-1
Begin

	SELECT @sql =  @sqlstart +...

However, I'd really like to know what you are trying to do here. I believe there is a simpler way. Please describe the business problem you're trying to solve.

7

There are N tables doc01, doc02, doc03, docN, the number of tables again is unknown before runtime. Each table has a common column name called Filename1, Filename2.... (from very first thread).

I need all N tables joined together on the filename column into one big table called mastertable. So for every iteration, there does need to be an extra inner join, but select * into dbo.mastertable must only occur once. Does it make sense?

Currently my code above repeats select * into dbo.mastertable for every increament of x....

8

OIC, well it should occur only once as you have written it. However, you also run the code once per loop. Is that what you want? Also, how many columns do the dbo.docn tables have? Since you're doing select *, you might run into the SQL limit for columns returned (4096) if you have enough docn tables.

so, for starters, Move the print and sp_executesql calls out of the loop.

Also, SQL will not be able to optimize a query with so many joins. perhaps use temp tables with joins of 5 sources tables, then join the temp tables together (also in sets of 5). More work to write, but likely better performance.

9

Thank you, I believe the code below is the logic that I need. However you are right in that SQL threw an error 'cannot create a row of size 9554 which is greater than the allowable maximum row size of 8060'.

I will have to go with your suggestion of using temp tables with joins. Are you able to clarify your suggested method? Maybe point me in the right direction? Sorry If I am asking really rookie questions.

Regards

DECLARE @x int = 1
DECLARE @y int = 100
Select @y = count(*)
from sys.tables
where sys.tables.name LIKE 'doc%'

DECLARE @sql AS NVARCHAR(MAX) = '
select *
into dbo.mastertable
from dbo.doc01 T1'
while @x <= @y-1
Begin

	SELECT @sql =  @sql  + ' inner join ' + 'dbo.doc0' 
	+ ltrim(str(@x+1)) + ' T'+ ltrim(str(@x+1))+ ' on'  + ' T1' + '.Filename1' + 
	' = ' + ' T' + ltrim(str(@x+1)) + '.Filename' + ltrim(str(@x+1)) + ' '

set @x += 1

End
print @sql
exec sp_executesql @sql

10

This is not a rookie question. It's actually advanced.

Here's the approach:

SELECT ...
INTO #temp1
FROM table1
JOIN table2
JOIN table3
...

SELECT ...
INTO #temp2
FROM table4
JOIN table5
JOIN table6
...

SELECT
FROM #temp1
JOIN #temp2

...etc.

You'll need to figure out how to write this so it is scalable. btw, how many columns will you pull from the various doc tables?

11

Cheers, I'll see what I can do.

The particular case at the moment has 80 tables, each with about 200 columns, the char length of each column is anywhere between 15- 120 chars.
I need to pull all columns for each of the tables.

12

ok....ish. what will you (or anyone else) do with 16000 columns in one row?

13

Just figured that I'm going to have issues joining 3 tables, just that exceeds the 8060 limit.

14

Time to redesign!

15

The large table is going to be a reference table for another platform!