May 17, 2011 at 12:53 pm
I have a string which has 58 values with pipe separator (|)
Now, i have to insert these 58 values as a record into a exiting table which also has 58 columns.
when i try to do using below code it throws error
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
declare @Col2 varchar(8000)
declare @Command varchar(8000)
select @Col2='|AuthoriaUsers|244375064|17875716|1.14||0000037249||0000037249||||DM4||||||||||||||||||||0000037249|||||||||||||||||||||||en_US|en_US|CAD|T1_US|'
insert into ComPRoles
(DocumentType,SenderId,ReceiverId,Version,Id,UserSourceSysRefKey,UserInactive,Login,Password,PasswordRecoveryPhrase,PasswordRecoveryAnswer,
Role1,Role2,Role3,Role4,Role5,Role6,Role7,Role8,Role9,Role10,ProtectedRole1,ProtectedRole2,ProtectedRole3,ProtectedRole4,
ProtectedRole5,ProtectedRole6,ProtectedRole7,ProtectedRole8,ProtectedRole9,ProtectedRole10,PersonSourceSysRefKey,CustomName1,
CustomBody1,CustomName2,CustomBody2,CustomName3,CustomBody3,CustomName4,CustomBody4,CustomName5,CustomBody5,CustomName6,
CustomBody6,CustomName7,CustomBody7,CustomName8,CustomBody8,CustomName9,CustomBody9,CustomName10,CustomBody10,PasswordChangeRequired,
StartPage,ContentLocale,ApplicationLocale,Currency,DateAndNumberFormatLocale)
SELECT SUBSTRING(@Col2,N+1,CHARINDEX('|',@Col2,N+1)-N-1)
FROM Tally --cross join #TblSecRow
WHERE N < LEN(@Col2)
AND SUBSTRING(@Col2,N,1) = '|'
am i missing some thing??
OR is there any easy way to accomplish this task?
May 17, 2011 at 2:07 pm
Yes, the string split that you are using returns the values as ROWS and you need them as COLUMNS. You can use a PIVOT or crosstab to change your rows into columns. You have a link in your own signature.
I also notice that your table is not normalized. If this is not a staging table, you would be wise to consider normalizing the table before proceeding.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 17, 2011 at 2:29 pm
There's always the KISS method depending on what you need this for.
DECLARE @col2 varchar(MAX),
@SQL varchar(MAX)
SELECT @SQL = ''
select @Col2='|AuthoriaUsers|244375064|17875716|1.14||0000037249||0000037249||||DM4||||||||||||||||||||0000037249|||||||||||||||||||||||en_US|en_US|CAD|T1_US|'
SELECT @Col2 = STUFF(REPLACE(@Col2,'|',''','''),1,2,'')
SELECT @Col2 = LEFT(@Col2,LEN(@Col2)-2)
SELECT @SQL = 'insert into ComPRoles
(DocumentType,SenderId,ReceiverId,Version,Id,UserSourceSysRefKey,UserInactive,Login,Password,PasswordRecoveryPhrase,PasswordRecoveryAnswer,
Role1,Role2,Role3,Role4,Role5,Role6,Role7,Role8,Role9,Role10,ProtectedRole1,ProtectedRole2,ProtectedRole3,ProtectedRole4,
ProtectedRole5,ProtectedRole6,ProtectedRole7,ProtectedRole8,ProtectedRole9,ProtectedRole10,PersonSourceSysRefKey,CustomName1,
CustomBody1,CustomName2,CustomBody2,CustomName3,CustomBody3,CustomName4,CustomBody4,CustomName5,CustomBody5,CustomName6,
CustomBody6,CustomName7,CustomBody7,CustomName8,CustomBody8,CustomName9,CustomBody9,CustomName10,CustomBody10,PasswordChangeRequired,
StartPage,ContentLocale,ApplicationLocale,Currency,DateAndNumberFormatLocale)
SELECT ' + @Col2
PRINT @SQL
-- EXEC ( @SQL )
May 17, 2011 at 2:45 pm
I like this solution, though the numerics may give him some issues depending upon the datatypes in the table. In addition, an empty string may be undesired if NULL is preferred.
Still & all, the KISS is a great solution.
OP, try this before doing your insert. Replacing the pipe with pipe space pipe gives it something to process. You can always LTRIM(RTRIM( each row before you insert:
select @Col2=REPLACE('|AuthoriaUsers|244375064|17875716|1.14||0000037249||0000037249||||DM4||||||||||||||||||||0000037249|||||||||||||||||||||||en_US|en_US|CAD|T1_US|','|',' | ')
edited 20110518 to modify the code tag to code=sql
May 17, 2011 at 6:21 pm
May 18, 2011 at 3:51 pm
Garadin (5/17/2011)
Yeah, you'd probably want to throw a NULLIF('<data>' ,'') in the replace as well.
Only if you don't want nulls in the database, right?
That would totally depend upon his table's design & the needs of the application or business users.
I'm sure there is a debate of the evils of null fields somewhere around here, but sometimes, the answer is nothing. 😉
backs away slowly, closing the can o' worms
May 18, 2011 at 4:29 pm
No, to replace his empty strings with NULLS. Most of the time you wouldn't really want to insert empty strings into fields. As you said though, it depends.
May 19, 2011 at 1:32 am
Using dynamic SQL like this is equal to the last S in KISS - Stupid!
SQL Injection attack waiting to happen.
May 19, 2011 at 2:12 am
peter.row (5/19/2011)
Using dynamic SQL like this is equal to the last S in KISS - Stupid!SQL Injection attack waiting to happen.
And your secure alternative solution is?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 19, 2011 at 2:43 am
If you are determined to use dynamic T-SQL then use a parameterised sp_executesql statement.
Even better I'd turn this into a parameterised stored procedure and do the string splitting on the C# side (or whatever other language is calling this) assuming that the data is submitted to the code in this way. Of course if the code is concatenating the data first and then passing to SQL like that then that's just even more kinds of wrong-ness.
May 19, 2011 at 6:14 am
peter.row (5/19/2011)
Using dynamic SQL like this is equal to the last S in KISS - Stupid!SQL Injection attack waiting to happen.
Could be. That's why I said "depending on what you need this for". It also doesn't handle there not being enough columns supplied and a whole slew of other problems. He hasn't really given his requirements. Perhaps this is a one time data load or something that will be done intermittently by himself to load in files, I have no idea.
May 19, 2011 at 7:01 am
You are right of course, I apologise for my harshness. :blush:
I guess in my career dynamic SQL has been a last resort kind of thing in an attempt to avoid having to even think about SQL injection attacks unless absolutely necessary.
May 19, 2011 at 8:59 am
Garadin. I call you a magician. wonderful. works great.
Yes, This is one time load. task accomplished
May 19, 2011 at 11:59 am
peter.row (5/19/2011)
You are right of course, I apologise for my harshness. :blush:I guess in my career dynamic SQL has been a last resort kind of thing in an attempt to avoid having to even think about SQL injection attacks unless absolutely necessary.
No worries, I tend to make rather bold statements myself, and it was a good thing to point out.
Glad we could help.
May 19, 2011 at 12:46 pm
Totally and 100% agree. I'm sorry, I haven't used the NULLIF function before. Thanks for pointing it out; my toolbox now has a new toy! 😀
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply