cloning sql record - SQL Server Forums

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)

cloning sql record

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2009-01-09 : 19:26:16

I would like to clone a record from the KPITBL to the same table incrementing automaticaly newly inserted record. I don't want to clone every column but only these:
Lead],
[WRM] ,
[PTDB],
[PAR],
[PM],
[RequestingLOB],
[LOB],
[Description],
[ProjectType],
[Responsible,
[RAG]

Here is the matadata:
CREATE TABLE [dbo].[KPITbl](
[TaskID] [int] IDENTITY(1,1) NOT NULL,
[Lead] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WRM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PTDB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PAR] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RequestingLOB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOB] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartLocationState] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinishLocation] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Responsible] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RAG] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartDates] [smalldatetime] NULL,
[EndDates] [smalldatetime] NULL,
[TreeorDomainImpacted] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfSites] [int] NULL,
[NumOfUsers] [int] NULL,
[GBdatamoved] [int] NULL,
[GBdatadeleted] [int] NULL,
[NumOfSrvrsAdded] [int] NULL,
[NumOfSrvrsDecommed] [int] NULL,
[NumOfAppsDeployed] [int] NULL,
[EUTEngineeringConsult] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TimeSpend] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Complexity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastUpdated] [smalldatetime] NULL,
[DCOorSTANDALONE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ECM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StatusCompletedDate] [smalldatetime] NULL,
[StatusCancelledDate] [smalldatetime] NULL,
[CreatedDate] [smalldatetime] NULL,
[StartLocationAddress] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartLocationCity] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PropertyIDStartLoc] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PropertyIDFinishLoc] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PThree] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Sheet1a] PRIMARY KEY CLUSTERED
(
[TaskID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

how do i accomplish this? I would have "CLONE" button somewhere in aspx app. Thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-09 : 21:49:03

INSERT KPITBL (<complete column List excluding the identity column>)
SELECT <Columns to Clone>, NULL or Non-Cloned Values
FROM KBITBL
WHERE TaskID = <id to clone>

the INSERT list of columns needs to match the SELECT list of columns (same columns, same order). You can exclude the nullable columns if you want NULLs in them.

Be One with the Optimizer
TG

jszulc
Yak Posting Veteran

66 Posts

Posted - 2009-01-10 : 01:24:41

Will try this , thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources