July 30, 2014 at 5:03 pm
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.
July 30, 2014 at 5:08 pm
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
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 30, 2014 at 5:57 pm
Just don't forget to turn IDENTITY_INSERT off for the table after you finish your load. 😉
July 30, 2014 at 6:04 pm
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?
July 30, 2014 at 6:19 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 30, 2014 at 6:30 pm
Because the documentation on IDENTITY INSERT states that you have to explicitly pass the value to insert into PK field.
Not true?
July 30, 2014 at 6:32 pm
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).
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 2, 2014 at 6:01 am
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;
August 4, 2014 at 9:15 am
Thanks to both of you for the assistance.
August 4, 2014 at 10:01 am
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?
August 4, 2014 at 10:36 am
IDENTITY_INSERT can only be 'ON' for a single table at a time, and there is no easy visibility to which table currently has this option turned 'ON'. Therefore, it's important to turn it off after use.
August 4, 2014 at 10:38 am
Ahh yes...I'd forgotten about that small thing 🙂
Understood. Thanks
August 4, 2014 at 1:02 pm
The SET statements are session specific (unless someone corrects me). That means that if you set identity_insert on, you can only use that option in that session and other sessions will remain unaffected.
When using identity_insert, you need to specify your identity column on every insert. Here's a test that you can make commenting and uncommenting the code.
CREATE TABLE Customers(
customer int identity primary key,
name varchar(50))
SET IDENTITY_INSERT Customers ON
GO
INSERT INTO Customers( customer, name) VALUES (3, 'Test 3')
GO
--SET IDENTITY_INSERT Customers OFF
--GO
INSERT INTO Customers( customer, name) VALUES (5, 'Test 5')
GO
INSERT INTO Customers( name) VALUES ('Test with column list on insert')
GO
INSERT INTO Customers VALUES ('Test without column list')
GO
SET IDENTITY_INSERT Customers OFF
GO
SELECT * FROM Customers
DROP TABLE Customers
August 5, 2014 at 11:01 am
I agree. That's what I'm actually finding to be true. There doesn't actually seem to be a need to turn it off; once the script runs I run this on the next table with no errors.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply