how to insert characters by default

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 2008 Forums

Transact-SQL (2008)

how to insert characters by default

Author

Topic

nagarjuna_a2006
Starting Member

16 Posts

Posted - 2014-03-11 : 02:57:30

We have a Student table with the below columns and data in database
SId SName SCourse
123 Student1 Java
124 Student2 DotNet
125 Student3 C
126 Student4 SQL

i would like to add a character 'S' default for the SId column whenever i inserted a record in the table
output format as follows
SId SName SCourse
S123 Student1 Java
S124 Student2 DotNet
S125 Student3 C
S126 Student4 SQL

Please post the answers

Regards,
Nagarjuna

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-11 : 03:08:55

[code]
;with aCTE
AS
(select 123 as SId,'Student1' as SName, 'Java' as SCourse union all
select 124,'Student2','DotNet' union all
select 125,'Student3','C' union all
select 126,'Student4','SQL')

SELECT
'S' + CAST(Sid as VARCHAR(30)) as [Sid]
,Sname
,Scourse
FROM
aCTE
[/code]

all the magic is here:
[code]'S' + CAST(Sid as VARCHAR(30)) as [Sid] [/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-11 : 03:09:33

output


Sid	Sname	Scourse
S123	Student1	Java
S124	Student2	DotNet
S125	Student3	C
S126	Student4	SQL

sabinWeb MCP

nagarjuna_a2006
Starting Member

16 Posts

Posted - 2014-03-11 : 03:20:48

Thanks a lot for your posting.
but i want to store the same thing in the database instead of showing in the result set. i will just pass only student id as 123 as shown in the below statement

Insert Student values(123,'Student1','Java')

in the table it should store as

SId SName SCourse
S123 Student1 Java

do i need to change anything in the table definition

Thanks in advance

Regards,
Nagarjuna

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-11 : 03:22:58

first of all, what is your table definition? the important field is Sid; what type is


Insert Student values('S' + cast(123 as varchar(30)) ,'Student1','Java')

sabinWeb MCP

nagarjuna_a2006
Starting Member

16 Posts

Posted - 2014-03-11 : 03:31:24

Please have the table definition

Create Table Student
(
SId int Primary Key,
SName Varchar(10),
SCourse Varchar(15)
)

Regards,
Nagarjuna

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-11 : 03:36:56

there are some discutions to do about this;

-why to do need the 'S' character to be store (To store this char, you need to change the tbl definition to something like this [SId varchar(30) Primary Key],but
it's not recomanded . You will have to also change the fields for the related table, that have FK to Sid (used in join)

from my point , i will not change , the tbl definition, to store the character 'S'.

sabinWeb MCP

nagarjuna_a2006
Starting Member

16 Posts

Posted - 2014-03-11 : 04:59:48

yeah, i don't have any problem for changing the table definition but i would like to know how this will be solved for this kind of requirements.. because this is the interview question which i have faced recently in one of the mnc company in top 10 companies.

Thanks in advance.

Regards,
Nagarjuna

remyo
Starting Member

6 Posts

Posted - 2014-03-11 : 11:01:52

Maybe the correct answer is: You can do it but best is if you dont.
You could create a extra field S + the primarykey.
1 reason is that all your relationships in the DB are still viable
2nd reason is that joining on integers is faster the joining on non integers

IF it is absulutly necessary

Check all the FKK's and note them
Check the datamodel if FKK have not been defined (ie relationsships to the current PK)

Drop the FK constraints
Create new table with varchar PK
COPY Data in new table including the S -> Or use select into
Change all fields found with FK constraints
Change all other fields
Recreate FK constraints

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-11 : 11:10:36

if you change the datatype from INT to VARCHAR you will able to accomodate that change. It's because 123 is an integer but when you append 'S' to it its no longer an integer but a STRING. So you have to change the data type to VARCHAR. Also, after you change the datatype, you need to write the update statement as below.


UPDATE tabelA
SET SID = 'S'+CONVERT(VARCHAR(10), SID)

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

nagarjuna_a2006
Starting Member

16 Posts

Posted - 2014-03-11 : 12:07:47

Thank you for giving reply..

thanks a lot..

Regards,
Nagarjuna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources