SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using IDENTITY INSERT


using IDENTITY INSERT

Author
Message
ipisors
ipisors
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 66
After reading this:
http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server

It looked promising to migrate data from an MS ACCESS table into a similarly-structured SQL table.

However, doesn't the article imply that you'd have to specify the values explicitly?
i.e., it's not as easy as

--STATEMENT TO TOGGLE IDENTITY INSERT
INSERT SQLTABLE (SELECT * FROM ACCESSTABLE)


Is it?

Before I proceed with a vb process that I wrote to do this row-by-row (which handles a lot of other concerns...things that passed muster in MS ACCESS datatypes that will cause overflow in SQL, like certain dates) - I'd like to know if I'm missing an easy way to implement the above.

SUMMARY: I have an MS ACCESS table, they already have primary keys, I need to RETAIN those primary keys, and no, they are not all perfectly sequential; there are gaps.
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20061 Visits: 7660
It's that easy. Just use a column list in the INSERT INTO statement for both sides to directly control who goes in what position.

IE:


CREATE TABLE #tester
(IdCol INT IDENTITY( 1, 1), SomeStuff VARCHAR(5))

SET IDENTITY_INSERT #tester ON

INSERT INTO #tester (IdCol, SomeStuff)
VALUES ( 1, 'abc')

INSERT INTO #tester (IdCol, SomeStuff)
VALUES ( 2, 'dbc')

INSERT INTO #tester (IdCol, SomeStuff)
VALUES ( 3, 'ebc')

SELECT * FROM #tester




- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41030 Visits: 19815
Just don't forget to turn IDENTITY_INSERT off for the table after you finish your load. ;-)


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
ipisors
ipisors
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 66
Craig, that looks like the same solution/examples I already had.

So you're passing values explicitly in the sql insert. I know how to do that.

I have 14,000 values in my MS ACCESS table. Am I to execute that code 14,000 times, changing the explcit values each time?
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20061 Visits: 7660
ipisors (7/30/2014)
Craig, that looks like the same solution/examples I already had.

So you're passing values explicitly in the sql insert. I know how to do that.

I have 14,000 values in my MS ACCESS table. Am I to execute that code 14,000 times, changing the explcit values each time?


Shouldn't have to. It's just an example to show mechanism.

IDENTITY_INSERT ON

INSERT INTO (IDCol, ColA, ColB)
SELECT
IDCol, ColA, ColB
FROM
AccessTbl

Should work just fine. How are you SELECT *'ing your Access table currently from SQL? That's the hardest part.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
ipisors
ipisors
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 66
Because the documentation on IDENTITY INSERT states that you have to explicitly pass the value to insert into PK field.

Not true?
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20061 Visits: 7660
ipisors (7/30/2014)
Because the documentation on IDENTITY INSERT states that you have to explicitly pass the value to insert into PK field.

Not true?

Not true from the way you're reading it. REALLY easy to do when BOL decides to be vague. The explicitly passed means you must declare the ID column in the column list and not assume it to be a member (INSERT INTO tbl SELECT blah vs. INSERT INTO tbl (blah) SELECT blah).


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39671 Visits: 19441
Just to further on the explanation with a quick example:
Cool

USE tempdb;
GO

CREATE TABLE dbo.MY_IDENTIY
(
MY_IDENTITY_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_MY_IDENTIY_MY_IDENTITY_ID PRIMARY KEY CLUSTERED
,MY_IDENTITY_VALUE INT NOT NULL
);

SET IDENTITY_INSERT dbo.MY_IDENTIY ON;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(CONVERT(BIGINT,1000)) CONVERT(INT,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS N FROM T T1, T T2, T T3)

INSERT INTO dbo.MY_IDENTIY (MY_IDENTITY_ID,MY_IDENTITY_VALUE)
SELECT
NM.N
,NM.N + 10000
FROM NUMS NM

SET IDENTITY_INSERT dbo.MY_IDENTIY OFF;

SELECT * FROM dbo.MY_IDENTIY;

DROP TABLE dbo.MY_IDENTIY;


ipisors
ipisors
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 66
Thanks to both of you for the assistance.
ipisors
ipisors
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 66
Luis Cazares:
I was thinking more about your post and realized I was unsure whether I had for sure turned back off Identity Insert for each table I recently ported to sql. However, I reviewed the records that have recently been added (since I did the migration in the last couple days) via users in the MS ACCESS front end application. the ID still seems to be incrementing correctly, with seed re-set as expected.

So is this statement (my understanding) correct?:

Failure to turn IDENTITY INSERT off has the following consequence...it would allow someone to execute a sql statement that specifies a value for the identity column. But that's it. It does not mean the incrementing will be turned off, nor that the seed will be anything other than expected.

Basically I'm trying to make sure I understand the full impact of , if I forget to turn it back off. It's basically just leaving it 'open' for explicit value insert, which could, if it occurred, cause what problem - it still wouldn't allow duplicate, would it?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search