Incrementing numbers in an insert statement

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)

Incrementing numbers in an insert statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-04-10 : 09:35:12

Aaron writes "So

I am trying to run this query, right:
--step1
drop table bcltbl
create table bcltbl (sfn varchar(255) null, ordno varchar(8),pickno varchar (8) )
go
insert into bcltbl (sfn)
EXEC master..xp_cmdshell 'dir "S:\Ship Ticket\*.*" /oD /b'
go
update bcltbl set ordno=left(sfn,7)
go
update bcltbl set pickno=substring(sfn,9,7)
go
select * from bcltbl

--step2
INSERT INTO document_link
(document_link_uid, source_area_cd, key1_cd, key1_value, key2_cd, key2_value, link_name, link_path)
SELECT MAX(document_link.document_link_uid) + 1 AS Expr1, '1319' AS Expr2, 'order_no' AS Expr3, bcltbl.ordno, 'pick_ticket_no' AS Expr4, bcltbl.pickno,
bcltbl.sfn, CAST('S:\Ship Ticket\' + bcltbl.sfn + '.pdf' AS varchar(34)) AS Expr5
FROM document_link INNER JOIN
bcltbl ON document_link.key2_value <> bcltbl.pickno
GROUP BY bcltbl.ordno, bcltbl.pickno, bcltbl.sfn

:
there
Step one is to pull the filenames from a directory in order of date.
Step 2 is to insert those values into a table.
The thing of it is... I can't get
MAX(document_link.document_link_uid) + 1
to... keep incrementing.
The inner join on the <> prevents objects that exist in both tables from being inserted.

And it would work great if I could just get that to increment.

Stay Cool,
Aaron Park"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-10 : 10:03:59

Your incrementing logic fails because your insert statement runs once to insert all the data, so MAX(DOCUMENT_LINK.DOCUMENT_LINK_UID) + 1 is only calculated once.

May I ask why you haven't just made DOCUMENT_LINK_UID an identity column?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources