how do I do this without a cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how do I do this without a cursor?

Author

Topic

edpel
Starting Member

22 Posts

Posted - 2003-12-17 : 14:20:35

I have a table which has a primary key on a character(18) column. I know this is a bad design but I can't change it. Usually, the column is stamped with a unique identifier through our application however, I need to write an insert that moves data from one table into this table. I have a way of generating the uniqueid, but I don't want to do it with in a cursor, row by row, just want to do it as a batch...how can I do this without using a cursor?

The table structure can't be changed in any way.

Thanks,
Eddie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-17 : 14:23:08

To move data from one table to another:

INSERT INTO DestinationTable (Column1, Column2...)
SELECT Column1, Column2
FROM SourceTable

Do you need the data to be modified when it gets to the destination table, if so please provide more information.

Tara

edpel
Starting Member

22 Posts

Posted - 2003-12-17 : 14:26:44

Yep, I know how to do the insert, just want a way to insert into the unique column without having to loop through each row in a cursor and generate a unique id.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-17 : 14:28:53

Give us some information on the unique id and we'll come up with the solution. What do you want the unique id to look like? Can you use an identity column?

Tara

edpel
Starting Member

22 Posts

Posted - 2003-12-17 : 15:02:00

The table (table A) currently uses a char(18) field for it's unique id. These 18 characters are all numbers. It doesn't have to look like anything specific, just has to be unique. I would love to use an identity column but can't change the table structure. I have also thought about pumping the data into a temp table that has an identity column, then moving it into Table A but this script may be run several times so this wouldn't be guaranteed unique as Table A already has some rows in it utilizing the 18 character unique id stamped by our app. I have also thought of just take the max unique id from Table A and incrementing it by 1 each time but I that would still require me to put my insert into a cursor...I believe.

Thanks,
Eddie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-17 : 15:25:03

Here ya go:



SET NOCOUNT ON

CREATE TABLE Table1
(
Column1 CHAR(18),
Column2 VARCHAR(2)
)

CREATE TABLE Table2
(
Column1 CHAR(18),
Column2 VARCHAR(2)
)

DECLARE @num INT

INSERT INTO Table1 VALUES('1', 'T')
INSERT INTO Table1 VALUES('2', 'S')
INSERT INTO Table1 VALUES('3', 'CA')
INSERT INTO Table1 VALUES('4', 'RE')

INSERT INTO Table2 VALUES('1', 'SD')
INSERT INTO Table2 VALUES('5', 'po')
INSERT INTO Table2 VALUES('6', 'LL')
INSERT INTO Table2 VALUES('4', 'Q')

SELECT @num = MAX(Column1) + 1
FROM Table1

EXEC ('SELECT Column1 = IDENTITY(INT, ' + @num + ', 1), Column2 INTO TempTable FROM Table2')

INSERT INTO Table1
SELECT Column1, Column2
FROM TempTable

SELECT *
FROM Table1

DROP TABLE Table1
DROP TABLE Table2
DROP TABLE TempTable

In my example, Table1 is the destination table, Table2 holds the data that you want moved. I use TempTable (needs to be a real table and not a # temp table due to dynamic sql) so that I can use IDENTITY function (only works with SELECT INTO). Let me know if you need more help.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources