March 20, 2006 at 2:03 pm
Hi
I am trying to Insert/update a table in the following way..
Table Name: Inventory
Cols : InventoryID (PK), InventoryName, InventoryDesc
Inventory
InventoryID InventoryName InventoryDesc
100 computer laptop
First Insert a record as with negative ID
-100 Computer palmtop
and then update ID=100 in a stored procedure WITHOUT using col names (like getting all the col names from sysclos system table ...since it is the same table.) can some one point in the right direction.
Thanks
March 20, 2006 at 4:09 pm
You're trying to write an update query without using column names?
You could generate the SQL you want into a string, and then execute that. It's not a nice way of doing it, but it will certainly work.
Presumably the query you want will be:
update i1
set i1.inventoryname = i2.inventoryname,
i1.inventorydesc = i2.inventorydesc
from Inventory i1
join Inventory i2
on i1.inventoryid = -i2.inventoryid
where i2.inventoryid < 0
;
delete from Inventory where inventoryid < 0
In a few minutes I'll post something to create this. I'll probably use a cursor, because it's still the most _reliable_ way of concatenating strings, unless you want to write yourself a CLR user-defined aggregate function.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
March 20, 2006 at 4:18 pm
Does this work for you:
declare @tablename varchar(256)
set @tablename = 'Inventory'
-- First let's fetch the id column.
-- We could have done this in our cursor, but it's nice to separate it out for readability.
declare @idcol varchar(256)
select @idcol = sc.name
from syscolumns sc
join
sysobjects so
on so.id = sc.id
where so.name = @tablename
and sc.colid = 1
-- select @idcol
-- Now let's get the 'set' clause of the query.
declare @othercols varchar(4000)
set @othercols = ''
declare @nextcol varchar(256)
declare csrCols cursor for
select sc.name
from syscolumns sc
join
sysobjects so
on so.id = sc.id
where so.name = @tablename
and sc.colid > 1
open csrCols
fetch csrCols into @nextcol
while (@@fetch_status = 0)
begin
set @othercols = @othercols + ', t1.' + @nextcol + ' = t2.' + @nextcol
fetch csrCols into @nextcol
end
close csrCols
deallocate csrCols
--...and rip off the leading comma
set @othercols = substring(@othercols,2,3999)
-- Now we construct the query we're going to run
declare @query varchar(8000)
set @query = 'update t1 set ' + @othercols + ' from ' + @tablename + ' t1 join ' + @tablename + ' t2 on t1.' + @idcol + ' = -t2.' + @idcol + ' where t2.' + @idcol + ' < 0 '
set @query = @query + '; delete from ' + @tablename + ' where ' + @idcol + ' < 0 '
-- select @query
exec (@query)
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply