How can I create a table with x attributes and n rows like?
For example:
x = 4 , and n = 10
att1 att2 att3 att4
----------------------
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
declare @x int = 4, @n int = 10
declare @strg nvarchar(1000) = 'create table myTable ('
declare @i int = 1
while @i<= @x
begin
set @strg = @strg + 'att' + cast(@i as varchar(5))+ ' int default ' + CAST(@i as varchar(5)) + ','
set @i = @i + 1
end
set @strg = SUBSTRING(@strg, 1, LEN(@strg)-1) + ')'
-- this creates your table
exec sp_executesql @strg
-- now lets insert rows
set @i = 0
while @i < @n
begin
INSERT INTO myTable DEFAULT VALUES;
set @i = @i + 1
end
-- lets check
select *
from myTable
Here's a script. You could put this into a stored procedure or just leave it as it is. All you have to do is update the initial col and row values.
DECLARE
@cols INT = 4,
@rows INT = 10,
@tablename VARCHAR(20) = 'TestTable'
DECLARE
@i INT = 1,
@j INT = 1,
@sql NVARCHAR(MAX) = 'create table ' + @tablename + '('
WHILE(@i <= @cols)
BEGIN
SET @sql = @sql + 'att' + CAST(@i AS VARCHAR(10)) + ' VARCHAR(10)'
IF NOT @i = @cols
BEGIN
SET @sql = @sql + ', '
END
SET @i = @i + 1
END
SET @sql = @sql + ')'
EXECUTE sp_executesql @sql
SET @sql = 'INSERT INTO ' + @tablename + ' VALUES'
WHILE(@j <= @rows)
BEGIN
SET @i = 1
SET @sql = @sql + '('
WHILE(@i <= @cols)
BEGIN
SET @sql = @sql + '''' + CAST(@i AS VARCHAR(10)) + ''''
IF NOT @i = @cols
BEGIN
SET @sql = @sql + ', '
END
SET @i = @i + 1
END
IF NOT @j = @rows
BEGIN
SET @sql = @sql + '), '
END
SET @j = @j + 1
END
SET @sql = @sql + ')'
EXECUTE sp_executesql @sql
Hope this helps.
