December 5, 2012 at 9:38 am
Can anybody help me actually execute the resultant DELETE statements in this query
drop table #incoming
CREATE TABLE #incoming
([id] [bigint] IDENTITY(1,1) NOT NULL,
[tablename] nvarchar(50),
[action] [int] NULL,
[rowid] [bigint])
INSERT #incoming (tablename,action,rowid) VALUES('#test',0,1111)
INSERT #incoming (tablename,action,rowid) VALUES('#test',0,1211)
INSERT #incoming (tablename,action,rowid) VALUES('#test',0,1311)
INSERT #incoming (tablename,action,rowid) VALUES('#test',1,1111)
drop table #test
create table #test
([tid] [bigint],
[data1] nvarchar(50),
[data2] nvarchar(50))
INSERT #test (tid,data1,data2) VALUES(1111,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1211,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1311,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1111,'foo','foo')
SELECT 'DELETE FROM ' + tablename + ' where tid =' + convert(char,rowid) from #incoming where action = 0
SELECT * from #test
December 5, 2012 at 10:23 am
Hello Alan,
Try this...
DECLARE @QRY varchar (255)
SELECT @QRY = 'DELETE FROM ' + tablename + ' where tid =' + convert(char,rowid) from #incoming where action = 0
--PRINT @QRY
EXEC (@QRY)
December 5, 2012 at 10:34 am
arunyadav007
That only seems to execute the last statement.
I guess i need to somehow loop the results.
Alan
December 5, 2012 at 10:38 am
Woh ! I thought you only need to execute the last delete statement.. But, I guess you need to execute a delete statement on all the tablenames from your temporary table.. Let me try...
December 5, 2012 at 10:40 am
Change to
declare @q varchar(max)
SELECT @q = ISNULL(@q, '') + 'DELETE FROM ' + tablename + ' where tid =' + convert(char,rowid) + '; ' from #incoming where action = 0
exec(@q)
but beware the dangers of this type of dynamic sql.
Edit: Read about the dangers here http://www.sommarskog.se/dynamic_sql.html
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 5, 2012 at 10:42 am
Yes i need to run the results of the SELECT 'DELETE..... statement
DELETE FROM #test where tid =1111
DELETE FROM #test where tid =1211
DELETE FROM #test where tid =1311
Got me stumped
December 5, 2012 at 10:50 am
toddasd
Bingo, thank you so much.
This is part of a database sync system. The commands are built inside a stored procedure they are not stored in tables, just the data. Like table name, action, change data.
Thanks again
December 6, 2012 at 5:32 am
toddasd (12/5/2012)
Change to
declare @q varchar(max)
SELECT @q = ISNULL(@q, '') + 'DELETE FROM ' + tablename + ' where tid =' + convert(char,rowid) + '; ' from #incoming where action = 0
exec(@q)
but beware the dangers of this type of dynamic sql.
Edit: Read about the dangers here http://www.sommarskog.se/dynamic_sql.html
This appears to be deleting everything from #incoming
Whereas it should not have deleted the below row as action != 0
4#test11111
December 6, 2012 at 5:48 am
Put a [font="Courier New"]PRINT @q[/font] in your script and you'll see why that's happening.
John
December 6, 2012 at 5:50 am
Hi
Bum data
INSERT #test (tid,data1,data2) VALUES(1111,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1211,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1311,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1111,'foo','foo')
Should read
INSERT #test (tid,data1,data2) VALUES(1111,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1211,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1311,'foo','foo')
INSERT #test (tid,data1,data2) VALUES(1411,'foo','foo')
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply