dynamic table dump to file
-
Ok, here is my script I'm trying to get the exec() statements to work so I can generate a text file from a table where I don't know the number of colums or name of the colums.
Any ideas?
Wes
declare @table_name varchar (255)
declare @hold varchar(255)
declare @hold2 varchar(255)
set @table_name = 'testt'
declare @max_col int
declare @table_id int
declare @this_col int
declare @col_list varchar(255)
select @table_id = id from sysobjects
where name = @table_name
select @max_col = max(colid) from syscolumns
where id = @table_id
select @this_col = min(colid) from syscolumns
where id = @table_id
set @hold2 = ''
while @this_col != @max_col
begin
select @col_list = name from syscolumns
where id = @table_id
and colid = @this_col
set @hold = (select top 1 test from testt)
--(exec('select top 1 '+@col_list+' from testt order by test asc'))
--print @hold
-- (select top 1 test from testt)
select @hold = ltrim(rtrim(@hold))
select @hold2 = @hold +','+@hold2
set @this_col = @this_col + 1
end
select @col_list = name from syscolumns
where id = @table_id
and colid = @this_col
--exec('select top 1 '+@col_list+' from '+@table_name+' order by test asc')
select @hold2 = @hold +','+ @hold2
select @cmds = 'echo OPEN '+@hold2+' >> c:\logdump.txt'
exec @cmdstr = master..xp_cmdshell @cmds
-
Wes,
I'm confused at what you are trying to accomplish. The script works (?)(except there is missing declares for @cmds and @cmdstr) but I am not sure what you are hoping to achieve for output. Can you post that info in more detail. Sorry, I read it a couple of times and still don't get it. Even had my coffee first.
HTH
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
I want to use this type of statement to load up the @hold variable (exec('select top 1 '+@col_list+' from '+@table_name+' order by '+@col_list+' asc'))
Sorry if I wasn't clear.
Wes
-
It almost had me. Syntax
Anyway, this example works...declare
@Priority varchar(32)
, @ColList varchar(32)
, @ID varchar(10)
, @Table varchar(32)
, @Sqlstr nvarchar(128)
set @ID = 1
set @ColList = 'LastName'
set @Table = 'SSC3Test'
set @Sqlstr = '(select '+@ColList+' from '+ @Table +' where ID = '+@ID+')'
print @Sqlstr
--
exec @Priority = sp_executesql @stmt = @Sqlstr
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
lol I'm glad you figured it out. I've been busting my head on it for a while. I never even thought about using the built in sp's. Thanks a ton!
Wes
-
hrum. ok The statement executes but I need to get the results back into variable to later processing. When I run the script it shows the results of the select in the results browser but are the results going into any variable. This is what I am testing with.
declare
@Priority varchar(32)
, @ColList varchar(32)
, @ID varchar(10)
, @Table varchar(32)
, @Sqlstr nvarchar(128)
, @stmt nvarchar(128)
set @ID = 1
set @ColList = 'test'
set @Table = 'testt'
set @Sqlstr = '(select top 1 '+@ColList+' from '+ @Table+')'
--
exec @Priority = sp_executesql @stmt = @Sqlstr
print @stmt
print @Priority
print @Sqlstr
I need the single row that is returned dumped into a variable. From that I build a list and dump all of that to a text file.
Wes
-
Wes,
The @Priority variable is getting populated with the output from the dynamic select statement.
In theory you should be able to build in your while loop and modify to fit your needs. I still don't understand what you are hoping for as output but if you want to explain further I would love to help. Sorry, having a hard time understanding this one.
Dave write code but Dave don't get big picture. Ugh.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
When I print @priorty variable it only has a 0 in it. It doesn't seem to have what should be the output of the select statement. I'll take a look at it again.
Thanks,
Wes
-
Well... Um.... Er.... Bigger bonehead than I thought.
Uh, I am, uh, getting the same thing. Need to investigate more. Some stupid reason I thought I had this working but looking at it now, I too am getting zero. Sorry for misleading you Wes.
Anyone else going to help with this one? Jump in anytime.....
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
lol, Well at least you put me on a diffrent track. I will keep playing with it and see what I can do. Thanks again for your help.
Wes
Anyway, this example works...
Uh, I am, uh, getting the same thing. Need to investigate more. Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply