February 12, 2008 at 7:42 am
The only way I can think of to do this in one proc is a lot of IF ELSE statements. "IF @table1 = 'People' ... ELSE IF @table1 = 'Orders'...." with code for each table. If you go that route, you'll have one proc that will do the whole thing. I'd definitely recommend using "with recompile" on that one, since it will have messy problems with the cached solutions each time the input table is changed, but it should work.
I'd also recommend (seriously), looking for a different employer. If your manager insists on solving technical issues in the least efficient, most expensive manner possible (as he has in this case), you'll at the very least end up stressed out and overworked for no good reason. That's personal advice, not professional.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2008 at 7:57 am
Lookup_BI (2/12/2008)
Hi Chris,I totally get you, but my employer is just over the hill to use a dynamic stored procedure.If only I can get this working, as I have a strict deadline.Do have a look at my code and let me know if there is anything I can do to get it work.
You have got single quotes that shouldn't be there in that SQL statement. It's busting up the dynamic SQL.
I fully echo everyone else's sentiments - dynamic for this is, well - not smart, and is going to make your job harder. Frankly I'd be pushing back HARD to get an actual reason why this person is so hot to do this. I'd also probably pull something Sergiy would be proud of (put up a "prop" dynamic stored proc, with a "real one" doing all of the work, since there's no reason to put something this complicated in.) unless they have a REALLY good reason for it.
I HATE managers who think they know more SQL than I do.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 12, 2008 at 8:00 am
I took a look in BOL for Kishore's suggestion on using tablediff.exe.
Lemme quote...
The tablediff command prompt utility returns detailed difference information between two tables and can even generate a Transact-SQL script to bring a subscription into convergence with data at the Publisher.
Now, I don't know for sure if that includes "data", but "convergence with data" seems to mean just that. I don't know if it will allow the two tables to be different as in the case of a partial staging table and a historical table 'cause I haven't tried it.
But, it might be worth a 4 row table experiment...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2008 at 8:28 am
I have used a hybrid approach: Write a procedure that generates the code to create static stored procedures. I have done exactly this for cases where we need to move data from QA to production databases.
What you are trying to do has a lot of potential issues. For example, you will need to execute the procedures in the correct order in order to maintain referential integrity; if table A is referenced by table B and B is referenced by C, you must insert/update A, then B, and then C.
As GSquared pointed out, what you are doing is a major undertaking. I believe that I spent several weeks writing the code to do this and getting it debugged and tested. Don’t bother asking me to post my code.
The only compare tool I am familiar with is Red Gate Compare. It generates a one-time script, so I believe you have rerun the compare tool every time. I am not sure it would be good for a situation that needs to be automated, or where there are large amounts of data involved.
February 12, 2008 at 8:29 am
Jeff Moden (2/12/2008)
I took a look in BOL for Kishore's suggestion on using tablediff.exe.Lemme quote...
The tablediff command prompt utility returns detailed difference information between two tables and can even generate a Transact-SQL script to bring a subscription into convergence with data at the Publisher.
Now, I don't know for sure if that includes "data", but "convergence with data" seems to mean just that. I don't know if it will allow the two tables to be different as in the case of a partial staging table and a historical table 'cause I haven't tried it.
But, it might be worth a 4 row table experiment...
Or - not. Wow. nothing like a 50MB file of single-row UPDATE statements. Never mind that it will also be "invalid" the minute you generate it, unless you've stopped all updates to the data (which you probably have by running this little jewel).
It's interesting, but that's an approach that doesn't look incredibly scalable.... The file would be out of date by the time you're done generating and running it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 12, 2008 at 9:51 am
Yeah, Matt... I was afraid it would be something of that nature... too bad.
Micheal, I'm right there with you... compromise is to write code to generate static code. Have done similar in the past to create audit triggers on a bazillion tables in Oracle... haven't had to gen code for upserts on SQL Server, yet, but your general approach is the same one I'd take.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2008 at 11:10 am
I just had a thought.
Maybe something like this would work:
create proc @Update
(@SourceTable_in varchar(100),
@TargetTable_in varchar(100))
as
declare @Script varchar(max)
select @script = 'delete from ' + @targettable_in +
' where id in (select id from ' + @sourcetable_in + ') '+
'set identity_insert ' + @targettable_in + ' on ' +
'insert into ' + @targettable_in + ' select * from ' +
@sourcetable_in
exec sp_executesql @script
It's going to hit the transaction log like a ton of bricks, but it might accomplish what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2008 at 11:14 am
22 answers here too!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97159
N 56°04'39.16"
E 12°55'05.25"
February 12, 2008 at 12:53 pm
Peso,
Im the one who logged that topic on SQLTeam as well.I currently have got the INSERT part working with some help.Currently working on Update.
I know it is a very unreasonable way to accomplish something that is not so tough...but despite many suggestions..My boss[:alien:] is still sticking with his decision.
I will let all of you know if I get the Update part working as well or If I need some help.
Thank You All.
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
February 12, 2008 at 5:29 pm
Lookup_BI (2/12/2008)
but despite many suggestions..My boss[:alien:] is still sticking with his decision.
Crud... I've been there and done that... having a boss that wears a big ol' chip on his shoulder...
Sure sorry I've not been of much help so far... sounds like you know what you need to do on this problem, but if that's a misinterpretation on my part, please come back and we'll see if we can provide something more reasonable in the form of help.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2008 at 9:37 pm
Lookup_BI (2/12/2008)
Peso,Im the one who logged that topic on SQLTeam as well.I currently have got the INSERT part working with some help.Currently working on Update.
I know it is a very unreasonable way to accomplish something that is not so tough...but despite many suggestions..My boss[:alien:] is still sticking with his decision.
I will let all of you know if I get the Update part working as well or If I need some help.
Thank You All.
Well, got to agree with everybody else here. I would present these other options:
1) Backup/Restore - backup current database, restore to past DB on a daily basis
2) Integration Services - create an SSIS package, since this is basically an ETL process from the current system to a past/reporting/archive system.
3) Download Quests SQL Comparison Suite for SQL Server (sorry, I know I should be recommending Redgate's SQL Compare, which I have and use all the time - but the OP said they are not willing to spend the big dollars and I cannot see how spending $0 is too large).
Now, what is going to your dynamic solution the minute somebody comes in later and changes the schema on the current system?
Anyways, good luck...:)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply