Insert Into Selected Values..

So you want to insert @i rows into the rt_planTMP table? That should be done with

SET @sql = 'INSERT INTO rt_planTMP (departman,planlanangun) SELECT TOP ' + CONVERT(VARCHAR, @i)

+ '1, GUNLER' +

' from p_calismagunleri WHERE gunler >= GETDATE()'

The rest of the code stays unchanged.

Though, since a table is an unsorted set of rows by default, using TOP without ORDER BY can give an unexpected result. I also don't quite understand the need of the count first and the top in the second query.

Thans for reply but now gives Line 1: "Incorrect syntax near ','." this error.

I forgot to add a space before the 1:

SET @sql = 'INSERT INTO rt_planTMP (departman,planlanangun) SELECT TOP ' + CONVERT(VARCHAR, @i)

+ ' 1, GUNLER' +

' from p_calismagunleri WHERE gunler >= GETDATE()'

If you post a question next time and follow the instructions in the link below, answers to your questions will mostly lead in more response and better tested code.

Now working thank you.

This won't work, it will fail on the INSERT if @i is greater than 1.

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Also, you don't need dynamic sql:

INSERT rt_planTMP (departman,planlanangun)

SELECT TOP(@i) 1, GUNLER

FROM p_calismagunleri WHERE gunler >= GETDATE()

Yes this true but only if you need to use this code on SQL2005 or above i need to use this code SQL2005 and SQL2000 both

in this case need to use dynamic query.

After all, the code worked. But if you're going to post more questions, it will be very appreciated if you post DDL as in the link below, on which server(s) you need to execute the code and other possibly important information.

I think, in this case, it would have been best if you posted this question in the SQL Server 2000 group.

It still doesn't look quite right either:

DROP TABLE #rt_planTMP

DROP TABLE #p_calismagunleri

CREATE TABLE #rt_planTMP (departman INT, planlanangun DATETIME)

CREATE TABLE #p_calismagunleri (gunler DATETIME)

INSERT INTO #p_calismagunleri (gunler) SELECT GETDATE() - 2

INSERT INTO #p_calismagunleri (gunler) SELECT GETDATE() - 2

INSERT INTO #p_calismagunleri (gunler) SELECT GETDATE() + 1

INSERT INTO #p_calismagunleri (gunler) SELECT GETDATE() + 2

INSERT INTO #p_calismagunleri (gunler) SELECT GETDATE() + 6

INSERT INTO #p_calismagunleri (gunler) SELECT GETDATE() + 7

INSERT INTO #p_calismagunleri (gunler) SELECT GETDATE() + 8

INSERT INTO #p_calismagunleri (gunler) SELECT GETDATE() + 9

INSERT INTO #p_calismagunleri (gunler) SELECT GETDATE() + 10

INSERT INTO #p_calismagunleri (gunler) SELECT GETDATE() + 11

DECLARE @i INT

SELECT @i = COUNT(GUNLER)

FROM #p_calismagunleri

WHERE gunler BETWEEN CONVERT(DATETIME, '01/04/2008') AND CONVERT(DATETIME, '12/04/2010')

SELECT @i

SET ROWCOUNT @i -- 2

INSERT #rt_planTMP (departman, planlanangun)

SELECT 1, GUNLER

FROM #p_calismagunleri

WHERE gunler >= GETDATE() -- 8 matching rows, 2 taken at random (14th and 15th April)

SET ROWCOUNT 0

SELECT * FROM #rt_planTMP

Another thing, convert dates into datetime like 04/01/2008 could lead into even more unexpected results. All in all, I would use this approach:

INSERT #rt_planTMP (departman, planlanangun)

SELECT 1, gunler

FROM #p_calismagunleri

WHERE gunler BETWEEN CONVERT(DATETIME, '04/01/2008',101) AND CONVERT(DATETIME, '04/12/2010',101)

It did here in the UK 😉

Newbies in any programming language often (usually?) write code by trial and error rather than by design and deliberation. When the code works, that's it - whether it's accidental, within the confines of the dev environment, or not.

I reckon if the OP were to write a summary of what the code is supposed to do, and why, it might well look completely different. Oh, and work in prod, too!