Problem with IN statement - SQL Server Forums

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

Transact-SQL (2000)

Problem with IN statement

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2006-03-06 : 05:49:33

I am trying to insert the date into temparary table using subquery as follow

insert into #PayError_new
select * from PayError where PayErrorKey in (1,
2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,
21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,
38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,54,55,56,
57,58,59,60,61,63,65,66,67,68,69,70,71,72,73,74,75,
76,77,78,79,80,81,82,83,84,86,87,88,89,91,92,95,96,
97,98,99,100)

but i got zero row(s) affected after execution.

above certain number(69) the values is not inserted into temparary table.

plz tell me that IN statement have any limitation
(means how many data i.e. number i can pass to IN statement)

shubhada

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-03-06 : 06:52:55

Nopes In statement doesnt have any limitation..

Check how many rows are getting selected when you are running only select query..

you can also use Select Into query for the same..

--Something like this ..

select * Into #PayError_new from PayError
where PayErrorKey in (1,
2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,
21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,
38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,54,55,56,
57,58,59,60,61,63,65,66,67,68,69,70,71,72,73,74,75,
76,77,78,79,80,81,82,83,84,86,87,88,89,91,92,95,96,
97,98,99,100)

this will also create the temp table with number of fields which are the in the select statement..

Hope this works for you..

Sucess Comes to those who Believe in Beauty of their Dream..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-06 : 07:03:34

Your query can be modified as

insert into #PayError_new
select * from PayError where PayErrorKey between 1 and 100

Make sure you have data for that range

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-03-07 : 07:21:58

quote:
Originally posted by madhivanan

Your query can be modified as

insert into #PayError_new
select * from PayError where PayErrorKey between 1 and 100

Make sure you have data for that range

Madhivanan

Failing to plan is Planning to fail

insert into #PayError_new
select * from PayError 
where PayErrorKey between 1 and 100
  and payErrorKey not in (62, 64, 85, 90, 93, 94)

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-03-07 : 07:25:54

Btw my guess is that you are actually using dynamic sql for that query, so check the string lenght of the variable you use to store the query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-07 : 07:53:33

Thanks Mirko

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources