CODEWITHSUNDEEP
sqlsql-servert-sqlloopscursor
Skunny11
Skunny11

Reputation: 39

T-SQL Adding each record from a resultset into an existing table without using Cursor?

I am pretty new to T-SQL and I am looking for a way to add each record from a distinct select query into an existing table with that records name. I will probably need the use of variables and a loop, just not sure how this is done without cursor because I was told to stay away from having to use cursor.

Here is kind of what I mean (using for each to explain what i need, i know this isnt the format for sql):

DECLARE @thename
DECLARE @record

SET @thename = (SELECT DISTINCT name FROM tableone) 

FOR EACH @record IN @thename
{
INSERT INTO tabletwo
SET @record = SCOPE_IDENTITY()
}

I am using scope_identity because when a record is added to tabletwo it is given an autoincrement id which I will need to reference later on. Any help on how this logic is carried out in T-SQL would be of great help to this novice.

Upvotes: 0

Views: 664

Answers (2)

Riley Major
Riley Major

Reputation: 2014

It depends on what you need to do with the identity values later on. If you really need each value, one at a time, to run some procedural code (such as executing a stored procedure), you will need some sort of a WHILE loop (with or without a CURSOR, but likely with).

However, the best performing code will usually use the list of new identity values as a set. Here is how you could capture the newly created identity values using the OUTPUT keyword:

-- Set up sample data.

DECLARE @SampleNames TABLE
(
    SampleName varchar(500)
);
INSERT INTO @SampleNames (SampleName) VALUES ('Fred');
INSERT INTO @SampleNames (SampleName) VALUES ('Nancy');
INSERT INTO @SampleNames (SampleName) VALUES ('Jim');
INSERT INTO @SampleNames (SampleName) VALUES ('Nancy');
INSERT INTO @SampleNames (SampleName) VALUES ('Jim');

-- Set up your "tabletwo" destination table

DECLARE @DestinationTable TABLE
(
    DestinationTableID int IDENTITY,
    DestinationTableName varchar(500)
);

-- Set up a holding place for your new IDs

DECLARE @ListOfIDs TABLE
(
    DestinationTableID int,
    UniqueName varchar(500)
);

-- Insert the distinct list of names into the destination table, capturing the generated identity values

INSERT INTO @DestinationTable
(
    DestinationTableName
)
OUTPUT
    INSERTED.DestinationTableID,
    INSERTED.DestinationTableName
INTO @ListOfIDs
    (
        DestinationTableID,
        UniqueName
    )
SELECT
    DISTINCT
    SampleName
FROM    @SampleNames;

-- @ListOfIDs will now contain all of your new IDs.

SELECT
    DestinationTableID,
    UniqueName
FROM    @ListOfIDs;

If you really need to execute procedural code for each unique name, you will be hard-pressed to avoid a CURSOR, and you will not be able to avoid a WHILE loop. You could avoid a CURSOR by using SQL Server's XML capabilities to concatenate the unique values into a string, and then use some string manipulation in a loop to extract individual values. This would technically avoid using a CURSOR, but it would not likely have superior performance.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

This is the way you want to build your query

insert into table2
(field1, field2, etc)
select field3, field4, etc
from table1
where whatever

Also the set keyword goes with update queries. Your question has it in an insert query which will throw an error.

Upvotes: 0

Related Questions