Transact-SQL: insert into xyz ( select * from abc )

Question

Hi,

i want to realize a construct in MS SQL that would look like this in Oracles PL/SQL:

declare
asdf number;
begin
for r in (select * from  xyz) loop
   insert into abc (column1, column2, column3) 
   values (r.asdf, r.vcxvc, r.dffgdfg) returning id into asdf;

    update xyz set column10 = asdf where ID = r.ID;
end loop;
end;

Any idea how to realize this would be helpful.

Thanks in advance

Answer 1

This seems to be simply a copy of one table, right?

Well:

SELECT column1, column2, column3 INTO abc FROM xyz

I think you could also to something like

INSERT INTO abc SELECT column1, column2, column3 FROM xyz

but in the second case you need to create the table before, the first instead does also create the TABLE

Cheers Johannes

Answer 2

declare @asdf int/varchar -- unsure of datatype
declare @vcxvcint/varchar -- unsure of datatype
declare @dffgdfg int/varchar -- unsure of datatype
declare @id int

declare db_cursor CURSOR FOR SELECT asdf, vcxvc, dffgdfg FROM xyz

OPEN db_cursor FETCH NEXT FROM db_cursor
INTO @asdf, @vcxvcint, @dffgdfg

WHILE @@FETCH_STATUS = 0 BEGIN

insert into abc (column1, column2, column3) values (@asdf, @vcxvcint, @vcxvcint)

set @id = scope_identity() -- This will get the auto generated ID of the last inserted row

update xyz set column10 = @asdf where id = @

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor
DEALLOCATE db_cursor

Of course basically all DBA's will kill you if you try and sneak a cursor into production code.

Answer 3

Version without the cursor, but with the temp column:

-- //temporarily add the column (assume the table "abc" already exists)
ALTER TABLE "abc" ADD xyzID INT;
GO;
-- //insert all the data (assuming the ID field on "xyz" is called ID)
INSERT INTO "abc" (column1, column2, column3, xyzID) SELECT asdf, vcxvc, rdffgdfg, ID FROM "xyz";
-- //update "xyd" with the new ID
UPDATE "xyd" SET column10 = "abc".ID FROM "xyd" INNER JOIN "abc" ON "xyd".ID = "abc".xydID
-- //drop the temporary column
ALTER TABLE "abc" DROP COLUMN xyzID;

Answer 4

If I understood what you asked (I'm not proficient with PL/SQL), looks like a pretty easy task:

INSERT INTO abc(column1, column2, column3) SELECT asdf, vcxvc, dffgdfg FROM xyz;
UPDATE xyz SET column10 = id;

But I'm just guessing your intention, hope haven't misunderstood.

P.S.: as someelse already pointed out, you must have already created table abc

Answer 5

Hope this is what you are looking for:

declare
asdf number;
begin
for r in (select * from  xyz) loop
   insert into abc (column1, column2, column3) 
   values (r.asdf, r.vcxvc, r.dffgdfg) returning id into asdf;

    update xyz set column10 = asdf where ID = r.ID;
end loop;
end;

would become

DECLARE @asdf int
DECLARE @ID int
DECLARE @MyTmpTableVar table(asdf int, abc_id int)

// insert records from your cursor r into table abc, 
// and return a temporary table with "id" and "asdf" fields from the xyz table
INSERT INTO abc(column1, column2, column3)
OUTPUT asdf, id INTO @MyTmpTableVar
SELECT r.asdf, r.vcxvc, r.dffgdfg
FROM xyz

// if it would return just one row and you wanted to find the value
//SELECT @asdf = asdf, @id = abc_id
//FROM @MyTmpTableVar

// update the table xyz with the values stored in temporary table
// restricting by the key "id"
UPDATE xyz
SET xyz.column10 = t.asdf
FROM @MyTmpTableVar AS t
WHERE xyz.id = t.abc_id

---

The sqlServer version of the returning clause in Oracle is the OUTPUT clause.

Please visit this msdn page for full information