Retreiving Autoincrement Field In SQL Server

Aug 1, 2006

Hi, im trying to insert values into 2 different tables. The primary key in the first table is an auto increment field, which is then the foreign key in the 2nd table.

Is there any way to retrieve the autogenerated value before performing the second insert. I have done some searching on the matter, but to no avail.

I tried using LAST_INSERT_ID() but i get the error message that it is not a recognized function name. The other option is using @@identity but i cant see how to put that into my SQL statement.

Can anyone help please.

Thanks in advance
Gareth

View 2 Replies


ADVERTISEMENT

Retreiving Records If The Ff. Field Is On A List..

Jun 23, 2008

hello dudes. i've got the following table:


table name: assassins

fields:
assassinid int, identity, primary key
assassinname nvarchar
assassincallsign nvarchar
knifekills int
sniperkills int
stealthkills int
fibergarotekills int


sample data:
1, Dana White, tapout, 12, 0, 12, 1
2, Chuck Liddell, iceman, 30, 0, 0, 2
3, Timothy Oliphant, FortySeven, 100, 20, 150, 30

supposed i want to retreive only those records whose type of kills is on a given list, how do i do that? the list is actually dynamic since the kill types is actually coming from a checkbox on my page:
[] Knife Kills
[] Sniper Kills
[] Stealth Kills
[] Fibergarrote Kills

supposed i will only check Knife Kills, it will only retreive those records whose knifekills > 0

or if i check Snipers Kills and Stealth Kills, then those records whose sniperkills and stealthkills is > 0.

any of you know how to do that?

U + U + D + D + L + R + L + R + Sel + Start...

View 12 Replies View Related

Field Autoincrement

Dec 10, 2004

hi,
Is their anybody knows how to make field in Sql Server autoincrement?
pls. help me.

View 3 Replies View Related

Is There A Way To Autoincrement A Field With Mixture Of Alpahbetic Characters And Numbers?

Jul 23, 2005

I have a feeling I'll be forced to use a script and a trigger for thistype of field format but I'm wondering if any of your wizards couldpoint at a simple way I could do something like this:For example, if I want to be able to keep track of new orders followingthis incrementing convention:ORD100000001ORD100000002ORD100000003.... etc ...Does MSSQL2000 have features that I can simply set for this kind offield or will I be resorting to writing up a SQL script and a trigger?

View 11 Replies View Related

SQLDMO Retreiving The ListInsstalledInstances For 64 BIT SQL Server

Apr 17, 2008



We are having a Windows Installer Program (32 bit) which calls the SQLDMO to get the list of installed instances in a 64 bit 2005 sql server machine. This fails as the windows installer tries opens up the 32 bit sqldmo and there is no instance of 32 bit sql server on this machine.
Is there any way by which sqldmo can return all 64 bit sql server instance when called from a 32 bit Application?

Regards,
Ashish

View 4 Replies View Related

Sql Server 2005 Autoincrement Value

Dec 25, 2006

I created a new table in sql server 2005. Primary key is autoincrement value of 1. But when I insert the row, it increments by 4. It goes like 1, 5, 9 etc..what could be the problem ?how can I correct it? Do I need to reseed it?
 Thanks!!

View 14 Replies View Related

SQL Server - PRIMARY KEYS (autoincrement)...

Jan 12, 2005

In SQL Server 2000, I'm looking for input as to how to set up primary keys (PKs).

Is it safe to use the IDENTITY feature using AUTOINCREMENT as a setting for a PK, or is it best to generate my own and check for dups?

My concern is whether SQL Server at any point will taint the numbers used as the PK -- because as you know, these PKs are going to wind up linking into (relating into) other tables (e.g. client orders).

Certainly it's vital that the clientID not get lost/changed during any regular DB maintenance or use.

Any input would be appreciated.

View 1 Replies View Related

Autoincrement Question Sql 2005 Server

Sep 25, 2007

How add column autoincrement in sql server 2005 use SQL Server ManagementStudio?Tom

View 1 Replies View Related

Retreiving Specific Row.

Jul 13, 2004

SQL question here:
I am setting up a blog, and I would like the data list to default to the most recent post ONLY. How would I define the filter to get the most recent post?
I have a publishing date in my table, and I can sort the post, but I have no clue how to select just one row to display.

I can get it to work by adding a dropdown list the is bound to the publishing date column, then retreving the post based on the control, but I don't want to use the control. (Making the control not visible is not an option.

View 2 Replies View Related

Retreiving Table Information With SQL

Mar 5, 2004

Hello:

I have an app that needs to retrieve the list of tables in an SQL database. I see that there is a way to do this with the OLEdb provider (GetOleDbSchemaTable() ), but I can't find a sibling method in SQL. IS there one/ Any Ideas?

Mike

View 4 Replies View Related

Storing/retreiving Images In Sql - Need Help - Possible???

Sep 21, 2004

I am storing images within and image field in sql, however when I go to get the data I have two issues.

the first issue is that my image is linked to information on another table but the only way that i have seen getting the image back is to do a:

response.binarywrite(mydatareader.item("image"))

but i am hoping to be able to find a way to use the template functionality with datagrid to align the picture with the information on the table. i am storing the image type and size with the image as well.

the second problem might be related to the load, i am not sure. sometimes, for some images and it just shows the frame of the image with the red x in the middle. The load appears to work (no errors) but since we cannot view them properly we dont know.

Our application wants to store all data in sql so that we can secure it and relate it to projects. in addition, we hope to be able to use this function to upload all sorts of binary documents from jpg, gif, doc, xls, visio, and others as we can. any issues with that process???

we would like to be able to display them directly in the browser where we can but all users have standard office.

any suggestions on either and hopefully both of these situations? we have sort of hit a brick wall.

View 4 Replies View Related

Inserting/retreiving Audio... Please, Please Help

Dec 21, 2005

hi. I am at a new stage in our project and need to figure out how to insert/retreive MP3/Wav files. The way we want to do this is have the file stored on the harddrive while a link to its location is stored in the database.

I have posted this search in other newsgroups and have gotten a lot of philosophical responses, but what i really am looking for is code examples. Please, what i would like to see is "how" this id done in code. If you have done this on your site, i'd really appreciate your posting code here or emailing me examples. Or, if you know of a really good explainaition of how to go about storing/retreiving audio files in the way i mentioned using MS SQL Sever 2000, I'd be equally indebted.

I hope I have explained myself clearly. Thank you for your assistance!

have a peaceful day.

View 4 Replies View Related

Retreiving Dts Package From Backup?

Dec 13, 2004

Last week I discovered a server runing a sql database for a vendor application. Last year sometime one of our former employees created a dts to populate some data in peoplesoft from this database. But, over this last weekend, the vendor came in and uninstalled and reinstalled sql server and only restored their specific database. All I have is backups of msdb and master and I'm not sure how to restore our dts package. Any clues would be appreciated.

View 4 Replies View Related

Retreiving Lost Data

Jan 29, 2005

I Somehow restored the latest database onto the test database. Can I now retrieve the lost data. If Yes , How is it possible.

Akash

View 2 Replies View Related

Retreiving Duplicates From A Database

Apr 12, 2008

How would I get three fields out of the database if they are duplicates?

I got it to work with one field but can't get it to work with the other two fields (BIC and PaymentAmt)

SELECT Claim,
COUNT(Claim) AS NumOccurrences
FROM Info
GROUP BY Claim
HAVING ( COUNT(claim) > 1 )

View 4 Replies View Related

Help In Retreiving Multiple HeadCount

Sep 28, 2007

Hi all
I have the following table schema and their details as follows:

Code Block
CREATE TABLE #Tbl_Project
(
ID varchar(40),
Name varchar(50)
)
GO
CREATE TABLE #Tbl_User_Project
(
ID int,
ProjectID varchar(40),
Role varchar(20)
)
GO
INSERT INTO #Tbl_Project VALUES('PRJ1','Project 1')
INSERT INTO #Tbl_Project VALUES('PRJ2','Project 2')
INSERT INTO #Tbl_Project VALUES('PRJ3','Project 3')
GO
INSERT INTO #Tbl_User_Project VALUES(1,'PRJ1','PM')
INSERT INTO #Tbl_User_Project VALUES(2,'PRJ1','TL')
INSERT INTO #Tbl_User_Project VALUES(3,'PRJ1','TM')
INSERT INTO #Tbl_User_Project VALUES(4,'PRJ2','PM')
INSERT INTO #Tbl_User_Project VALUES(5,'PRJ2','TL')
INSERT INTO #Tbl_User_Project VALUES(6,'PRJ3','PM')
INSERT INTO #Tbl_User_Project VALUES(7,'PRJ3','TL')
INSERT INTO #Tbl_User_Project VALUES(8,'PRJ3','TL')
GO
--Required Output
--ProjectID ProjectName PM:TL:TM Ratio HeadCount
--PRJ1 Project 1 1:1:1 3
--PRJ2 Project 2 1:0:1 2
--PRJ3 Project 3 1:0:2 3
DROP TABLE #Tbl_Project,#Tbl_User_Project

This is just sample of the values. The ratio obtained should be as follows:
Assuming for a Project 1 there are 2 - PM's , 6-TL's , 15 - TM's then the ratio should be 1:3:7.5 . So please help me in this regard.

View 5 Replies View Related

Retreiving Values From The Same Column

Sep 23, 2007

Hi every one,

I have a serious problem struggling with sql query the problem is i have a table below

Sno CarId carcharacterstics Charactersticvalue

1. Mercides01 Color Red
2. Mercides01 EngineType Desile
3. Mercides01 Speed/Hr 350KM
4. Maruthi01 Color Red
5. Maruthi01 EngineType Desile
6. Maruthi01 Speed/Hr 250KM
7. BMW Gears Automatic
8. BMW EngineType Desile
9. BMW Speed/Hr 250KM


Now i need to retrieve the carid whose color is red and Engine type is desile the query i have written is

select carid from carchar where ((characterstics='Color' and charactersticvalue=Red) and (characterstics='EngineType' and charactersticvalue=Desile))

But I am not able to retrieve the values. I can retrieve the values correctly if i mention only one characterstics and its value but i cant retrieve if i mention more than that

View 3 Replies View Related

Retreiving TranLog Filenames From Windows Directory

Jan 24, 2006

Due to circumstances beyond my control, I have to restore a full database backup from October 1, 2005 and then over 4400 transaction logs to bring a database up to Jan 01, 2006 on a development server (storage team lost all daily full backups from 10/02/2005 to 01/01/2006). Since the backups were not taken on this machine originally, the information I would pull from the sysjobhistory tables to get the transaction backup names is not available. Does anyone have a link or a script to pull the filenames from the backup directory into sql server so I can build a dynamic script to apply the tran logs instead of running all 4400+ of them by hand? Thanks, Tom

View 5 Replies View Related

Retreiving Connection Strings From Machine.config

May 12, 2006

Hi,

I'm trying to figure out how to retreive connection strings from the machine.config instead of storing the conn strings in a *.dtsconfig file. The reason that I'm trying to use the machine.config is for the simple reason that I'll be able to manage all of the conn strings in one location on each server. (Instead of mutliple dtsconfig files.) Is it posible to utilize the machine.config in SSIS? If so, could someone explain how, or better yet, have an example?

thanks for the help!

View 11 Replies View Related

Retreiving Tree-Structure Data From A Table Throug

Apr 7, 2007

Hi all
I have a Table with Following structure ( a Tree Structure )

PK Parent Level Code
--- -------- ------- ------
1 0 0 100
2 1 1 101
3 1 1 102
4 2 2 103
5 3 2 104
6 4 3 105

The same as following Tree as you can see
1__
| 2__
| 4__
| 6
| __
3__
5

I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that
Could any one help me.?


PK Parent Level Value First-Parent' Code 2nd-Parent's Code 3rd-Parent's Code
---- -------- ------ ------- --------------------- ---------------------- -----------------------
1 0 0 100 NULL NULL NULL
2 1 1 101 100 NULL NULL
3 1 1 102 100 NULL NULL
4 2 2 103 101 100 NULL
5 3 2 104 102 100 NULL
6 4 3 105 103 101 100

Any help greatly would be appreciated.
Kind Regards.

View 2 Replies View Related

Help In Creating Insert Statements For Retreiving Data From Database

Apr 14, 2008



Hi all,


Could someone tell me how to get the data from all tables of the database in the form of insert script? We are moving our databse from SQL Server 2000 to SQL Server 2005. The scripts for the Database, Tables, Views , Procedures, Functions have been obtained and it is only the data that is remaining. Some are small tables with 5 to 6 columns but there are some with 50 odd columns. A friend of mine told me about a procedure that returns a dataset with INSERT statements by passing a table name as a parameter. Such procedure would be of great help.

Thank you

View 5 Replies View Related

Optimizing Queries / Stored Procedure For Retreiving Millions Of Rows

Apr 22, 2006

I am having one querry regarding the same line.
In my stored procedure i am fetching the data from one table containing upto 5 to 6 million rows I made use of index in my database but then also I cant optimise my execution time of that sp.
Please help me out of this problem.

View 1 Replies View Related

Autoincrement

Sep 20, 2006

hi,

does anybody know how to create an identity column in a query without having to use the into clause?

for example, i want to do the following:

select *,identity(int,0,1) as myId
from myTable

but this is not allowed

thank you

View 10 Replies View Related

How To Autoincrement ID

Mar 26, 2007

i have a table for storing username and password.
the following are the fields in table
ID -int
UserName-nvarchar(50)
Password-nvarchar(50)

i dont want to have a separate field for id
i want to autincrement them
plz give me an idea
am using SQLServer Express
am new to this field so plz forgive me for this simple question

thanks
Shruthi

View 8 Replies View Related

Sql Autoincrement

Apr 4, 2006

in mysql the primary key has the possibility tu autoincrement herself

can it be done in MS SQL ??? how

View 9 Replies View Related

SQL Autoincrement Id STOP

Apr 10, 2008

 Hi  All        I am using SQL server Database in one of my table there is a column which is set to Identity=Yes i.e., The ID is increment by one on every insert and if  the insertion failed then the id generated goes off then in the next generation it uses new id ..........EXfirst insertion id=1 then in the second insertion if while adding data to other rows if i get some error  then the id 2 is not used and when i correct the error and insert it then id=3? can any one give me the solution for this and NextWhen i delete the datafrom the table see the ids are upto 20 and i delete all the records from the table after insertion of new  record the id will be 21plese help me in this  

View 3 Replies View Related

Autoincrement Manually

Jun 24, 2008

Hi All,

This may sound quite easy for you, however I am a newbie in SQL.

So I have an identity field, which I wish to increment automatically every time I do an insert. However the starting integer has to be the MAX value from another table.

So for example I am doing an insert in a #temp table
INSERT INTO #temp(name, surname)
SELECT name, surname from table1

Now the personId of the #temp table has to start from the MAX of table2
Ie SELECT MAX(personId) from table2

The SELECT MAX(personId) from table2 can also be NULL, ie the first time I am inserting, so I also have to cater for this scenario.

can anyone help?

Thanks

Johann

View 12 Replies View Related

AutoIncrement Column

Oct 26, 2005

How can I know that a column is auto incremented or not? What are the commands used to know this??
Thanks guys

View 1 Replies View Related

Function Used To Set Autoincrement Id To 0

Dec 12, 2007

Hi all, i am deleting rows of a table and reinserting new ones. This table has an auto increment primary key. what is the function used to set it to zero each time i delete the rows in order to rebegin counting from 1 when i refill the table. Thanks in advance...

View 7 Replies View Related

Autoincrement Column

Mar 17, 2008

Hi,
I have an existing table (without PK). I want to output my SELECT statement with an auto-increment on the 1st column like this:

# Lastname Firstname Address
1 Obama Clinton xxxxx
2 Hillary xxxx xxxxx
...
Can someone help me on this pls?
Appreciate it.
Thanks
joseph

View 6 Replies View Related

SQLexpress And Autoincrement

Apr 7, 2006

I've been searching for an answer a long time now... but nowhere I found a clear solution...

Is it possible to implement autoincrement with SQLexpress edition?

View 8 Replies View Related

Autoincrement With Alter Statement

Jul 3, 2000

looking for necessary syntax to alter table id to autoincrement adding identity statement, not sure on syntax for seed an increment, or if it is possible at all.

View 2 Replies View Related

Is There A Way To Define A Numeric Key As Autoincrement?

Sep 14, 1998

I noticed in SQL ODBC API reference that SQLGetTypeInfo would return true for AUTO_INCREMENT if a smallint field is defined as autoincrement. Is there a simple way to set a field autoincrement thru the SQL Server`s front end?

T.G.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved