Incorrect syntax!!! - 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

SQL Server Development (2000)

Incorrect syntax!!!

Author

Topic

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-05-19 : 09:44:12

Hi,

I am trying to insert some data into a table variable. The reason for doing so is, i want a identity field in this table variable which should start with a max ID from another table.

So here is what iam doing, and i get the error

'Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@MAX_ID'.'


DECLARE @MAX_ID INT

SET @MAX_ID = (SELECT max(ISNULL(unique_id, 0)) from table1)

'PRINT @MAX_ID 

[note : i do this print statement its giving me correct data]

[i think this is where the error is pointed to]
declare @t table (temp_id int IDENTITY (@MAX_ID, 1), field1 varchar(50))
insert into @t
select distinct a.name_var from table2 a
left join table1 b on a.field1 = b.field1 and a.field2 = b.field2 and a.field3 = b.field3 and a.field4 = b.field4 and a.field5 = b.field5
where b.field1 is null

select * from @t

[once @t is constructed properly with the unique_id, i will then insert the records into table1.]

can somebody tell me what is it that iam doing wrong here.

Thanks a lot in advance,
Nitu

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-19 : 09:47:44

Get rid of the quote before PRINT

declare @t table (temp_id int IDENTITY 1, 1), field1 varchar(50))
can't use a variable for the identity.
Why do you want to anyway - just add @max_id to it when it is used.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-05-19 : 10:01:52

Thank you for your reply.

Even after taking the print statement out, iam getting this error. as you say

quote:

can't use a variable for the identity.

see like thats why this error is occuring.

coming to why i want to do this, is because i want the initial value of the identity field to start with the max value and not with 1.

The whole idea behind this is, i am comparing two tables, table1 and table2. If data is found in table2 and not there in table1 which means these records are new to table1, i want to insert these new records from table2 to table1. there is a field in table1 which should have incremental values (act like an identity, but its not declared as one, since in some bizzare situations this can be a null value). for this field i have to insert data as asn incremental of 1 starting from the max value. So thats why i am creating this table variable with a identity field and i want the initial value of this identity field to be a value which i give it.

So how can i do this.

Thanks a lot for your help,
Nitu

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-19 : 10:10:14

Read further.

just add @max_id to it when it is used.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-05-19 : 10:24:31

I am sorry, i am not able to understand how exactly to do this.

quote:

just add @max_id to it when it is used.

can you please tell me where exactly should i add @max_id to temp_id in the following code:




declare @t table (temp_id int IDENTITY (@MAX_ID, 1), field1 varchar(50))
insert into @t
select distinct a.name_var from table2 a
left join table1 b on a.field1 = b.field1 and a.field2 = b.field2 and a.field3 = b.field3 and a.field4 = b.field4 and a.field5 = b.field5
where b.field1 is null

select * from @t

i am not able to figure this out because, i am simply inserting only one field in to @t, and expecting temp_id to do its thing.

So can you please give me example code.

thanks a lot for your help,
really appretiate it.

--Nitu

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-19 : 10:26:17

declare @t table (temp_id int IDENTITY (0, 1), field1 varchar(50))
insert into @t
select distinct a.name_var from table2 a
left join table1 b on a.field1 = b.field1 and a.field2 = b.field2 and a.field3 = b.field3 and a.field4 = b.field4 and a.field5 = b.field5
where b.field1 is null

select temp_id + @max_id, field1 from @t

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-05-19 : 10:32:20

Thanks a lot.

I feel silly i dint think that way. I am under pressure to get this done by end of today, may be thats why

Thanks a lot again.

--Nitu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources