Insert substring of previous value

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

General SQL Server Forums

New to SQL Server Programming

Insert substring of previous value

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-20 : 09:37:09

In doing a series of inserts, i need the following. The second line of this insert "Levyno" needs to look at the insert for the previous column ("Receiptno") and insert a substring of that value. Specifically, i need levyno to be the first six and last six characters of that insert's receiptno. Obviously, i'm having trouble with the syntax for that Select Substring command.

USE CIVIL

INSERT INTO CIVIL.dbo.SUBJECTS
(
RECEIPTNO,
LEVYNO,
VALUES
(
'GCSO11000000100ENF',
SELECT SUBSTRING (RECEIPTNO,1,6) FROM civil..Subjects,

Help?

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-10-20 : 09:51:22

This may work if you can plug it into your insert statement

select left(RECEIPTNO, 6) + right(RECEIPTNO, 6) FROM Civil

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-20 : 10:27:03

I don't know why or where i'm getting a syntax nightmare on this, but here is one whole insert. As soon as i plug in the value for the Select on the value for the Levyno, it all gets thrown into syntax hell:

USE CIVIL
INSERT INTO CIVIL.dbo.SUBJECTS
(
RECEIPTNO,
LEVYNO,
MNINO,
NAME,
DBA,
STREET,
CITY,
ALTSTREET,
ASSIGNEDTO,
SERVEDBY,
OFFICERDATE,
OFFICERTIME,
SVCDATE,
SVCTIME,
SVCTYPE,
SVCINFO1,
SVCINFO2,
COMMENTS,
FEESEARNED,
UNIQUEKEY,
OTHER
)
VALUES
(
'GCSO11000000100ENF',
select left(RECEIPTNO, 6) + right(RECEIPTNO, 6) FROM Civil,
'GCSOMNI0000000026792',
'Jane Doe',
'',
'145 No RD.',
'CHATTAHOOCHEE',
'',
'Smith',
'CPL. JOE Smith',
'1/2/07',
'1654',
'1/2/07',
'GCSO11',
'A',
'Jane Doe',
'',
',, ',
'20',
SCADMIN.dbo.fnsc_GetUniqueID(NEWID()),
''
);

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-20 : 11:07:23

Disregard. My select statement was not fully qualified. Should have been:
(select left(RECEIPTNO, 6) + right(RECEIPTNO, 6) FROM Civil.dbo.subjects),

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-20 : 11:28:31

Good grief. Now, without any syntax errors to deal with, when i execute this:

INSERT INTO CIVIL.dbo.SUBJECTS
(
RECEIPTNO,
LEVYNO,
MNINO,
NAME,
DBA,
STREET,
CITY,
ALTSTREET,
ASSIGNEDTO,
SERVEDBY,
OFFICERDATE,
OFFICERTIME,
SVCDATE,
SVCTYPE,
SVCINFO1,
SVCINFO2,
COMMENTS,
FEESEARNED,
UNIQUEKEY,
OTHER
)
VALUES
(
'GCSO11000000100ENF',
(
SELECT LEFT(RECEIPTNO, 6) + RIGHT(RECEIPTNO, 6)
FROM Civil.dbo.subjects
),
'GCSOMNI0000000026792',
'Jane Doe',
'',
'145 No RD.',
'CHATTAHOOCHEE',
'',
'Smith',
'CPL. JOE Smith',
'1/2/07',
'1654',
'1/2/07',
'A',
'Jane Doe',
'',
',, ',
'20',
SCADMIN.dbo.fnsc_GetUniqueID(NEWID()),
''
);

I get the following error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-10-20 : 12:20:20

The select statement 'SELECT LEFT(RECEIPTNO, 6) + RIGHT(RECEIPTNO, 6) FROM Civil.dbo.subjects' is returning more than one row per one row of the insert statement. You should try doing a select into statement instead of what is being done here.

Sorry I cannot reproduce the whole thing - need to go home - but something like:

Select LEFT(RECEIPTNO, 6) + RIGHT(RECEIPTNO, 6), ColumnA, ColumnB etc
INTO subjects

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-10-20 : 12:50:39

I must be missing something...

If you are inserting the value 'GCSO11000000100ENF', and the next value needs to be the left and right 6, why not hard code them also?
short example:


USE CIVIL
INSERT INTO CIVIL.dbo.SUBJECTS
(
RECEIPTNO,
LEVYNO,
MNINO
)
VALUES
(
'GCSO11000000100ENF'
,'GCSO11100ENF' --<--- no need to SELECT if you already know the value
,'GCSOMNI0000000026792'
)

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-21 : 06:50:18

Because that GCSO number changes with each insert statement. Therefore the LevyNo will be different with each insert.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-10-21 : 11:43:00

Where is the GCSO number coming from? It looks hard coded in your example to me.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-21 : 11:50:35

The GCSO is hardcoded into each of the 60K inserts, but it is different in each insert. The LevyNo is based upon each insert's Caseno. The original data was imported from Excel, sans any levyno. I've also been looking at some way of dis-concatenating the caseno on the excel side using a function, as that would allow me to quickly hardcode the relevant levyno into each entry. Guidance from excel boards on the WWW indicate that i can't do that. I'm not sure that's accurate or not.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-10-24 : 09:39:39

I would then simply insert all the data, then go back and update the levyno.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources