March 21, 2011 at 8:14 pm
Interesting question, thanks, and a good reason you should always specify the data type for hardcoded items when using a Table Value Constructor or UNION since data type precedence comes into play.
Here is a link to the Table Value Constructor in BOL that mentions the data type precedence: http://msdn.microsoft.com/en-us/library/dd776382.aspx, and here is a link for data type precedence: http://msdn.microsoft.com/en-us/library/ms190309.aspx
Edited to correct the information. (thanks Bradley!)
March 22, 2011 at 12:01 am
No. My answer is first time BEGH, second time BEGH and Third time BEGH. Bcoz these two variables are varchar only. so if u pass the interger value without single quote it will accept.
First time :
insert into #test values
('1','A'),
('','B')
insert into #test values
(2,'D'),
('','E')
insert into #test values
('','G'),
('','H')
go
-- First select statement
select * from #test where c1 = ' '
delete #test
answer :
B
E
G
H
Second time:
insert into #test values
('1','A'),
('','B')
insert into #test values
('2','D'),
('','E')
insert into #test values
('','G'),
('','H')
go
-- Second select statement
select * from #test where c1 = ' '
delete #test
Third Time:
insert into #test values
(1,'A'),
('','B')
insert into #test values
(2,'D'),
('','E')
insert into #test values
('','G'),
('','H')
go
-- Third select statement
select * from #test where c1 = ' '
answer :
B
E
G
H
Note: So all three times the value comes same value only in SQLSERVER 2005
March 22, 2011 at 12:15 am
The question is " What is the output of the select statement from the below code
executed in sql server 10.0.1600 and with default settings " and not in SQL Server 2005 🙂
And are you sure this below code executed successfully in 2005 ?
insert into #test values
('1','A'),
('','B')
Thanks
March 22, 2011 at 2:52 am
Gopinath Srirangan (3/22/2011)
The question is " What is the output of the select statement from the below codeexecuted in sql server 10.0.1600 and with default settings " and not in SQL Server 2005 🙂
And are you sure this below code executed successfully in 2005 ?
insert into #test values
('1','A'),
('','B')
Thanks
The Insert statement would definitely not work in 2005. The feature of inserting multiple rows using VALUE caluse is introduced in 2008.
March 22, 2011 at 2:53 am
Interesting question.
Good learning for me.
Thanks.
March 22, 2011 at 2:56 am
tejaswini.patil (3/22/2011)
Gopinath Srirangan (3/22/2011)
The question is " What is the output of the select statement from the below codeexecuted in sql server 10.0.1600 and with default settings " and not in SQL Server 2005 🙂
And are you sure this below code executed successfully in 2005 ?
insert into #test values
('1','A'),
('','B')
Thanks
The Insert statement would definitely not work in 2005. The feature of inserting multiple rows using VALUE caluse is introduced in 2008.
If the INSERT statement syntax is changed according to SQL Server 2005 and executed the script in SQL Server 2005 then the output is B, E, G, H for all the three select statements.
March 22, 2011 at 3:00 am
Too much thinking for one point...
March 22, 2011 at 3:04 am
paul s-306273 (3/22/2011)
Too much thinking for one point...
"Point" does not matter however question matters.
I have found the question interesting and am sure there will be good learning for me.
🙂
March 22, 2011 at 3:21 am
Gopinath Srirangan (3/22/2011)
And are you sure this below code executed successfully in 2005 ?insert into #test values
('1','A'),
('','B')
Thanks
It will not work in 2005. That's the server I use daily and I wish this syntax would work because it could simplify my job significantly when inserting a bunch of values manually.
March 22, 2011 at 3:45 am
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
March 22, 2011 at 3:48 am
sorry ,i'm not getting .
i got first answer in sql server2008
March 22, 2011 at 5:16 am
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.
March 22, 2011 at 5:36 am
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:
c1c2
1A
B
go
select 1 as c1,'A' as c2
union
select '','B'
Result2:
c1c2
1A
0B
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
March 22, 2011 at 5:52 am
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
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply