stored insert staements in a table and cursor

Question

Post reply

stored insert staements in a table and cursor

nabajyoti.b

SSCrazy

Points: 2177
More actions
January 6, 2009 at 7:37 am

#155090

i have a table t1 which contains insert statements which are to executed sequentially one by one.However t1 contains 3 columns
c1, c2 and c3 where c2 contains all those insert statements as strings.
Now if the value of c3 is 0 for a particular record, the insert statement in c2 expects a date to be supplied, which is getdate().
HOw do i go about implementing the requirement? I belive i have to use a cursor. Could you please show me some code related to the case.
Thanks...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions and sample data. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

foxjazzG SSCrazy Points: 2221 More actions · Answer 2

I think he described it pretty well.

Unfortunately t-sql isn't friendly like that. It's difficult if not impossible to do dynamic inserts statements.

What you should do instead of "having insert statements in a table, have the values you want inserted into a table, much simpler.

Then just work with ssis in getting the data in instead of t-sql, it's also easier than trying wierd stuff.

You could write C# code to read t1 and execute the dynamic statements using a datatable loop. That would be fastest in the case you describe.

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 3

And what if c3 is not 0? Is the insert statement different then, or the parameter value is different?

As a starting point, this would be one of the ways to do it, no C# and no cursors.

There is no code that would deal with different values of c3.

create table tinserts(c1 int identity(1, 1), c2 varchar(100), c3 bit)

go

set nocount on

insert tinserts(c2, c3)

select 'insert into atable values(getdate(), 11)', 0 union all

select 'insert into atable values(getdate(), 232)', 1 union all

select 'insert into atable values(getdate(), 11341)', 0

go

set nocount on

declare @sql varchar(max)

;with cte (a) as (select c2 + char(10) + 'GO' + char(10) from tinserts for xml path(''))

select @sql = a from cte

print @sql

--exec @sql --you can execute inserts

go

drop table tinserts

Regards

Piotr

...and your only reply is slàinte mhath

foxjazzG SSCrazy Points: 2221 More actions · Answer 4

sure there is, the difference is in c3.

Do the stuff you need where c3 = 0

sqlexecute ("inert into t4 (c1,c2,c3) values (@c1,@c2,getdate())

do the stuff you need where c3 <> 0

sqlexecute ("inert into t4 (c1,c2,c3) values (@c1,@c2,@c3)

the pseudo code works. Just do to different functions depending on the date value in c3.

And you can still use c# it's easier to do this in a valid programming language than t-sql because it's easier to debug.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

foxjazz (1/6/2009)
And you can still use c# it's easier to do this in a valid programming language than t-sql because it's easier to debug.

OK, now is time to leave the crack pipe alone 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉