July 27, 2017 at 12:56 am
Hi,
I am trying to do a identity insert on table [CnP].[WorkComment_test.
DDL
CREATE TABLE [CnP].[WorkComment_test](
[WorkCommentId1] [int] NOT NULL,
[WorkId] [int] NOT NULL,
[Comments] [nvarchar](max) NOT NULL,
[IsExternalViewable] [bit] NOT NULL,
[IsAlert] [bit] NOT NULL,
[CreatedBy] [nvarchar](256) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedBy] [nvarchar](256) NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[IsDeleted] [bit] NOT NULL,
[RegistrationId] [int] NULL,
CONSTRAINT [PK_WorkComment1] PRIMARY KEY CLUSTERED
(
[WorkCommentId1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
is identity & identity increment is not turn on this table. So I trying to get the current highest value and +1 to it for the next row.
I have tried using the following while loop but the first column @newid is just the same number for each row instead of increment by 1.
DECLARE @startInt INT
DECLARE @ENDInt INT
DECLARE @NEWID INT
SET @startInt = 1
SET @ENDINT = (SELECT DISTINCT COUNT(WINFkey) from apradb.dbo.winf w WHERE last_paid in ('L17','L18','L19','L20','L21','L22','L23','L24','L25','L26','L27','L28','L29','L30','L31','L32'
,'L33','L34','L35','L35B','L36A','L36B','L36C','L37A'))
WHILE (@startInt <= @ENDInt)
BEGIN
SET @NewID = (SELECT MAX([WorkCommentId]) FROM [schemap].[WorkComment])
--INSERT INTO [schmeap].[WorkComment_test] ([WorkCommentId1],[WorkId],[Comments],[IsExternalViewable],[IsAlert],[CreatedBy],[CreatedDate],[UpdatedBy],[UpdatedDate],[IsDeleted],[RegistrationId])
SELECT @newID
,SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY))
,'Legacy Migration Information'
,1
,1
,'domain\testuser'
,GETDATE()
,SYSTEM_USER
,GETDATE()
,0
,0
FROM db.schemaI.winf w
WHERE last_paid in
(
'L17'
,'L18'
,'L19'
,'L20'
,'L21'
,'L22'
,'L23'
,'L24'
,'L25'
,'L26'
,'L27'
,'L28'
,'L29'
,'L30'
,'L31'
,'L32'
,'L33'
,'L34'
,'L35'
,'L35B'
,'L36A'
,'L36B'
,'L36C'
,'L37A'
)
SET @NEWID = @NewID +1
Continue;
END
Result
16880979 01118589 Legacy Migration Information
16880979 01118822 Legacy Migration Information
Desired
16880979 01118589 Legacy Migration Information
16880980 01118822 Legacy Migration Information
Thanks
July 27, 2017 at 2:08 am
Firstly, an explanation of what what you're doing isn't working. Inside your While loop your returning your full dataset, in this case every row that your going to insert, every time you loop through. This means that each time the process runs @NewID is going to have the same value across every row, then you increment the value by one. Then you return the whole data set (again), and every row has the new value of @NewID.
Using a While Loop or Cursor really isn't the idea here. When using SQL you need to stop thinking with iterative mind set, and think about it in a whole. I apologise that I can't remember who said this (probably Jeff Moden, but whoever it is, please feel free to cite them) but a good saying is "Don't think about what you want to do to a row; think about what you want to do with a column". Your thought process was you need to take the first row and set it the highest value plus one of the current ID. Then go to the nxt row and add one, then the next row and add one. Instead, a dataset solution would be to set the ID to the Row Number in that dataset, and add the highest value from your table to it.
I've made a small assumption, as theres no sample data, but this should get you the answer you want:INSERT INTO [schmeap].[WorkComment_test] ([WorkCommentId1],[WorkId],[Comments],[IsExternalViewable],[IsAlert],[CreatedBy],[CreatedDate],[UpdatedBy],[UpdatedDate],[IsDeleted],[RegistrationId])
SELECT ROW_NUMBER() OVER (ORDER BY SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY)) + (SELECT MAX([WorkCommentId]) FROM [schemap].[WorkComment]) AS ID, --Total guess what the ORDER should be.
--The above uses the ROW_NUMBER function, to give each row its own "ID". I then use your SQL to get the highest ID value from your existing table and add that it it.
--So, the first row would be allocated ROW_NUMBER. If your existing max ID is 132465, then that equates to 132465 + 1 = 132466.
SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY)),
'Legacy Migration Information',
1,
1,
'domain\testuser',
GETDATE(),
SYSTEM_USER,
GETDATE(),
0,
0
FROM db.schemaI.winf w
WHERE last_paid in ( 'L17','L18','L19','L20','L21','L22','L23','L24','L25','L26','L27','L28','L29','L30','L31','L32','L33','L34','L35','L35B','L36A','L36B','L36C','L37A');
If you have any questions, please ask. The important part is you understand what the above SQL is doing, so you can learn from it. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 27, 2017 at 5:59 pm
Thanks for the reply.
Unfortunately your row_Number() returns the same as when I tried to use it, which is why I switched to trying using a while loop. The Row_Number() is only returning the row 1,2,3,4,5, when I need the max(workcommentid) + 1 each time through the loop.
July 27, 2017 at 7:38 pm
I have written a Cursor, but the same issue is happening, columnid is not increasing by 1 each time through the loop. Any suggestions?
DECLARE @ColumnID INT
SET @ColumnID = (SELECT MAX([WorkCommentId]) FROM [abc].[WorkComment]) --CurrentID or Row Number
DECLARE @ID INT --Number of Rows to Process
DECLARE IDCursor CURSOR FOR
SELECT DISTINCT COUNT(WINFkey) from dbdb.dbo.winf w WHERE last_paid in ('L17','L18','L19','L20','L21','L22','L23','L24','L25','L26','L27','L28','L29','L30','L31','L32'
,'L33','L34','L35','L35B','L36A','L36B','L36C','L37A')
OPEN IDCursor
FETCH NEXT FROM IDCursor INTO @ID
WHILE (@@FETCH_STATUS = 0)
BEGIN
--INSERT INTO [PPP].[WorkComment_test] ([WorkCommentId1],[WorkId],[Comments],[IsExternalViewable],[IsAlert],[CreatedBy],[CreatedDate],[UpdatedBy],[UpdatedDate],[IsDeleted],[RegistrationId])
SET @ColumnID = 1 + @ColumnID
SELECT @ColumnID as 'ColumnId'
,SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY))
,'Legacy Migration Information'
,1
,1
,'domain\testuser'
,GETDATE()
,SYSTEM_USER
,GETDATE()
,0
,0
FROM dbdb.dbo.winf w
WHERE last_paid in
(
'L17','L18','L19','L20','L21','L22','L23','L24','L25','L26','L27','L28','L29','L30','L31','L32','L33','L34','L35','L35B','L36A','L36B','L36C','L37A')
FETCH NEXT FROM IDCursor INTO @ID
END
CLOSE IDCursor
DEALLOCATE IDCursor
July 28, 2017 at 1:29 am
ringovski - Thursday, July 27, 2017 5:59 PMThanks for the reply.
Unfortunately your row_Number() returns the same as when I tried to use it, which is why I switched to trying using a while loop. The Row_Number() is only returning the row 1,2,3,4,5, when I need the max(workcommentid) + 1 each time through the loop.
This implies that the problem is your subquery. That's not something i can test, as i can't see your data. Can you supply sample DLM, as we can then test our answers.
P.S. Please don't use a cursor,., They are slow and not required for this operation.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 28, 2017 at 10:39 am
if you can't use IDENTITY, can you use a SEQUENCE? That would allow you to get new values after each run, and you not having to worry about querying MAX(WorkCommentID) value each time. Here's an article that describes their usage:
https://docs.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers
initial setup would be like this (unfortunately you can't just pass in @StartID to the CREATE SEQUENCE statement) :DECLARE @StartID int, @sqlcmd nvarchar(500);
SELECT @StartID = MAX(WorkCommentID) + 1 FROM [schemap].[WorkComment];
SET @sqlcmd = N'CREATE SEQUENCE dbo.WorkCommentSequence AS int START WITH ' + CAST(@StartID AS nvarchar(10)) + N' INCREMENT BY 1;';
EXEC sp_executesql @sqlcmd;
Then in Thom's code, instead of:SELECT ROW_NUMBER() OVER (ORDER BY SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY)) + (SELECT MAX([WorkCommentId]) FROM [schemap].[WorkComment]) AS ID,
you can use:SELECT NEXT VALUE FOR dbo.WorkCommentSequence AS ID,
July 30, 2017 at 4:00 pm
MAX([WorkCommentId]) must be not in SELECT part, but rather in FROM section, as a derived table.
Subqueries within SELECT are executed for each returned row.
INSERT INTO [schmeap].[WorkComment_test] ([WorkCommentId1],[WorkId],[Comments],[IsExternalViewable],[IsAlert],[CreatedBy],[CreatedDate],[UpdatedBy],[UpdatedDate],[IsDeleted],[RegistrationId])
SELECT ROW_NUMBER() OVER (ORDER BY SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY)) + isnull(MaxID, 0), --Total guess what the ORDER should be.
--The above uses the ROW_NUMBER function, to give each row its own "ID". I then use your SQL to get the highest ID value from your existing table and add that it it.
--So, the first row would be allocated ROW_NUMBER. If your existing max ID is 132465, then that equates to 132465 + 1 = 132466.
SUBSTRING(w.WINFkey,3,LEN(w.WINFKEY)),
'Legacy Migration Information',
1,
1,
'domain\testuser',
GETDATE(),
SYSTEM_USER,
GETDATE(),
0,
0
FROM db.schemaI.winf w
Left join (SELECT MAX([WorkCommentId]) MaxID FROM [schemap].[WorkComment]) AS M on WINFkey > ''
WHERE last_paid in ( 'L17','L18','L19','L20','L21','L22','L23','L24','L25','L26','L27','L28','L29','L30','L31','L32','L33','L34','L35','L35B','L36A','L36B','L36C','L37A');
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply