How to insert primary keys without using identity

I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?

thanks for your time,

AndreIt is possible with cursors, but would be awkward and I expect subject to problems if the table was having lots of records added by lots of users at the same time...

Why do you not want to use Autoincrement?

How to insert a row in a table with 1 identity column

HI,

I have an SQL Server table with only 1 column. That column is an
identity column. How can I insert a row in this table using SQL
syntax?

I tried insert
into T_tableName () values ()
and a few other options, but I can't seem to get it to insert.

Thanks

Alain"Alain Filiatrault" <alainf@.humaprise.com> wrote in message
news:45d95216.0312230604.328d0c8a@.posting.google.c om...
> HI,
> I have an SQL Server table with only 1 column. That column is an
> identity column. How can I insert a row in this table using SQL
> syntax?
> I tried insert
> into T_tableName () values ()
> and a few other options, but I can't seem to get it to insert.
> Thanks
> Alain

CREATE TABLE T
(
col INT IDENTITY NOT NULL PRIMARY KEY
)

INSERT INTO T
DEFAULT VALUES
INSERT INTO T
DEFAULT VALUES

SELECT col FROM T

col
1
2

Regards,
jag

How to insert a new record (and get it's identity), if a record is not found

What I'm trying to do is write a query (actually it will be a sub-query) that gets the ID of a record using a standard SELECT/WHERE, but if the record is not found then I want to insert a record and return the ID of the inserted record.

So far I've got this (which doesn't work!)

SELECT ISNULL((Select ContactID AS ID FROM [TileManager].[dbo].[Contact] WHERE ([Salutation] = 'Mrs' AND [Forename] = 'Freda' AND [Surname] = 'Bloggs')), (INSERT INTO [TileManager].[dbo].[Contact] ([Salutation],[Forename],[Surname]) VALUES ('Mrs','Freda','Bloggs'); SELECT @.@.IDENTITY AS ID))

Any help would be greatly appreciated.

Rob:

Be careful using the @.@.identity function; try SCOPE_IDENTITY() instead. Look it up in the books.

Dave

|||

I don't believe you can embedded the insert statement like that - why aren't you trying something like this

INSERT Contact (Salutation,Forename,Surname) SELECT 'Mrs','Freda','Bloggs'
WHERE NOT EXISTS (SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs')
SELECT ID = @.@.IDENTITY

|||

SELECT ID = @.@.identity will almost always work; it can experience problems such as:

-- -
-- Illustrate the difference between @.@.identity and scopy_identity()
--
-- In this example, @.@.identity returns the value of the identity
-- that was inserted by the trigger
-- -
set nocount on

create table dbo.whatA ( what1 integer identity)
go
create table dbo.whatB ( what2 integer identity)
go

insert into whatA default values insert into WhatA default values
insert into whatB default values
insert into whatA default values insert into WhatA default values
insert into whatA default values insert into WhatA default values

--select * from whatA
--select * from whatB
go

create trigger dbo.trI_whatB on whatB
for insert
as
begin
insert into whatA default values
end

go

insert into whatB default values
select @.@.identity as [Incorrect @.@.Identity]
select scope_identity() as [Correct scope_identity()]
select max (what1) as [WhatA Identity] from whatA
select max (what2) as [WhatB Identity] from whatB
go

drop trigger dbo.trI_whatB
go
drop table dbo.whatA
go
drop table dbo.whatB
go

--
-- O U T P U T :
--

-- Incorrect @.@.Identity
-- -
-- 7

-- Correct scope_identity()
-- -
-- 2

-- WhatA Identity
-- --
-- 7

-- WhatB Identity
-- --
-- 2

|||

That's close to what I'm trying to get, but, what I want is the ID of the record if it is found or the ID of the added record if it is not found. The code you've given returns NULL if the record already exists.

Thanks

|||

INSERT Contact (Salutation,Forename,Surname) SELECT 'Mrs','Freda','Bloggs'
WHERE NOT EXISTS (SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs')

SELECT ID FROM Contact WHERE Salutation = 'Mrs' AND Forename = 'Freda' AND Surname = 'Bloggs'

is it acceptable for you to do it as two statements like this?

AWAL

|||

This will do the existance check first and only insert if no record is found...

DECLARE @.ID int

--Get value if existing

SELECT @.ID

FROM [TileManager].[dbo].[Contact]

WHERE ([Salutation] = 'Mrs' AND [Forename] = 'Freda' AND [Surname] = 'Bloggs');

--No value found so insert

IF @.ID IS NULL

BEGIN

INSERT INTO [TileManager].[dbo].[Contact] ([Salutation],[Forename],[Surname]) VALUES ('Mrs','Freda','Bloggs');

SELECT @.ID = SCOPE_IDENTITY();

END

--Use ID for something

SELECT @.ID

sql

how to insert a identity key through store procudure?

I want to insert 3 values to sql database through store procudure

-- a store procudure is like this--

ALTER PROCEDURE dbo.FreeExperience

@.identity ,

@.name nvarchar(20),
@.gender int,

AS
begin
Insert into list(cid,name,gender)
values (@.identity,@.name,@.gender)

end
RETURN

------------------

the cid is a identity value . it will +1 automatically when user insert a new data..

if i ingore this column in store procudure it will cause error (becuase this column is not allow null)

please help. thanks

Try

ALTER

PROCEDURE dbo.FreeExperience
@.namenvarchar(20),
@.genderint,
AS
INSERTINTO list(name,gender)values(@.name,@.gender)
RETURN
GO

or

CREATE

PROCEDURE dbo.FreeExperience2
@.identityINT,
@.namenvarchar(20),
@.genderint,
AS
SETIDENTITY_INSERT listON-- Allows Identity value to be supplied
Insertinto list(cid,name,gender)values(@.identity,@.name,@.gender)
SETIDENTITY_INSERT listOFF
RETURN|||

if you cid field is setup as identity in you table (cid int identity(1,1))

insert statement like this should work without any errors

Insert into list(name,gender)
values (@.name,@.gender)

and identity value will be automatically set by SQL server

|||

I still got error message...

exceptiona information: System.Data.Sqlclien.SqlException: procedure or parameters 'FreeExperience' must have a parameters'@.identity', but didn't provide..

p.s FreeExperience is my store procedure name..

|||

If you want SQL Sever to automatically add the Identity value for you, you need to remove the column entirely from the proc, and your INSERT statement. If you want to manually supply the value then you use the SET IDENTITY_INSERT <Table> ON clause, and provide the value you want to be used instead. IF the column is a Primary Key for the table, you also need to make sure the value you are providing does not already exist.

|||

thank you... but I am not sure if I really understand what did you mean

can you please give me a simple example for this ?

this store procudure is for a program which will insert new colum to table

1 column named " cid" in table which is set as a identity column will automatically +1 and insert to cid

|||

Use this stored procedure
alter PROCEDURE dbo.FreeExperience
@.name nvarchar(20),
@.gender int,
AS
Insert into list(name,gender) values (@.name,@.gender)
RETURN

|||

this stored procedure will cause error

because the cid column is not allow to be null...

|||

>>this stored procedure will cause error because the cid column is not allow to be null...

Identity columns are populated automatically by the database so the stored procedure will work!

|||

thank you .. but it really doesn't works..

it continue showing the error message...

can't insert NULL to 'cid，table'list'; not allow Null。INSERT fail..

I checked all column in table list from database, all column allow null except column cid.

the statement will work only if I set the cid column as accept null ..

please help..

|||

Modify ur table by setting the "IdentityIncrement=1, IdentitySeed=1" of column cId, then remove cId from ur storedprocedure

HTH

|||

Modify ur table by setting the "IsIdentity=Yes, IdentityIncrement=1, IdentitySeed=1" of column cId, then remove cId from ur storedprocedure

HTH

how to insert identity values

hi to the group,
i am small problem,
i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc)
with out using identity(sql server)/generated always(db2)
can any one knows please explain itDidn't I answer this one already?

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx|||Didn't I answer this one already?

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx
OMFG brett, are you kidding?

you seriously expect people to remember everything you wrote up to several years ago on a completely different site from this one?

i don't know, man, did you answer it already?

sheesh|||jagadish, look up the SET IDENTITY INSERT option in Books Online.

how to improve performance in this query?

Hey guys,

Here's my situation:

I have a table called lets say 'Tree', as illustred bellow:

Tree
====
TreeId (integer)(identity) not null
L1(integer)
L2(integer)
L3(integer)
...
L10(integer)

The combination of the values of L1 thru L10 is called a "Path" , and
L1 thru L10 values are stored in a second table lets say called
'Leaf':

Leaf
====
LeafId (integer)(identity) not null
LeatText varchar(2000)

Here's my problem:

I need to lookup for a given keyword in each path of the tree table,
and return each individual column for the paths that match the
criteria. Here's the main idea of how I have this now.

SELECT TreeId,L1,L2,...,L10, GetText(L1) + GetText(L2) as L2text + ...
+ GetText(L10) AS PathText
INTO #tmp FROM Tree //GetText is a lookup function for the Leaf table

SELECT L1,GetText(L1),L2,GetText(L2),...,L10,GetText(L10) FROM #tmp
WHERE
CharIndex(@.keyword,a.pathtext) > 0

Does anyone would know a better,smart, more efficient way to
accomplish this task? :)

Thks,On 1 Nov 2004 08:23:44 -0800, Silvio wrote:

>Does anyone would know a better,smart, more efficient way to
>accomplish this task? :)

Hi Silvio.

Yep. Improve your table design. You should normalize down to at least
third normal form. That emans that the repeating group (L1 ... L10) in the
Leaf table has to go in it's own table:

CREATE TABLE Paths
(TreeID int NOT NULL REFERENCES Trees,
PathNo int NOT NULL CHECK (PathNo BETWEEN 1 AND 10),
LeafID int NOT NULL REFERENCES Leaves,
PRIMARY KEY (TreeID, PathNo)
)

If the same leaf can't occur twice in a tree, you could also add a
UNIQUE(TreeID, LeafID) constraint, or make that the primary key. If the
order of the leaves in a tree is unimportant, you can leave out the PathNo
column.

>I need to lookup for a given keyword in each path of the tree table,
>and return each individual column for the paths that match the
>criteria.

Probably something like this:

SELECT P.PathNo, L.LeafText
FROM Paths AS P
INNER JOIN Leaves AS L
ON L.LeafNo = P.LeafNo
WHERE EXISTS (SELECT *
FROM Paths AS P2
INNER JOIN Leaves AS L2
ON L2.LeafNo = P2.LeafNo
WHERE P2.TreeID = P.TreeID
AND CHARINDEX(@.keyword, L2.LeafText) > 0)
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

How to identify IDENTITY column on a linked server table?

Using SQL2000 (latest SP and patches)...
I'm trying to identify the IDENTITY column of a table in a database on a
linked server. Ordinarily I'd just use sp_columns, but since it's a linked
database I have to use sp_columns_ex, and it doesn't return the IDENTITY
column identifier!
Help!! :) Any ideas? Thanks!
JackHave you tried
exec linkerserver.db.dbo.sp_help tablename
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jack Black" <jackblackisback@.hotmail.com> wrote in message
news:%23e438$y0DHA.404@.tk2msftngp13.phx.gbl...
> Using SQL2000 (latest SP and patches)...
> I'm trying to identify the IDENTITY column of a table in a database on a
> linked server. Ordinarily I'd just use sp_columns, but since it's a
linked
> database I have to use sp_columns_ex, and it doesn't return the IDENTITY
> column identifier!
> Help!! :) Any ideas? Thanks!
> Jack
>|||Thanks for responding! :)
Yeah, I've tried that... Suffice to say the scripting language I'm using
(ColdFusion) doesn't support multiple resultsets without using their stored
procedure tag (CFSTOREDPROC), so I would have to build the query into a
stored proc. Normally not a problem, but CFSTOREDPROC is hardwired to
execute all sp's with a call command, so the syntax executed would be "CALL
EXEC ...", resulting in a failed call. And since CALL doesn't work with
linked server syntax, I can't use it.
I'm trying to avoid requiring a local DSN pointing to the remote SQL server,
but I think it's coming down to needing that because of CF's shortcomings...
Any other thoughts welcome!! :)
Jack
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:u2sBBC40DHA.716@.TK2MSFTNGP12.phx.gbl...
> Have you tried
> exec linkerserver.db.dbo.sp_help tablename
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Jack Black" <jackblackisback@.hotmail.com> wrote in message
> news:%23e438$y0DHA.404@.tk2msftngp13.phx.gbl...
> > Using SQL2000 (latest SP and patches)...
> >
> > I'm trying to identify the IDENTITY column of a table in a database on a
> > linked server. Ordinarily I'd just use sp_columns, but since it's a
> linked
> > database I have to use sp_columns_ex, and it doesn't return the IDENTITY
> > column identifier!
> >
> > Help!! :) Any ideas? Thanks!
> > Jack
> >
> >
>