Optimising following sql query

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 2005 Forums

Transact-SQL (2005)

Optimising following sql query

Author

Topic

rakesh123
Starting Member

2 Posts

Posted - 2008-11-06 : 10:46:40

I have a table with following records
Id Identifier ProviderId CreatedBy CreatedDate
GUID OR 123 X 01/01/2007
GUID OK 123 X 01/01/2007
GUID OM 123 X 01/01/2007
GUID OL 234 X 01/01/2007

I want to get the records corresponding to providerid ‘123’ and insert new set of records. So the final table will look like

Id Identifier ProviderId CreatedBy CreatedDate
GUID OR 123 X 01/01/2007
GUID OK 123 X 01/01/2007
GUID OM 123 X 01/01/2007
GUID OL 234 X 01/01/2007
GUID OR10 123 Y Todays date
GUID OK10 123 Y Todays date
GUID OM10 123 Y Todays date

I did this by selecting the first table in to a temporary table and loop, process and insert new set of records. Is there any efficient way of doing this?

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-11-06 : 10:54:50

What's wrong with the following:

INSERT INTO Table(Identifier, ProviderId, CreatedBy, CreatedDate)
SELECT Identifier + '10', ProviderId, 'Y', CURRENT_TIMESTAMP
FROM Table
WHERE ProviderId = 123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 10:55:02

[code]INSERT INTO Table (Identifier,ProviderId,CreatedBy,CreatedDate)
SELECT Identifier+'10',ProviderId,'Y',GETDATE()
FROM Table
WHERE ProviderId=123[/code]

i assume GUID is of type uniqueidentifier with default set to NEWID() or NEWSEQUENTIALID()

rakesh123
Starting Member

2 Posts

Posted - 2008-11-07 : 00:28:01

Thats just display of records in the format i needed (Select statement u specified). I want to have records physically inserted in the table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 01:00:35

quote:
Originally posted by rakesh123

Thats just display of records in the format i needed (Select statement u specified). I want to have records physically inserted in the table.

Did you had a chance to see INSERT on top of SELECT query? it really inserts values to table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources