Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

using IDENTITY INSERT Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 5:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:54 PM
Points: 39, Visits: 56
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.
Post #1598033
Posted Wednesday, July 30, 2014 5:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:27 PM
Points: 6,256, Visits: 7,432
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
Post #1598034
Posted Wednesday, July 30, 2014 5:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:58 PM
Points: 3,637, Visits: 7,934
Just don't forget to turn IDENTITY_INSERT off for the table after you finish your load.


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1598040
Posted Wednesday, July 30, 2014 6:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:54 PM
Points: 39, Visits: 56
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?
Post #1598042
Posted Wednesday, July 30, 2014 6:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:27 PM
Points: 6,256, Visits: 7,432
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
Post #1598047
Posted Wednesday, July 30, 2014 6:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:54 PM
Points: 39, Visits: 56
Because the documentation on IDENTITY INSERT states that you have to explicitly pass the value to insert into PK field.

Not true?
Post #1598050
Posted Wednesday, July 30, 2014 6:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:27 PM
Points: 6,256, Visits: 7,432
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
Post #1598052
Posted Saturday, August 2, 2014 6:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 1,955, Visits: 5,087
Just to further on the explanation with a quick example:


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;

Post #1598923
Posted Monday, August 4, 2014 9:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:54 PM
Points: 39, Visits: 56
Thanks to both of you for the assistance.
Post #1599303
Posted Monday, August 4, 2014 10:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:54 PM
Points: 39, Visits: 56
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?
Post #1599318
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse