Help with large table update and insert

Question

Post reply

Help with large table update and insert

thomashohner

SSCrazy Eights

Points: 9856
More actions
October 10, 2014 at 4:39 pm

#294111

We have a report package that is downloaded by customers and then it is periodically updated as we find bugs or make changes. In the install scripts the Query Stored Procedures are DROPED if they exists and then the script recreates/creates them. However part of this install/update script is several look up tables. Which so far has been done the exact same way in the installer if TABLE exists then it is dropped an rewritten. So far no major issues, until now. They have added active reports to the package meaning the customer can now make additions to the look up tables. So how do we get around not wiping out their additions to the tables when they do a report update while still be able to do fresh installs and updates?
I started with doing some DYNAMIC SQL. IF NOT EXIST THEN EXEC ('Create table,') the tricky part is what comes after. I started by saying EXEC ('IF 0=(SELECT COUNT(*) FROM TABLE
BEGIN
INSERT
')
However in one table alone there are 4500 lines of inserts it will take me all weekend to do that as dynamic SQL is there a faster better way of doing all of this?
Thanks in advance
Yes, I'm still new:-P
***SQL born on date Spring 2013:-)

Viewing 10 posts - 1 through 9 (of 9 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Do you need to do it once? Or do you need to do it automatically at any time?

SSMS has an option to script your procedures as dynamic SQL, you just need to set it to true.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 2

Thanks Luis for your reply,

this is what that gives me IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cusUDSImmunizationCategoryProcedures_2014]') AND type in (N'U'))

but I need to check if the table is empty before continuing with the insert because either the table is already there from the initial install or this is a fresh install. So I started this

IF 0=(SELECT COUNT(*) AS Rn FROM [dbo].[cusUDSImmunizationCategoryProcedures_2014])

BEGIN

INSERT [dbo].[cusUDS6b_obsnum_2014] ([HDID], [LineNumber], [Description], [OBSName], [MlCode], [ReportYear], [MonthsToCheck], [Created], [CreatedBy], [LastModified], [LastModifiedBy]) VALUES (N'50472', N'6bHx', N'vaginal hysterectomy, laparascopic, hx of', N'LAPRVAGHYSHX', N'MLI-50472', 2014, NULL, NULL, NULL, CAST(0x0000A3A900000000 AS DateTime), NULL)

I'm still learning the dynamic SQl so please be patient.

***SQL born on date Spring 2013:-)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Where are those 4500 lines of inserts coming from?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 4

right after that first line I showed in the code. That's my problem Idont want to go through that whole thing putting in ' ' ' ' ' to get the dynamic SQL to work.

***SQL born on date Spring 2013:-)

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 5

I am hoping you much smarter gentlemen than myself know better ways of doing this. Other wise I have a long weekend ahaead...:crazy:

***SQL born on date Spring 2013:-)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Have you tried using the QUOTENAME()function?

It can be really helpful for this kind of things.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

And I was asking where do the insert values come from, not where they go.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 8

My apologies Luis, I have already had a beer or two for the night. The Inserts come from scripting the table similar to the image that you showed me as we built this look up table on our test db to push out to customers. I have never heard of that function. I will look it up now.

***SQL born on date Spring 2013:-)

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 9

Thanks Luis QUOTENAME() put me on the right path. Was able to get the script complete quickly. Thanks:-)

***SQL born on date Spring 2013:-)