insert query - 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

General SQL Server Forums

New to SQL Server Programming

insert query

Author

Topic

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-20 : 06:41:22

hello sir
In my project i have one table in that table i have feild
UserId,StockName,status,CreatedBy,CreateDate,AssignHistoryCode ....
i want to edit one record in that table and insert new same record only
CreatedBy,CreateDate,and status are different.
so how is it possible
pls help me
its urgent.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 06:45:32

INSERT Table1 (UserId, StockName, status, CreatedBy, CreateDate, AssignHistoryCode)
SELECT UserId, StockName, {New value here}, {New value here}, GETDATE(), AssignHistoryCode
FROM Table1
WHERE {Some record filtering here}

E 12°55'05.25"
N 56°04'39.16"

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-20 : 07:33:23

thanku very much.
but in that quey if i want create date is null then what can i do. pls help me.

INSERT Table1 (UserId, StockName, status, CreatedBy, CreateDate, AssignHistoryCode)
SELECT UserId, StockName, {New value here}, {New value here}, GETDATE(), AssignHistoryCode
FROM Table1
WHERE {Some record filtering here}

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 07:34:38

INSERT Table1 (UserId, StockName, status, CreatedBy, CreateDate, AssignHistoryCode)
SELECT UserId, StockName, {New value here}, {New value here}, NULL, AssignHistoryCode
FROM Table1
WHERE {Some record filtering here}

E 12°55'05.25"
N 56°04'39.16"

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-20 : 07:44:25

first thanku very much.
and in this query i want automaticaly increase the analystassigncode.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 07:48:19

Sure.
Where is the column resided?

E 12°55'05.25"
N 56°04'39.16"

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-20 : 07:51:37

hello
its my primary key.
and this is my first column in same table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 07:55:39

Is it an IDENTITY column too?

E 12°55'05.25"
N 56°04'39.16"

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-20 : 07:57:27

yes its identity column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 08:11:44

Then no worries! The column value in AnalystAssignCode will increase automatically.

E 12°55'05.25"
N 56°04'39.16"

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-20 : 23:56:55

hello
its not done automatically.
what is identity column.
its primary key.
its numeric(10,0)Not Null.
pls help me.
gayatri

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-21 : 00:23:55

pls sir its urgent.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 00:37:24

IDENTITY column means the column will automatically populated by SQL Server. We dont need to explicitly pass the value. It has two parts a seed value and increment value. The values for this column will start from seed value and for each new record it is incremented by increment value.

i.e If we define it as as IDENTITY(1,1) it starts with 1 then increments with 1 for each successive records.

Mostly primary keys are defined as IDENTITY columns so that we dont explicitly need to pass the values.If its is not then we might need to take MAX(pk) from table each time and insert MAX(pk) +1 as pk value for next record.

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-21 : 00:50:57

yes you are right but how can i use that max function in above query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 01:34:50

Try this. It should do the trick if you are inserting 1 record at a time.
INSERT Table1 (UserId, StockName, status, CreatedBy, CreateDate, AssignHistoryCode)
SELECT UserId, StockName, {New value here}, {New value here}, NULL, MAX(AssignHistoryCode) + 1
FROM Table1
WHERE {Some record filtering here}

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-21 : 02:31:39

its not run
i have one more code i.e

DECLARE @AnalystAssignhistorycode INT

BEGIN TRAN

SELECT @AnalystAssignhistorycode = MAX(AnalystAssignhistorycode)
FROM tAnalystAssignHistory

SELECT @AnalystAssignhistorycode = COALESCE(@AnalystAssignhistorycode, 0) + 1

INSERT tAnalystAssignHistory
(AnalystAssignhistorycode,
SecurityCode,
ISIN,StockName,UserId,IsDummyEnabled,AnalystAssignDate,StatusCode,
CreatedBy, CreateDate,UpdatedBy,UpdateDate)
SELECT @AnalystAssignhistorycode,
SecurityCode,
ISIN,StockName,'PatelAmi',IsDummyEnabled,AnalystAssignDate,'A',
'Gayatri', GETDATE(),NULL,NULL
from tAnalystAssignHistory
where
UserId = 'ShahVis' and StockName = ' Cipla Ltd.'

COMMIT TRAN

its run but i didnt understand what is this.
can i use stored procedure for above purpose.and how?
pls help me.
its realy imp for me.
thanku

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 02:47:54

The code looks fine to me. Its basically taking MAX(pk) from table and if its not returned(first time insertion) its putting it to 1.You can make a procedure out of this snippet itself.

just give it like this

CREATE PROCEDURE procname

....any parameters you want here(as an example you can take

@UserID varchar(50)
@StockName varchar(100)
AS

your code ( you may remove BEGIN TRAN...COMMIT TRAN lines)
please change last WHERE as follows
where
UserId = @UserID and StockName = @StockName

GO

you can call sp like this

EXEC @StockName 'ShahVis',' Cipla Ltd.'

b/w what was error you gopt for my script?

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-21 : 02:57:56

INSERT tAnalystAssignHistory AnalystAssignhistorycode,
SecurityCode,
ISIN,StockName,UserId,IsDummyEnabled,AnalystAssignDate,StatusCode,
CreatedBy, CreateDate,UpdatedBy,UpdateDate)
SELECT MAX(AnalystAssignhistorycode) + 1,
SecurityCode,
ISIN,StockName,'PatelAmi',IsDummyEnabled,AnalystAssignDate,'A',
'Gayatri', GETDATE(),NULL,NULL
FROM tAnalystAssignHistory
where
UserId = 'ShahVis' and StockName = ' Cipla Ltd.'

error is --Line 1: Incorrect syntax near 'AnalystAssignhistorycode'(insert).
thanku

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 03:18:18

Put a "(" in the first line between "tAnalystAssignHistory" and "AnalystAssignhistorycode".

E 12°55'05.25"
N 56°04'39.16"

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-21 : 03:27:37

hello
i already have this bracket.but not run the query.

INSERT tAnalystAssignHistory
(AnalystAssignhistorycode,SecurityCode,ISIN,StockName,
UserId,IsDummyEnabled,AnalystAssignDate,StatusCode,
CreatedBy, CreateDate,UpdatedBy,UpdateDate)
SELECT AnalystAssignhistorycode,
SecurityCode,ISIN,StockName,'PatelAmi',
IsDummyEnabled,AnalystAssignDate,'A',
'Gayatri', GETDATE(),NULL,NULL
from tAnalystAssignHistory
where UserId = 'ShahVis' and StockName = ' Cipla Ltd.'

thanku for urgent reply.

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-21 : 03:30:28

sorry for above reply its wrong qury.

INSERT tAnalystAssignHistory (AnalystAssignhistorycode,
SecurityCode,
ISIN,StockName,UserId,IsDummyEnabled,AnalystAssignDate,StatusCode,
CreatedBy, CreateDate,UpdatedBy,UpdateDate)
SELECT MAX(AnalystAssignhistorycode) + 1,
SecurityCode,
ISIN,StockName,'PatelAmi',IsDummyEnabled,AnalystAssignDate,'A',
'Gayatri', GETDATE(),NULL,NULL
FROM tAnalystAssignHistory
where
UserId = 'ShahVis' and StockName = ' Cipla Ltd.'

i have eeor==
'tAnalystAssignHistory.SecurityCode' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

thanku

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources