April 1, 2019 at 10:58 am
I have an export table from another SQL-server database.
This table contains an identity. I want a new identity and using the OUTPUT clause of the INSERT statement, I want to create a temp table, which holds both the OLD identity and the NEW identity. This was possible with the MERGE statement.
How can I achieve this with the INSERT statement.
Ben
April 1, 2019 at 11:20 am
Is this not as simple as:
INSERT INTO {YourTable} ({List,of,columns,goes,here})
OUTPUT inserted.{OldIdColumn},
inserted.{NewIDColumn}
VALUES ({Values,to,insert,go,here});
/* --Or, if a SELECT statment
SELECT {List,of,columns,goes,here}
FROM YourOtherTable YOT
--JOIN...?
WHERE...;
*/Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 1, 2019 at 12:03 pm
/*
Thanks for your anwser.
What I want is the 'OLD' and the NEW identity.
The OLD identity is the identity in a #tempA file.
Because a new Identity is inserted, the old identity get's lost.
*/
-- Example
-- Ben
-- 20190401
--
-- drop table A; drop table #tempA ; drop table resolveA
--------------------------
-- Set up a table A
--------------------------
Create table A (id int identity(1000,1), texts varchar(200));
Create table resolveA (old_ID int, new_ID int);
INSERT INTO A (texts) values('hello')
INSERT INTO A (texts) values('thanks')
INSERT INTO A (texts) values('greetings')
--------------------------
-- Create a temp table, this is imported from an offsite SQL-server machine.
-- Here table is is used to create this table.
--------------------------
select * into #tempA from A
update #tempA set texts = texts+'_new'
------------------------------------------------------------------------
-- The update.
------------------------------------------------------------------------
insert into A (texts)
OUTPUT inserted.id /*not the correct field.*/ AS OLD_ID, inserted.id AS NEW_ID into resolveA -- OUTPUT inserted.id, T.id (I would like to link the new_id and the old_id)
select texts from #tempA T
-- Why, because the table is referenced by other tables using the id.
-- Those id's come from the same source and should be resolved.
-- So I would like to get the OLD and the NEW ID, here it is twice the new ID.
-- drop table A; drop table #tempA ; drop table resolveA
select * from resolveA
-- thanks for your time and attention,
-- Ben
April 1, 2019 at 12:33 pm
I don't think you can because only the inserted table is in scope for INSERT.
Why do you not use MERGE?
Far away is close at hand in the images of elsewhere.
Anon.
April 1, 2019 at 1:02 pm
deleted.IDColumn AS OldID
Oh, then you want:
<span class="pln">OUTPUT inserted</span><span class="pun">.</span><span class="pln">IDColumn </span><span class="kwd">AS</span><span class="pln"> NewID</span><span class="pun">,</span><span class="pln">
deleted</span><span class="pun">.</span><span class="pln">IDColumn </span><span class="kwd">AS</span><span class="pln"> OldID</span>
insert into A (texts)
OUTPUT DELETED.id AS OLD_ID, inserted.id AS NEW_ID into resolveA -- OUTPUT inserted.id, T.id (I would like to link the new_id and the old_id)
select texts from #tempA T
/*
Results in the error:
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "DELETED.id" could not be bound.
*/
-- Sorry for the formatting, did some searching, but haven't found the correct BBCodes for SQL formatting. Sorry. Formatting adjusted 🙂 🙂 🙂 🙂 🙂 🙂 🙂 🙂
-- Thanks Thom.
-- Why do you not use MERGE?
If it is possible to use the OUTPUT in the INSERT, then this is far more simpler than using the MERGE.
And as I get from previous discussions the MERGE is not completely to be trusted. (??? this is hear say ???)
Thanks,
Not yet used to all new formats and (missing) of features.
Ben
April 1, 2019 at 1:14 pm
Sorry for the formatting, did some searching, but haven’t found the correct BBCodes for SQL formatting. Sorry. — Thanks Thom.
Click "{;} insert/edit code sample". Paste code into popup window. 🙂
Also, should have been specific, as you're inserting there will be no deleted table; and therefore no "old ID". This is why I assumed you meant you had a column called "OldID", as there is no existing data. If you want a NULL value then do OUTPUT NULL AS OldID, inserted.ID AS NewID. You'll need to be using an UPDATE statement; an INSERT can't update existing rows. I think you're mixing up the 2 statements. An INSERT and UPDATE do exactly as they say on the tin; they insert rows into the table, or update the existing rows.
If this isn't what you're after, sample data and expected results/behaviour will make things a lot easier (please take the time to format it 🙂 ).
As for not using MERGE, it was awful in SQL Server 2008. Since then, it has got better; but there are still some concerns on things. Pretty sure Aaron Bertrand did an article on it which you would find with a quick Google.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 1, 2019 at 2:42 pm
Hello Tom,
In the temp version of the table, you see an id, this does not get inserted into the new table.
In the example if you run : SELECT * FROM a
You get :
1000 hello
1001 thanks
1002 greetings
The same values in the temp table.
After inserting in the resolve table you get
old_id new_id
1003 1003
1004 1004
1005 1005
But the situation I want in the resolve table is :
old_id new_id
1000 1003
1001 1004
1002 1005
Thanks for your time and attention,
Ben
(The resolve table I have assumed that the original row 1000 will become the new row 1003 etc. It could be that the original row 1000 becomes the new row 1005. There is no control which row becomes which row). Because other tables link to this table a resolve table is needed.
Because of RBAR, I do not want to insert the rows one by one. Because the identity is used as a PK, there can not be another PK. smily.
Again sorry for the formatting, a short search did not come up with a correct command for correct formatting of SQL code. There is probably a simpel [SQL][/SQL] mechanism, but have not stumbelt on it yet
April 1, 2019 at 2:45 pm
As for not using MERGE, it was awful in SQL Server 2008. Since then, it has got better; but there are still some concerns on things. Pretty sure Aaron Bertrand did an article on it which you would find with a quick Google.
I used MERGE on 2008R2 and now on 2016. I have not seen the reported bugs but perhaps I am just lucky in that I have not met the conditions causing the bugs.
I use MERGE mostly as a solution to the problem posted, to output input columns not inserted to the destination table in the OUTPUT clause.
Far away is close at hand in the images of elsewhere.
Anon.
April 1, 2019 at 2:52 pm
-- Found the formatting smily smily smily.
-- Example
-- Ben
-- 20190401
--
--
drop table A;
drop table #tempA ;
drop table resolveA
----------------–
-- Set up a table A
----------------–
Create table A (id int identity(1000,1)
, texts varchar(200))
Create table resolveA (old_ID int
, new_ID int)
INSERT INTO A (texts) values(‘hello’)
INSERT INTO A (texts) values(‘thanks’)
INSERT INTO A (texts) values(‘greetings’)
----------------–
-- Create a temp table, this is imported from an offsite SQL-server machine.
-- Here table is is used to create this table.
----------------–
select * into #tempA from A
update #tempA set texts = texts+’_new’
-----------------------------------------------
-- The update.
----------------------------------------------
--
insert into A (texts)
OUTPUT inserted.id /*not the correct field.*/ AS OLD_ID
, inserted.id AS NEW_ID
into resolveA
select texts from #tempA T
-- Why, because the table is referenced by other tables using the id.
-- Those id’s come from the same source and should be resolved.
-- So I would like to get the OLD and the NEW ID, here it is twice the new ID.
--
drop table A;
drop table #tempA ;
drop table resolveA
-- Before the insert
select * from A -- (Has 'old' identities')
select * from #tempA -- (Has 'old' identities')
-- After insertion
select * from A -- (The newly inserted rows have new identities)
select * from resolveA -- (Should have the old and the new identities).
-- thanks for your time and attention,
-- Ben
April 1, 2019 at 5:17 pm
You can only put columns in the OUTPUT clause from the INSERTED and DELETED table(s), if/when each is present.
Sadly, you can't use any column from any input in the query, only columns directly modified by the statement.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 2, 2019 at 6:58 am
This was removed by the editor as SPAM
April 2, 2019 at 10:55 am
One workaround is to add the OldId column to the new table, and include it in your INSERT statement. It'll then be available to the OUTPUT..INTO.
You can then decide if you still need to use the OUTPUT table, or do what ever you need to do using the destination table.
Once the processing is done, drop the OldId column.
April 2, 2019 at 1:24 pm
Hello Scott, David, Thom and other readers.
Optimistic as I was I 'thought' that the OUTPUT clause in the INSERT (and UPDATE) statement was the solution to get the OLD identity en the NEW identity 'matched' in a 'resolve table'. So that after the insert it was clear which identity from the OLD system matches which identity from the NEW system. This would help with exporting MASTER/DETAIL/DETAIL tables from one system to another system.
The merge statement can do this, but there are some caveats. (Thom: "it was awful in SQL Server 2008. ..... but there are still some concerns ....".) Below I have included the code for the MERGE to achieve the required result.
As for the OUTPUT clause of an insert, it does not realy add functionality to the language. The same effect can be reached by some extra statement(s) which is often simpler to read and write than the OUTPUT clause is. WHERE the output in the MERGE can be used to resolve the OLD identity with the NEW identity, even if there is no other set of fields which can be used for that. So I am a bit disappointed with the OUTPUT clause of the INSERT statement (useless ???).
Thanks for educating me,
Ben
The merge code for the given example.
The merge code for the given example.
------------------------------------------------------------------------
-- The Merge Statement.
------------------------------------------------------------------------
MERGE INTO A AS TGT
USING (SELECT * FROM #tempA ) AS SRC ON 1 = 2
WHEN NOT MATCHED THEN
INSERT(texts) VALUES(SRC.texts)
OUTPUT SRC.id, inserted.id
INTO resolveA(OLD_ID, NEW_ID);
-- Check the result
SELECT * FROM RESOLVEA
April 2, 2019 at 1:36 pm
Thank you schleep,
One workaround is to add the OldId column to the new table, and include it in your INSERT statement. It’ll then be available to the OUTPUT..INTO.
In general you do not want to change the meta data on a production database, it might break something and there is always the problem of concurrency. So changing an actual production table is (very often) not an option. (Problems with triggers, views, replication, partitioning etc. etc.)
The OUTPUT clause from the INSERT is pretty useless (????).
The OUTPUT clause of the MERGE, does solve the problem. (Thom: “it was awful in SQL Server 2008. ….. but there are still some concerns ….”.)
For my current problem, I did go with the logical keys present in the current tables. But this restricts the solution to non tables having a non changing 'extra' unique key next to the identity. (Which is therefore a not normalised implementation). To have a more generic solution I should go with the MERGE construction.
Ben
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply