split a variable into a table variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

split a variable into a table variable

Author

Topic

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-17 : 13:52:58

I have to split a "fixed" string (@subscriberData nvarchar(305)) into a table

Declare @subscriber table
        (
            groupNum        char(5),
            location        char(5),
            lastname1       nvarchar(12),
            lastname2       nvarchar(12),
            firstname       nvarchar(12),
            initial         char(1),
            birthdate       char(8),
            sex             char(1),
            relation        char(1),
            coverage        char(1),
            address1        nvarchar(36),
            address2        nvarchar(36),
            city            nvarchar(25),
            state           char(2),
            zipCode         nvarchar(9),
            dateHired       char(8),
            status          char(1),
            otherDental     char(1),
            cobraMonths     char(2),
            cobraExp        char(8),
            cobraTerm       char(8),
            actionCode      char(1),
            actEffecDate    char(8),
            regionCode      char(1),
            municipality    char(4),
            indivSS         char(9),
            indivSufix      char(2),
            COB3            char(1),
            primaryCenter   nvarchar(10),
            HICode          nvarchar(12),
            ODSIfamilyID    nvarchar(11),
            alternateID     nvarchar(25),
            msg             nvarchar(15),
            contract        nvarchar(13)
        )

but some transactions won't have all the fields though but
existing fields must be in a specific possition

I'm thinking to do this

set @subscriberData= @subscriberData+ replicate(' ',305 -len(@subscriberData))
Insert into @subscriber
left(@subscriberData,5),
subString(@subscriberData,6,5),
subString(@subscriberData,11,12),
subString(@subscriberData,24,12),
subString(@subscriberData,37,12),
.
.
.

But I would like to change the string to a pipe delimited string and do something as
I would do it in VB

myArray = split(subscriberData,"|")
rec.addNew
For i = 0 to rec.recordCount-1
     rec.Fields(i) = myArray(i)
next

Any suggestions??
I have two other strings that will be in other two tables with fewer fields but with the possibility of having more than one record

By the way is there a limit for Stored procedures?

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-17 : 14:28:06

Why not bcp it in to a staging table using a format card?

Brett

8-)

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-17 : 14:54:30

what you mean by staging table? (I looked in dictionary.com and the translation was
"tabla de estacionamiento" which means "parking table")
would the format card be a format file?
I have worked with bulk insert and format files but not with BCP. Is BCP preferable than
Bulk Insert (within a sp using variable tables)?

I can change anything to make the SP more efficient, exept for the amount of data received,
so all suggestions and critiques are welcome ;-)

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-17 : 15:07:54

Sorry...my DB2 is getting the way...yes format file

A staging table is a table that you can load to so that you can audit and check your data before placing it to it's final destination...

Format files are perfect for fixed width data

I like to make my staging tables all have data types as varchar..that way the load shouldn't fail.

You can then confirm if dates are truly valid, numbers are numbers, there are no keys, ect

Just call bcp using xp_cmdshell

Brett

8-)

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-17 : 16:41:29

Ok, sounds good I do something like that to process bulk data from clients. But I truncate the table
every time I start running a new file

Thanx Brett!! Sometimes I get stuck in simple things, don't know why. I thought it wouldn't be a good
idea to do it for one transaction. But I guess I was wrong. I think is simpler this way.

Let me see if I got it right

1.I create the three tables I need(one time only)
2.Since the SP will be accessed by many users and I don't want it to get gigantic I'll generate
  a transaction ID @transID=getdate() and add it as an additional field to the strings that are going 
  to be bcp to the three staging tables
3.BCP the strings into the tables
4.Make all the validations
5.If no violations to the business rules perform the transaction
6.Delete records with transactionID = @transID

One thing I still don't have clear, can I use bcp with variable tables??
If so I'll just need steps 3 to 5

Well I have to go for today. thanx for the help. I'll try simple BCP with a variable table for a start

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-18 : 10:38:21

I tried

EXEC xp_cmdshell 'bcp @ee in \\myServer\fromFTP\myFile.txt -f \\myOtherServer\myFixedFormat.fmt'

and i got an error

Server: Msg 2812, Level 16, State 62, Line 47
Could not find stored procedure 'xp_cmdshell'.

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-18 : 10:46:19

processing the same thing on the server I got

Password: 
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@ee'.
NULL

so I think the bcp does not work with table variables because I declared the table just before the bcp

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-18 : 11:03:24

the BCP didn't work for me not with variable table neither with temporary table
so I tried Bulk Insert with variable table and it didn't work either but
it worked with temporary table

bulk insert #ee
from '\\myServer\fromFTP\myFile.txt'
with (FORMATFILE = '\\myOtherServer\myFixedFormat.fmt')

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-22 : 08:07:31

I'm not receiving a file but a long string so bulk insert doesn't work either. I'm sticking to the original plan.

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-22 : 09:08:43

Doesn't make sense.

Also I wouldn't think you could use a temp table and then try to use xp_cmdshell and bcp since they would be separate spids...just create a permanent table...

And what's with the data?

Where's it created from?

Brett

8-)

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-11-22 : 12:57:09

There are three applications that create and update contracts see
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41947
and I want to centralice the logic for the transaction so it will be easier to maintain
the IT director requests changes and asks to revert changes more often than she should and
I want to make my life a bit simpler until I find a much better job or she retires which
would be much better (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41407)

if you have a better idea i will appreciate it.

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources