Two Identity Cols In Table

Jul 14, 2006

Hi,

How can we use two identity column (with identity property) in table ?

GK


gk

View 5 Replies


ADVERTISEMENT

How To: Find Cols In A Table

Jan 12, 2002

I'd like to know if I can make one proc/cursor that I can pass only a table name and it will determine what cols exist in the table?

AND, what is the syntax for such a query?


I will be receiving data back where I will receive the accountID and only the deltas will have values all other columns will be null.

My proc will update an existing record, updating the specific col when an accountID exists, it will create a new record if the accountID does not exist.

I'd like to search all the cols getting their names and values when not null.

I can construct a proc for each table searching each col by known name.

However, I'd like to know if I can make one proc/cursor that I can pass only a table name and it will determine the cols?

All my tables start with cols AccountID and end with RecID with any number of cols inbetween.

TIA

JeffP...
cross posted to ms.pub.mssqlserver.programming

View 2 Replies View Related

How Can I Get Primary Key Col And Other Two Cols In The Same Table Become One To One Relationship.

Jun 12, 2006

For ex.

Table Match_List ( MatchID, UserID_A, UserID_B)

constraints like

MatchID primary key

UserID_A <> UserID_B

unqiue index (UserID_A,UserID_B)

but I wish to exclude duplicated rows like 1,1,2 & 2,2,1,

cause UserA to UserB, and UserB to UserA are the same thing.

How can I get this?

View 7 Replies View Related

Last GASP On Insert Row In Table With Identity Field, And Get New Identity Back ?

Jul 9, 2006

While I have learned a lot from this thread I am still basically confused about the issues involved.

.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.

To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.

1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.

2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!

3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?



TIA,



Barkingdog

View 10 Replies View Related

Insert Row In Table With Identity Field, And Get New Identity Back

Jun 30, 2006

I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).

What is the most direct way to do this in SSIS?



TIA,



barkingdog



P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?

View 12 Replies View Related

XML To Cols

Oct 5, 2006

I have a XML column in my source DB. Is there any way possible using SSIS to convert the XML to ordinaty cols (varchar) in the destination db.

I am using Sql 2k5.

thanks in advance

View 1 Replies View Related

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column

Jun 19, 2008

Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun

View 6 Replies View Related

Query From Two Cols !?

Jun 27, 2007

my table

id; part_name ; part_desc
01; xpto ; descr
02; qwerty ; azerty
03; azerty ; descr xpto

I want to search from two columns
something similar to :

SELECT * FROM tools WHERE (part_name LIKE @parameter_search or part_desc LIKE @parameter_search);

If i search for "xpto" only the first line would show.

How can i solve/go around this?

View 4 Replies View Related

Distinct Cols Of More Than One

Aug 30, 2005

Hi

There is a table which has repeated values in more than one col.
I want to view all the distinct values for both the cols

for eg

col1 col2 col3
A B D
A B E
B C E
B C F


I want the output as

col1 col2
A B
B C


cheers
Vic

Vicky

View 8 Replies View Related

How To Simply Join Cols Together?

Jun 5, 2007

I have about 30 tabs with same struture.
I want to simple put column 2 in all 30 table in a new table without joining on anything. Is there a way to do that?
Thanks
JP

View 3 Replies View Related

Display Row Data As Cols

Jun 6, 2006

I have data in rows that I need to aggregate and display in a columnar fashion and I haven't been able to figure out how to do it. A simplified version of the data as it is stored in the table:

Station Month Day Reading

1 1 1 100

1 1 2 200

1 1 3 300

1 2 1 400

1 2 2 500

And I would like to create a query that returns:

Station Month Day 1 Day 2 Day 3

1 1 100 200 300

1 2 400 500

Any help you can provide or tips to steer me in the right direction are much appreciated.

Dianne Siebold

View 6 Replies View Related

Query Single Col Into Several Cols Result Set

Oct 24, 2004

Hello,

I have a table with 1 column containing numeric values, and I need to create a query which displays the count of values for each set of conditions. For example, if the table contains the values 1,2,3,4,5 and 6, the output of the query should look like this:

L M H
-----------------------
3 2 1

(L are values below 4, M between 4 and 5, H is 6 and above)

Any suggestions?

TIA

View 1 Replies View Related

SQl Query... Multiple Cols To Single XML.

Oct 12, 2006

Hi I have a table which has couple of name cols and 10 varchar columns. The requirement was to retain the first two cols but convert the 10 cols into a single XMl.

the table has this schema;


ManagerName varchar
UserName varchar
TextField1 Varchar
TextField2 Varchar
TextField3 Varchar
TextField4 Varchar
TextField5 Varchar
TextField6 Varchar
TextField7 Varchar
TextField8 Varchar
TextField9 Varchar
TextField10 Varchar

I wrote a query like;

SELECT

TPD.[NameId]
,CrtdUser.[UserId]
from [Olddb_DB] TPD
join

[NewDB]..[Manager]CrtdUser
on
Crtduser.managerName = TPD.ManagerName
join
[NewDB]..[Users]Users

on
Users.[loginDetail] = TPD.[UserName]


to get the cols other than the XML

/*********/

SELECT

XmlDat.[textField1],

XmlDat.[textField2],

XmlDat.[textField3],

XmlDat.[textField4],

XmlDat.[textField5],

XmlDat.[textField6],

XmlDat.[textField7],

XmlDat.[textField8],

XmlDat.[textField9],

XmlDat.[textField10],

XmlDat.[textField11],

XmlDat.[textField12]

from

[olddb_db]..[DETAIL_DEF] XmlDat,[NEWDB]..[Detail_Def]TPD

where TPD.Name = XmlDat.Name

for XML AUTO, ELEMENTS

and this query to convert all the varchar cols to a XML. Now the problem is I am not sure of how to combine both these queries to Insert into the new table.

Can someone guide me in this

the destination table looks like

Name (Varchar)

UserId (int)

UserVal_XMl (XML)





Thanks in advance

View 3 Replies View Related

Joe Celko Nested Set Model: How To Compute The Lft And Rgt Cols

Jul 3, 2006

Hi,

With reference to http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427
I want the "sql stmt" which wud give the lft and rgt col values..

i am reading his book but cant understand :eek: where he explains
wat lft and rgt cols are..

"The root is always (lft,rgt) (1, 2*(Select count(*)from table) and leaft nodes are (lft+1=rgt)" :S

View 3 Replies View Related

How To Widen Subtotal Col Without Widening Data Cols?

Jul 28, 2007


Hello ,

I need to make the automatic subtotal column in most right position in a matrix wider than the data columns , i faild so far and discovered that its width is proportional to the data column width , its logic for the total column to be wider since its value is sum(all left data columns)

any way around this ?

thanks
Bassam

View 1 Replies View Related

Finding Mismatched Rows Between Identical Tables Based On 2 Or More Cols

Jun 8, 2007

CREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)

View 6 Replies View Related

Transact SQL :: How To Alter Existing Table Column As Identity Without Dropping Table

Nov 20, 2013

I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.

create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)

View 7 Replies View Related

SQL Server 2012 :: Insert Into Table With Identity Columns From Another Table

Dec 23, 2013

I just created a new table with over 100 Columns and I need to populated just the first 2 columns.

The first columns to populate is an identify column that is the primary key. The second column is a foreign_key to an other column and I am trying to populate this columns with all the values from the foreign_key value. This is what I am trying to do.

column1 = ID
column2= P_CLIENT_D

SET IDENTITY_INSERT PIM1 ON

INSERT INTO PIM1 (P_CLIENT_ID)
SELECT
Client.ID
FROMP_Client

So I am trying to insert both an identity values and a value from an other table while leaving the other columns blank. How do I go about doing this.

View 1 Replies View Related

ExecuteScalar --&> How To Get The OrderID(Identity) From A Table To Another Table ??

Mar 23, 2006

I am new to asp.net and studying on book.. currently i am stuck with a problem which not understand what is it !! Can anyone help me ?? I trying a shopping cart "Check Out" method, and when i am done the process.. My order_lines Table can update the OrderID which just generated !! What wrong with the statement ??  
 
Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick        ' Insert the order and order lines into the database        Dim conn As SqlConnection = Nothing        Dim trans As SqlTransaction = Nothing        Dim cmd As SqlCommand
        Try            conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)            conn.Open()            trans = conn.BeginTransaction            cmd = New SqlCommand()            cmd.Connection = conn            cmd.Transaction = trans
            ' set the order details            cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@MemberName, @OrderDate, @Name, @Address, @City,@State, @PostCode, @Country, @Total)"            cmd.Parameters.Add("@MemberName", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@OrderDate", Data.SqlDbType.DateTime)            cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar, 255)            cmd.Parameters.Add("@City", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@State", SqlDbType.VarChar, 50)            cmd.Parameters.Add("@PostCode", Data.SqlDbType.VarChar, 15)            cmd.Parameters.Add("@Country", Data.SqlDbType.VarChar, 50)            cmd.Parameters.Add("@Total", Data.SqlDbType.Money)
            cmd.Parameters("@MemberName").Value = User.Identity.Name            cmd.Parameters("@OrderDate").Value = DateTime.Now()            cmd.Parameters("@Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text            cmd.Parameters("@Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text            cmd.Parameters("@City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text            cmd.Parameters("@State").Value = CType(Wizard1.FindControl("txtState"), TextBox).Text            cmd.Parameters("@PostCode").Value = CType(Wizard1.FindControl("txtPostCode"), TextBox).Text            cmd.Parameters("@Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text            cmd.Parameters("@Total").Value = Profile.Basket.Total
            Dim OrderID As Integer            OrderID = Convert.ToInt32(cmd.ExecuteScalar())  <-- Is it wrong or need to add wat ?            ' change the query and parameters for the order lines            cmd.CommandText = "INSERT INTO OrderLines(OrderID, ProductID,Quantity, Price) VALUES (@OrderID, @ProductID, @Quantity, @Price)"            cmd.Parameters.Clear()            cmd.Parameters.Add("@OrderID", Data.SqlDbType.Int)            cmd.Parameters.Add("@ProductID", Data.SqlDbType.Int)            cmd.Parameters.Add("@Quantity", Data.SqlDbType.Int)            cmd.Parameters.Add("@Price", Data.SqlDbType.Money)            cmd.Parameters("@OrderID").Value = OrderID
            For Each item As CartItem In Profile.Basket.Items                cmd.Parameters("@ProductID").Value = item.ProductID                cmd.Parameters("@Quantity").Value = item.Quantity                cmd.Parameters("@Price").Value = item.UnitPrice                cmd.ExecuteNonQuery()            Next            ' commit the transaction            trans.Commit()        Catch SqlEx As SqlException            ' some form of error - rollback the transaction            ' and rethrow the exception            If trans IsNot Nothing Then                trans.Rollback()            End If            ' Log the exception            Throw
        Finally            If conn IsNot Nothing Then                conn.Close()            End If        End Try        ' we will only reach here if the order has been created successfully        ' so clear the cart        Profile.Basket.Items.Clear()    End Sub

View 4 Replies View Related

GET IDENTITY FROM TABLE

Mar 16, 2006

How do i get identity from stored procedure? I CANNOT GET THE ID I CANNOT RETURN ANYTHING AT ALL! WHY?

This is last three lines of stored procedure

SELECT ID = SCOPE_IDENTITY()
RETURN
GO

I get ABSOLUTELY nothing I can't get anything to come back from by stored procedure to my ASP page. I have a "RETURN_VALUE" parameter, that's not the problem. There's something really stupid about this stored procedure that since I've inserted records into a table then from that point on I cannot return ANYTHING! WHYYYYYY???

Sorry, I am SO VERY frustrated. Can anyone help me?

mellamokb

View 3 Replies View Related

The Identity Of My Table

Mar 23, 2008

Hello,
I created a table with the identity column "ID". It will increase by 1 when I insert a row to the table.
However, when I wanted to publish my database with my application, I deleted all the rows in my table. At this time, I wanted the identity to begin with 1 again. Can I do it in sql server management studio or is there other ways to do it?
Thanks.

View 4 Replies View Related

IDENTITY ON CRETE TABLE HELP PLEASE!!

Sep 10, 2004

Below is the snippet of code that matters. Currently Figure 1 works great. I need to make Figure 2 work. Notice the Identity Seed vaue I need changed. Any ideas?


FIGURE 1:
CREATE TABLE _SMDBA_.Tmp__CUSTOMER_
(
SEQUENCE int NOT NULL IDENTITY (1, 1),


Figure 2:
CREATE TABLE _SMDBA_.Tmp__CUSTOMER_
(
SEQUENCE int NOT NULL IDENTITY ((SELECT NBRCOLUMN FROM TABLE WHERE BLAH = 'BLAH'), 1),


I need this desperately...
Thanks in advance.

Joe

View 2 Replies View Related

Inserting With DTS In IDENTITY Table

Oct 12, 2006

Hi

I need to insert values from a text-file to a table with a primary key as identity. In the text file I have no idea of the primary key values and i get "foreign key constraint violation" when trying to import null values into the column.

How can I solve the problem? With ordinary insert-statement there is no problem since the table generates identity- key values automatically. Is there a possibility to generate identity values with DTS-import?

Björn

View 1 Replies View Related

Insert Into A Table With Identity

Aug 31, 2006

I have 2 tables with same structure.only difference is, TableA has key colum which is declared as identity starting from 1.

I want to insert data from TableB into TableA where TableB.Key is all null with TableA.Key starting from 9000001

when i try to do that iam getting an error
"An explicit value for the identity column in table 'lEADlOAN' can only be specified when a column list is used and IDENTITY_INSERT is ON.
"
even when I turned IDENTITY_INSERT ON for TableA

View 4 Replies View Related

How To BCP IN To Table With IDENTITY Column

Sep 8, 2005

I have fixed length records (167 bytes) in a .txt file and want to loadthis data exactly as is into a staging table where I hope to be able tolater get at selected columns using the SUBSTRING function. Here is mytarget table:CREATE TABLE JJA_BCP_NHO_DAT (RecID int IDENTITY(1,1) NOT NULL, Data VARCHAR(167) NOT NULL)Here is my bcp command:bcp DevMDW.dbo.JJA_BCP_NHO_DAT inN:RawData_MDSPurchaseUnzipArea2005_08_Aug_0149 7407.txt -f bcp.fmtHere is the .fmt file:8.011 SQLCHAR 0 167 "" 2Data SQL_Latin1_General_CP1_CI_ASHere are the first 3 lines of data in the .txt file (note how 1st 2bytes are blank; last 2 bytes of each record are 78, 79, 80):02160168C013CMA20050819 328UUU AGAWAM 36422005072901001 BERKSHIREBANK 25013 7801940155C001 MA20050805 254UUU AGAWAM 28072005071801001 WEBSTERBANK 25013 7902350188C014CMA20050729 067UUU AGAWAM 24812005070701001 FIRSTPIONEER FARM CR 25013 80The bcp command runs OK but the output is "shifted" in the columncalled DATA in the table. The IDENTITY column looks good in all rows,but only the first row is OK in the DATA column. Starting in the 2ndrow, all bytes are truncated or shifted by what appears to be 2characters. This is hard to show with pasting results but I ran thisquery:SET ROWCOUNT 10SELECT RecID, SUBSTRING(Data,1,10) as FirstTen, SUBSTRING(Data,158,10) as LastTen, DATALENGTH(DATA) AS LEN_Data FROM JJA_BCP_NHO_DAT1 02160168 25013 781672 019401 25013 167379 0235CR 250116743 80 01 CO 251675013 80 CORP 167625013 80 REG SYS 1677 25013 98ANK 1678 25013 VGS BK 1679 2501 21 MTG CO16710RP 25RYWIDE HOM167Every line in the .txt file ends after column 167 with x'0d0a'. I'vetried a field terminator of
and that produces this error:SQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encounteredin BCP data-fileThanks in advance for some help on this.

View 2 Replies View Related

Table Identity After Updated

Oct 1, 2007



Hello all,

I fixed a after update trigger with sql server 2005 and I need to return the last identity updated in the table to insert it into another table. How can i get the last Identity updated in a table?

Please help ..
Sms

View 11 Replies View Related

Identity Seed Reset In SQL Table

Feb 16, 2004

I have a test database that is being moved to the production server. Currently in one of the tables I have an identity seed for each record. Is there a way to reset it back to zero. I have deleted all my records but it still doesnt work, and I dont want to create a new table.

Thanks

View 5 Replies View Related

Identity Column In Temp Table

Jun 19, 2005

Hi,

I am trying to create a temp table with an identity column.  Here is the code that I am using...

SELECT UserId, IDENTITY(int, 1, 1) AS colId
INTO #User
FROM MyUserTable
WHERE UserId = 1

I am getting an error though.

Server: Msg 8108, Level 16, State 1, Line 9
Cannot add identity column, using the SELECT INTO statement, to table
'#User', which already has column 'UserId' that inherits the identity
property.

Is there any way to work around this? 

Thanks for your help.

View 1 Replies View Related

An Explicit Value For The Identity Column In Table

Sep 15, 2005

for some unknow reasons.. my store proc stop working.. and i got an error.. i have installaed the latest SP4 for SQL server 2000 and still have the problem !any ideas why ?? Message "An explicit value for the identity column in table 'LCMS_Modules' can only be specified when a column list is used and IDENTITY_INSERT is ON."CREATE procedure LCMS_Modules_Add
 @PageID int, @ModuleDefID int, @Panename nvarchar(32), @Title nvarchar(128), @Admins nvarchar(256)
as
insert into LCMS_Modulesvalues(@PageID, @ModuleDefID, 99, @Panename, @Title, '0;', @Admins, 0, '', 'Center', '', '', '', 1, 0)GO

View 1 Replies View Related

DTS Help - Identity Column In Target Table

Mar 26, 2001

Having an identity column in target table, how do I use SQL 7 DTS ?

In Import wizard, I chose 'ignore' in source, checked Enable Indenity Insert - DTS failed with a msg like - a column cannot have NULL...

In DTS package, had Fast load and Enable Indenity checked - no luck !

What would be the standard procs for this since this sounds like a very common
senario.

Appreciate your help very much !

-Ivan

View 1 Replies View Related

How To Fill Table With Many Of Identity Values?

May 17, 2004

I created table

create table t1
(
oid int identity(1, 1),
f tinyint
)

and need way to fill it as fast as possible with say 100000 sequential values. Field f may stay = 0 but oid should be raising from row to row. How can I do it in MSSQL 2000?

View 3 Replies View Related

INSERT Into Table With IDENTITY Column

Jul 31, 2001

I'm sure this is a common problem but I can't find any relevant info on this site

I have a table that I would like to insert values into. I want to take the values from a secondary table using a select statement.

e.g. a simplified version...


TABLE1
(
Id INTEGER IDENTITY,
UserName VARCHAR(50),
Description VARCHAR(50)
)

TABLE 2
(
UserName VARCHAR(50),
Description VARCHAR(50)
)

Runing the insert statement

INSERT Table1 (UserName, Description)
SELECT *
FROM TABLE2

results in the error
erver: Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'Id', table 'CDS_Live.dbo.Table1'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I was under the impression that an identity column would be automatically inserted by SQL server. Now I know I could write a piece of anonymous Transact SQL which declares a cursor by selecting all rows from table 2 and inserting rows into table 1 on a row by row basis, but is there any way I could do the Insert with a single INSERT statement?

thanks

neill

View 1 Replies View Related

How To Add Identity Property To Existing Table?

Mar 2, 2004

Has anybody ever tried to do this. I can't figure it out. All I want to do is take an existing table that already has values in the column that I want to change and add the identity property to yes and set the identity seed and increment to a specific number. I know you can do it in the CREATE TABLE statement but is there a way to use the ALTER TABLE command?

View 4 Replies View Related







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