May 28, 2018 at 10:00 am
Hi,
is there any Chance to get this working?
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[test] [nchar](10) NULL,
)
Now insert something with a dummy NULL placeholder:
insert into [Spielwiese].[dbo].[Table_1]
(id, test)
values
(null, 'test')
Sounds strange, I agree. The reason why I'm asking is, this Statement is generated by Attunity Replicate. The ID-column only exists in the target table, so I want to assign record ID when inserting (to be used in Triggers)
May 28, 2018 at 10:37 am
rotcha99 - Monday, May 28, 2018 10:00 AMHi,
is there any Chance to get this working?
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[test] [nchar](10) NULL,
)Now insert something with a dummy NULL placeholder:
insert into [Spielwiese].[dbo].[Table_1]
(id, test)
values
(null, 'test')
Sounds strange, I agree. The reason why I'm asking is, this Statement is generated by Attunity Replicate. The ID-column only exists in the target table, so I want to assign record ID when inserting (to be used in Triggers)
Not possible. You cannot put a NULL into an IDENTITY column (even after executing SET IDENTITY_INSERT dbo.Table_1 ON). But you could insert some other value (-1, perhaps)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 28, 2018 at 12:09 pm
Unfortunately, it won't accept any values. turning off identify_insert will overwrite the value assigned by the server. Perhaps I could do something inside a 'before insert' trigger.
May 28, 2018 at 12:31 pm
rotcha99 - Monday, May 28, 2018 12:09 PMUnfortunately, it won't accept any values. turning off identify_insert will overwrite the value assigned by the server. Perhaps I could do something inside a 'before insert' trigger.
Not sure what you mean. The following works for me:
CREATE TABLE dbo.Table_1
(
id INT IDENTITY(1, 1) NOT NULL,
test NCHAR(10) NULL,
);
SET IDENTITY_INSERT dbo.Table_1 ON;
INSERT dbo.Table_1
(
id,
test
)
VALUES
(-1, 'test');
SET IDENTITY_INSERT dbo.Table_1 OFF;
SELECT * FROM dbo.Table_1 t
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 28, 2018 at 1:03 pm
Well, this will always insert the value -1, instead of a random/autoinc that makes a row (technically) unique.
Looks like I'll have to use the business keys (as infered by the Primary Key) - actually I wanted to avoid this pattern, since "custom" rules can be hard to generate.
It's the same problem that blocks direct replication into a temporal table. Attunity provides NULL for any field that is not listed in the mapping. And SQL-Server won't "eat" these for the datetime pairs and period attribute.
May 28, 2018 at 2:32 pm
rotcha99 - Monday, May 28, 2018 1:03 PMWell, this will always insert the value -1, instead of a random/autoinc that makes a row (technically) unique.
Looks like I'll have to use the business keys (as infered by the Primary Key) - actually I wanted to avoid this pattern, since "custom" rules can be hard to generate.It's the same problem that blocks direct replication into a temporal table. Attunity provides NULL for any field that is not listed in the mapping. And SQL-Server won't "eat" these for the datetime pairs and period attribute.
Your first post said this:
Now insert something with a dummy NULL placeholder:
In particular, it said nothing about uniqueness or randomness.
Please take the time to post fuller details in any future posts, to avoid wasting other people's time.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 28, 2018 at 2:39 pm
Sorry, I was referring to that INSERT INTO-Statement. As the author, the DDL was clear to me. Sometimes comments would help. I am researching patterns (PoC) to build an ODS that would provide history where needed.
May 29, 2018 at 4:12 am
Simply insert the value without the idinsert into [Spielwiese].[dbo].[Table_1] (test)
values ('test');
May 29, 2018 at 10:43 am
And as a follow up to Des Norton's post, you could also consider a sequence. You might not be able to guarantee no gaps, but it would certainly be functional to do a FETCH NEXT VALUE FROM MySequence into a variable and then use the resulting value.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 31, 2018 at 12:57 pm
rotcha99 - Monday, May 28, 2018 10:00 AMHi,
is there any Chance to get this working?
CREATE TABLE [dbo].[Table_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[test] [nchar](10) NULL,
)Now insert something with a dummy NULL placeholder:
insert into [Spielwiese].[dbo].[Table_1]
(id, test)
values
(null, 'test')
Sounds strange, I agree. The reason why I'm asking is, this Statement is generated by Attunity Replicate. The ID-column only exists in the target table, so I want to assign record ID when inserting (to be used in Triggers)
I think your problem is you don't understand there is no such thing as an "identity column"; this is a proprietary count of a table property related to the storage of physical storage. It counts the attempts (not even the successes) of records (not even rows) which the original Sybase SQL Server inherited from the old UNIX/minicomputer days.
Think about it for a minute. Go back to the basics. Every table in your schema (automobiles, squids, etc.) can have an identity column because the table property is a matter of the implementation that Sybase originally made. Why do you think it is an attribute of those automobiles or squids or whatever?
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply