Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

T-SQL Expand / Collapse
Author
Message
Posted Tuesday, March 22, 2011 3:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:43 AM
Points: 47, Visits: 57
And are you sure this below code executed successfully in 2005 ?

This is not a correct syntax in sqlserver2005 which is below given query
insert into #test values
('1','A'),
('','B')



correct answer:

insert into #test values('1','A')

insert into #test values ('','B')

this is the way to insert the values to the particular table in sqlserver2005
Post #1081836
Posted Tuesday, March 22, 2011 3:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 8, 2013 1:57 AM
Points: 47, Visits: 49
sorry ,i'm not getting .
i got first answer in sql server2008
Post #1081840
Posted Tuesday, March 22, 2011 5:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 1:59 AM
Points: 1,042, Visits: 234
In SQL Server 2008 the INSERT statement has been enhanced which enables to insert multiple records using the VALUE clause in a single INSERT statement.

insert into #test values
(2,'D'),
('','E')
Above INSERT statement will insert two records.

In first set of values, 2 is not embedded in single quotes and that is why the data type is considered as "int". SQL Server considers that data type for the first value of all records being inserted in that INSERT statement.
First set of values inserts a record as 2, D
While inserting second record, the first value '' is implicitly converted as 0 so second set of values inserts a record as 0, E.


This question gave us a learning that we have to be careful about implicit conversion that SQL Server does.
Post #1081894
Posted Tuesday, March 22, 2011 5:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 157, Visits: 292
Hi pksutha,

Let me explain little more..


insert into #test values ('1','A'), ('','B')

is not same as below.

insert into #test values('1','A')
insert into #test values ('','B')

The former insert statement in introduced in 2008 and the later is the usual code that we use often.

To be compatible in both, pls run the below code either in 2005 or 2008 and compare the results.

select '1' as c1,'A' as c2
union
select '','B'

Result1:
c1 c2
1 A
B

go

select 1 as c1,'A' as c2
union
select '','B'

Result2:
c1 c2
1 A
0 B


The important thing that i want to bring out is the datatype of the final result set is based on the first statement in the insert list.
From the above example ,
first result set has c1 [varchar(1)] , C2 [varchar(1)]
second result set has c1 [int, C2 [varchar(1)]

Hope this is helpful ?

Thanks
Gopi
Post #1081903
Posted Tuesday, March 22, 2011 5:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:43 AM
Points: 47, Visits: 57
Hi Gopi,


i was explaing about insertion syntax,

not about selection process. In both 2005 and 2008 will display ans for selection command for insertion command.

bcoz sqlseerver 2005 doesn't support this syntax like insert into #test values('1','A'),(' ','B')

With Regards
Sutha
Post #1081911
Posted Tuesday, March 22, 2011 5:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:43 AM
Points: 47, Visits: 57
For datatype , this doubt clarification i've posted one answered already.
varchar accepts both number and character and without single quote number also like (1,'b') or ('1','b')
both possible in sqlserver2005
Post #1081914
Posted Tuesday, March 22, 2011 6:06 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:30 AM
Points: 4,121, Visits: 5,488
Good question, thanks.
This serves as a gentle reminder of the dangers of relying on implicit conversion.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1081920
Posted Tuesday, March 22, 2011 6:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:43 AM
Points: 47, Visits: 57
ya i'm Agreeing this.

First of all insert into #test values(2,'A'),(' ' ,'B') this is not there in sqlserver 2005.

So now we can't consider data type issue.

IF u go with sqlserver2008 ,yes of course we need to consider about data types.

Thank you
Post #1081925
Posted Tuesday, March 22, 2011 6:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 157, Visits: 292
Hi Sutha,
Still you have same issue in SQL Server 2005 using old method.
Try this and compare the results.

select * into #D1 from (
select '1' as c1,'A' as c2
union
select '','B'
) T

select * from #D1
go

select * into #D2 from (
select 1 as c1,'A' as c2
union
select '','B'
) T
select * from #D2

drop table #D1
drop table #D2

Implicit conversion matters both in 2005 and 2008 when using union in the select before inserting the records.
Post #1081929
Posted Tuesday, March 22, 2011 7:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 10:23 AM
Points: 2,630, Visits: 4,041
That new syntax in 2008 is handy. I'll have to try it out. Nice to learn something new. Thanks.
Post #1081963
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse