SQL Server: Stored Procedure coding

Question

I have a staging table containing a varchar(1000) column which contains strings like

some text\more text\text string again\still another string\one more string.

The records can contain either 8 or 9 occurrences of the \, which I can readily determine

What I need to do is insert the staged records into a *real* Table, with each string inserted into a specific column. So I need to determine

If string contains 8 fields, then insert into these target table columns else if string contains 9 fields, then insert into these target table columns otherwise skip

I am quite new to Procedural code and don't understand how to implement this If, Then, Else logic. Can anyone give me hand?

Show us the code you wrote when you attempted this and found it to not produce what you wanted. — dfundako, May 08 '18 at 16:30
Sure we can help. Can you post some information so we have a chance? Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, May 08 '18 at 16:37
Take a peek at https://stackoverflow.com/questions/49541435/parsing-out-a-single-column-into-multiple/49542466#49542466 — John Cappelletti, May 08 '18 at 16:51
Is there some reason why this isn't as simple as googling `SQL Server IF ELSE`? — Tab Alleman, May 08 '18 at 17:17
Ok... firstly, your question isn't particularly clear. That's ok, but bear in mind its hard to help. One suggestion I would make however is that SQL is not well suited to parsing strings (ie. delimited by slashes). Have a look to functions that can turn your string into a datatable, and you could do joins and stuff. — Black Light, May 08 '18 at 20:27

score 1 · Accepted Answer · answered May 09 '18 at 07:36

Hope this code can help you to solve your probem.

create table myTable (
      c1  varchar(1024)
    , c2  varchar(1024)
    , c3  varchar(1024)
    , c4  varchar(1024)
    , c5  varchar(1024)
    , c6  varchar(1024)
    , c7  varchar(1024)
    , c8  varchar(1024)
    , c11  varchar(1024)
    , c12  varchar(1024)
    , c13  varchar(1024)
    , c14  varchar(1024)
    , c15  varchar(1024)
    , c16  varchar(1024)
    , c17  varchar(1024)
    , c18  varchar(1024)
    , c19  varchar(1024)
);

declare @string as varchar(1024) = 'some text\more text\text string again\still another string\one more string\s6\s7\s8';
declare @col_values as table ( idx int identity(1,1), value varchar(1024) );
declare @row_cnt as integer

-- Split the string into separate values
insert into @col_values
select t.value  from string_split (@string,'\') t;
-- Get number of values in splited string
set @row_cnt =  @@ROWCOUNT

if @row_cnt = 8
    begin
        insert into myTable(c1,c2,c3,c4,c5,c6,c7,c8)
            values ( (select value from @col_values where idx = 1)
                   , (select value from @col_values where idx = 2)
                   , (select value from @col_values where idx = 3)
                   , (select value from @col_values where idx = 4)
                   , (select value from @col_values where idx = 5)
                   , (select value from @col_values where idx = 6)
                   , (select value from @col_values where idx = 7)
                   , (select value from @col_values where idx = 8)
                   );

    end;
else
    begin
        if @row_cnt = 9 
            begin
                insert into myTable(c11,c12,c13,c14,c15,c16,c17,c18,c19)
                    values ( (select value from @col_values where idx = 1)
                           , (select value from @col_values where idx = 2)
                           , (select value from @col_values where idx = 3)
                           , (select value from @col_values where idx = 4)
                           , (select value from @col_values where idx = 5)
                           , (select value from @col_values where idx = 6)
                           , (select value from @col_values where idx = 7)
                           , (select value from @col_values where idx = 8)
                           , (select value from @col_values where idx = 9)
                           );
            end;
        else
            -- If there is no 8 or 9 values then raise an exception
            raiserror(N'Number of elements is %d',16,0,@row_cnt);
    end;

select * from myTable ;

drop table myTable ;
go

SQL Server: Stored Procedure coding

1 Answers1