SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sequential 'Alter Table' queries


Sequential 'Alter Table' queries

Author
Message
Mark Tarquini
Mark Tarquini
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 149
Since my last visit, I've gotten by with just using my "Teach yourself SQL" book and lots of web searches. Now it's time to 'talk' to real DBAs again. I am working on creating Stored Procedures for a process that I do every single month. I have two done already and want to make another.

I have the following query lines that work, but I can only seem to run them if I select two lines at a time and then press F5. What am I missing in my query to just have these six alter statements to just run all in a row and then proceed with processing the statements that come after? (creating a new table based off these smaller tables that were imported from CSV files).

The errors I get are similar to these:

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near ''.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ''.

What does it not like about my single quotes?


--Remove providers that are not on the CPC+ Accepted Physician List (Oct 2017)
delete from Mem_BCBSMCom where NPI not in (Select NPI from CMS_Accepted_Providers)

--Add column Program column to table
alter table [Mem_BCBSMCom] add [Program] varchar(8);
update [Mem_BCBSMCom] set [Program] = 'BCBSMCom';

--Add column Program column to table
alter table [Mem_BCBSMMA] add [Program] varchar(7);
update [Mem_BCBSMMA] set [Program] = 'BCBSMMA';

--Add column Program column to table
alter table [Mem_BCN] add [Program] varchar(3);
update [Mem_BCN] set [Program] = 'BCN';

--Add column Program column to table
alter table [Mem_HAP] add [Program] varchar(3);
update [Mem_HAP] set [Program] = 'HAP';

--Add column Program column to table
alter table [Mem_Humana] add [Program] varchar(6);
update [Mem_Humana] set [Program] = 'Humana';

--Remove providers that are not on the CPC+ Accepted Physician List (Oct 2017)
delete from Mem_Priority where [PCP NPI] not in (Select NPI from CMS_Accepted_Providers)

--Add column Program column to table
alter table [Mem_Priority] add [Program] varchar(8);
update [Mem_Priority] set [Program] = 'Priority';


-Mark

MS-SQL 2016 Enterprise, Azure Hosted.
Techie/Sysadmin by trade; completely new to T-SQL. I hardly call myself a DBA. Ive got a lot to learn and doing my best.
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: General Forum Members
Points: 81488 Visits: 9184
I don't get any syntax errors. That error must be because of some text/code before or after the main code.

But you will likely get exec errors, since the column won't exist in the table at the time SQL parses the proc.

In these types of situations, I often use dynamic SQL instead:


--Remove providers that are not on the CPC+ Accepted Physician List (Oct 2017)
delete from Mem_BCBSMCom where NPI not in (Select NPI from CMS_Accepted_Providers)

--Add column Program column to table
exec('alter table [Mem_BCBSMCom] add [Program] char(8);')
exec('update [Mem_BCBSMCom] set [Program] = ''BCBSMCom'';')

--Add column Program column to table
exec('alter table [Mem_BCBSMMA] add [Program] char(7);')
exec('update [Mem_BCBSMMA] set [Program] = ''BCBSMMA'';')

--Add column Program column to table
exec('alter table [Mem_BCN] add [Program] char(3);')
exec('update [Mem_BCN] set [Program] = ''BCN'';')

--etc.



SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207996 Visits: 24241
While I am sure that there are good reasons for doing so on occasions, including DDL to modify your database schema within a stored procedure is not best practice. For one reason, it stuffs up source control (you can no longer compare your source project with the target database to check for drift, for example). Wherever practically possible, I'd recommend keeping your DDL away from DML.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)SSC Guru (158K reputation)

Group: General Forum Members
Points: 158583 Visits: 22556
Maybe you have a BEGIN without END? Or something similar.


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
Mark Tarquini
Mark Tarquini
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 149
Phil Parkin - Monday, February 5, 2018 10:39 AM
While I am sure that there are good reasons for doing so on occasions, including DDL to modify your database schema within a stored procedure is not best practice. For one reason, it stuffs up source control (you can no longer compare your source project with the target database to check for drift, for example). Wherever practically possible, I'd recommend keeping your DDL away from DML.

Thank you for this insight. For this particular monthly occasion, I was originally adding the columns that I needed into the CSV file, but I found doing it that way each month was tedious through Excel, or through a command line in Windows. I found the DDL method much easier. I haven't come across another reason to do it this way in anything else yet. I will keep this in mind though moving forward.

Side note - I had to look up DDL and DML again because I couldn't remember what they were. Smile


-Mark

MS-SQL 2016 Enterprise, Azure Hosted.
Techie/Sysadmin by trade; completely new to T-SQL. I hardly call myself a DBA. Ive got a lot to learn and doing my best.
RandomStream
RandomStream
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2849 Visits: 648
I suggest you mask actual table/db/server names in future posts.
Zidar
Zidar
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 Visits: 467
Maybe you should read something on relational database design, independent of SQL. If you need to constantly add columns to your tables, then your problem is of a different sort. MS SQL database is not used for teh same things as CSV or Excel. Whatever you are doing to CSV files or Excel files, should not mechanically be reproduced in any database system. That is a bigger problem than sinatx error in some script.
Mark Tarquini
Mark Tarquini
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 149
Zidar - Monday, February 5, 2018 1:35 PM
Maybe you should read something on relational database design, independent of SQL. If you need to constantly add columns to your tables, then your problem is of a different sort. MS SQL database is not used for teh same things as CSV or Excel. Whatever you are doing to CSV files or Excel files, should not mechanically be reproduced in any database system. That is a bigger problem than sinatx error in some script.

Zidar,
At the moment I'm following what the previous "DBA" was doing. Every month we have to assemble a report. It is significantly easier to assemble the report using T-SQL Queries. I attempted to try it with Excel at one point and it was a fruitless effort. The sheets became too large. The data in the database is not used for storage of any kind. I'm just using SQL to manipulate the data because as the previous person found trying to do it with Excel was just a waste of time. All the data import into SQL is in CSV or Spreadsheets, though, which are quite large, 50,000 rows or more. In a nutshell this is the process:

0. Remove prior months tables.
1. Download membership 'rosters' CSVs from insurance companies (which are all with different column headers)
2. Import the rosters into their own tables.
3. Add the "Program" column to each table and fill the program row with the program name for that particular roster.
4. Filter the Membership Rosters to remove certain records.
5. Join Membership Rosters into one large Roster
6. Use Unions and Joins to create two new tables on certain data columns.
7. Export and send records from two new table created from Step 6 to parties requiring them.

I realize this is not glorious DBA (and I use the title DBA loosely) work, but it is far more efficient than Excel.


-Mark

MS-SQL 2016 Enterprise, Azure Hosted.
Techie/Sysadmin by trade; completely new to T-SQL. I hardly call myself a DBA. Ive got a lot to learn and doing my best.
jasona.work
jasona.work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41983 Visits: 15931
usererror - Monday, February 5, 2018 1:53 PM
Zidar - Monday, February 5, 2018 1:35 PM
Maybe you should read something on relational database design, independent of SQL. If you need to constantly add columns to your tables, then your problem is of a different sort. MS SQL database is not used for teh same things as CSV or Excel. Whatever you are doing to CSV files or Excel files, should not mechanically be reproduced in any database system. That is a bigger problem than sinatx error in some script.

Zidar,
At the moment I'm following what the previous "DBA" was doing. Every month we have to assemble a report. It is significantly easier to assemble the report using T-SQL Queries. I attempted to try it with Excel at one point and it was a fruitless effort. The sheets became too large. The data in the database is not used for storage of any kind. I'm just using SQL to manipulate the data because as the previous person found trying to do it with Excel was just a waste of time. All the data import into SQL is in CSV or Spreadsheets, though, which are quite large, 50,000 rows or more. In a nutshell this is the process:

0. Remove prior months tables.
1. Download membership 'rosters' CSVs from insurance companies (which are all with different column headers)
2. Import the rosters into their own tables.
3. Add the "Program" column to each table and fill the program row with the program name for that particular roster.
4. Filter the Membership Rosters to remove certain records.
5. Join Membership Rosters into one large Roster
6. Use Unions and Joins to create two new tables on certain data columns.
7. Export and send records from two new table created from Step 6 to parties requiring them.

I realize this is not glorious DBA (and I use the title DBA loosely) work, but it is far more efficient than Excel.

Sounds like a job for an SSIS package, rather than a stored procedure.
(Please note, I'm not an SSIS developer, but I've got developers who do similar workloads of importing data)

As for the 0 step, why remove the prior tables? I'm presuming that indicates you drop them? Why not simply truncate the tables, which would keep the table structures (columns, etc,) reset any ID values, and save some log thrashing? Or, do the headers in the files you're importing change from month-to-month?

Mark Tarquini
Mark Tarquini
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 149

jasona.work - Tuesday, February 6, 2018 6:16 AM

Sounds like a job for an SSIS package, rather than a stored procedure.
(Please note, I'm not an SSIS developer, but I've got developers who do similar workloads of importing data)

As for the 0 step, why remove the prior tables? I'm presuming that indicates you drop them? Why not simply truncate the tables, which would keep the table structures (columns, etc,) reset any ID values, and save some log thrashing? Or, do the headers in the files you're importing change from month-to-month?

JasonA -

I'm not quite there yet on my skillset. I have tested a few SSIS import packages (for other purposes) but they stopped working after our IT department reimaged my workstation. The difficulty I had with my previous SSIS packages is that our SQL 2016 server is remotely hosted at Microsoft's Azure site. I cannot get our IT vendor to budge on setting up a VPN tunnel to Azure and joining our SQL Server to our local domain. So I have no AD authentication. It's extremely irritating at times to have to do everything with SQL Authentication, but I digress...

I had been removing the previous tables just because it was easier and I am, again, just not there yet with my skillset. I'm able to produce the results needed for our team on a daily basis, but the time to learn is not always there and usually just on getting done what I need done quickly. I'll get there, it's probably going to take years. Smile But in theory the headers on the source tables should be consistent each month, but one vendor did alert us that there were some changes coming up later this year.


-Mark

MS-SQL 2016 Enterprise, Azure Hosted.
Techie/Sysadmin by trade; completely new to T-SQL. I hardly call myself a DBA. Ive got a lot to learn and doing my best.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search