Using new Identity in new insert (other table)
-
Hi!
Hopefully, someone will have a quick answer for me - my brain is currently on SLOW....
Basically, I INSERT a new row into table "UserList", then I use the new ID (generated Identity field) in another INSERT into the table "UserID".
A rough version of the SQL for each is (Note that I just put the variable names into the VALUES list for illustration only):
SQL1: "INSERT INTO [UserList] ([Name], [Id]) VALUES (c_UserName, c_UserId) "
SQL2: "SELECT IDENT_CURRENT('[UserList]')"
lIdentity = ID from latest INSERT
SQL3: "INSERT INTO [UserID] ([User_Id],[MasterOverride], [ConfigurationId]) VALUES (lIdentity, c_MasterOverride , c_ConfigurationId 
"This works as is, but I would like to eliminate the need for a seperate step for SQL2 and the creation of lIdentity. In other words, I would like to put the select for the identity into SQL3.
Any help?
Thanks,
Allan
-
In BOL, check out SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY. One of them will suit your needs.
Once you understand the BITs, all the pieces come together
-
Thomas: Thanks, I have checked and IDENT_CURRENT works for this situation.
I would like to embed SQL2 into SQL3 and get the same (correct) results.
Thanks again,
Allan
-
I'm sorry, but I can do not completely understand your post. Are you executing SQL1, SQL2, SQL3 all in same script, one after the other?
Whay can't SQL3 be:
INSERT INTO [UserID] ([User_Id],[MasterOverride], [ConfigurationId])
VALUES (IDENT_CURRENT('[UserList]'), c_MasterOverride , c_ConfigurationId
Once you understand the BITs, all the pieces come together -
I'd be inclined to do this with a trigger, normally. Any reason not to do that (like this only applies in a special case, or some such)?
R David Francis -
Will this little snipet help?:
Create Table Test1 (ID int IDENTITY (1, 1), Data1 varchar(10))
Create Table Test2 (ID2 int, Data2 varchar(10))
Insert Into Test1 (Data1) Select 'Data for 1'
Insert into Test2 (ID2, Data2) Select IDENT_CURRENT('[Test1]'), 'Data for 2'
Select * from Test1 Join Test2 On ID = ID2
Drop table Test1 Drop Table Test2 -- cleanup
Once you understand the BITs, all the pieces come together -
Firstly: Thomas - Thanks, that's essentially what I'm looking for (Like I said, my brain is on SLOW - and I have sixteen other problems to solve in the next 35 minutes).
Secondly: R. David - There is an underlying reason to do this without triggers. The client has built a 'parallel' access method to SQL Server, which is faster - needed for real-time robotics. Triggers and Stored Procedures are a NO-NO in this system for data update or deletion. [It interferes with the alternate server code]. (You can use Stored Procedures or Views to look at data - read only). In order to access this from my Visual Basic pieces of the puzzle, I need to send messages to the alternate server via WinSockets - and I need to wait for a reply. The SQL statements that I showed above are being executed, but they need to be packaged in one lump, so that I can send the whole thing to the server handler (which can deal with multiple SQL statements). My initial implementation had me reading the ID after doing the first insert, then proceeding with the remainder of the inserts. This works fine on my laptop, but when I go over the wire, I sometimes need to wait for the results of the first insert before continuing - and I can't always guarantee how long a wait - and due to some VB encapsulation interferance, I don't always get the response raised.
Thanks,
Allan
-
Allen, maybe instead of "VB Executing" your INSERT, call the "insert" in the form of a "record set" query returning your identity to be used as a constant in subsequent VB to SQL calls...
SET NOCOUNT OFF -- keeps VB happy
Insert Into Test1 (Data1) Select 'Data for 1'
Select IDENT_CURRENT('[Test1]') as ReturnVal
Once you understand the BITs, all the pieces come together -
Thomas:
Due to design restrictions (as I mentioned in my previous posting here), I cannot do an "EXEC SQL" OR a Recordset Update or Delete or Insert.
Basically, I am allowed to get data via SQL (and ADO) in my programs, but I must send a message via WinSocket to the Server Handler, which will perform the actual Update, Insert, or Delete.
(There is an additional reason for the restrictions - the Backup method calls for a proprietary duplication of every change to all of the tables - use of Stored Procedures and running updates, etc., via SQL or ADO recordsets bypass the duplications).
Note, that while I have been testing the system, I have been allowed to use ADO and SQL to update, etc. (Production is another story).
Thanks for your help,
Allan
-
So there is not way of getting a "return value" of the IDENTITY from the Winsock call you have to make. Right?
When you make the Winsock call, and it procedes to perfom the INSERT .... does the T-SQL code performing this work "know" who the calling VB session/TransactionID is? If so, then a trigger on the INSERT of [Test1] table, can either INSERT a record into another table having IDENTITY, VB TRansactionID columns, or add another column to [Test1] table to contain your VB session TransactionID. Then your VB can query to find "its" newly inserted record(s). Other ideas I have fall apart if there are multiple VB sessions doing these transactions at the same time. At any rate, if the Winsock call can not return to VB the IDENTITY, then your VB code will have to periodically "poll" SQL since you state that VB may never get a "return" from the Winsock anyway.
Do I understand your situation ok?
Once you understand the BITs, all the pieces come together
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply