.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

join two identity columns

Posted By:      Posted Date: October 17, 2010    Points: 0   Category :ASP.Net

I have a book table and a author table which contains book id for the book tbl and author id for the author tbl.
Then I create another table which is Book_Author and it must have the book id and author id join together. How to do this?

I have a book table and a author table which contains book id for the book tbl and author id for the author tbl.

Then I create another table which is Book_Author and it must have the book id and author id join together. How to do this?

View Complete Post

More Related Resource Links

Combining databases with Identity columns

(Using SQL Server 2005)  I have only a basic knowledge of SQL Server, but need to roll up 5 SQL databases into one database (I guess this is known as "replication"?).  These database are of similar structure and make use of an identity (or autonumber) column.  These id's are also referenced in numerous child tables.  To avoid collisions, this is my plan:   1.  turn off the identity feature of all the columns (but still keep condition of uniqueness) 2.  For each database, assign a unique range of identities (ie, db1 gets 1-500, db2 gets 501-1000, etc) and then renumber these columns accordingly.  The new id's should cascade to the child tables via the table relationships. 3.  Replicate or copy the data of all 5 databases into the new one.  There should be no collisions. 4.  Turn back on the identity feature of the appropriate columns, hopefully everything works as before. After this is done we hopefully can get rid of the 5 original databases and proceed with just one. Should this work and/or is there a better way to do this?  

Mark columns as Identity in multi-table views?

Hello! When you create a view of one table (i.e. SELECT * FROM Table), it marks the table's identity column as an identity col in the view as well. I have a few that selects columns from several nested tables, but they are all related in a main identity column I select as well, but SQL Server doesn't mark this column as identity, is there a way to do it?Shimmy


TABLE 1SELECT I.STUNA    ,S.SNO         ,SUM(B.AMT * Y.YEAR) AS AMT  FROM STUDENT S  LEFT JOIN INFO I ON S.SNO = I.SNO  LEFT JOIN BONUS B ON S.SNO = B.SSNO  LEFT JOIN YEAR  Y ON S.SNO = Y.SSNO  STUNA       SNO      AMTJOHN         A        10LISA         B        20ALLEN        C       100TABLE 2SNO   AMTA     1B     2C     3D     5I WANT TO HAVE RESULT LIKE THIS (TABLE1 + TABLE2)STUNA       SNO      AMTJOHN         A        11LISA         B        22ALLEN        C       103EDDIE        D         5I TRIED USE THIS QEURY SELECT I.STUNA    ,S.SNO         ,SUM(B

Why are Identity columns automatically marked as "NOT FOR REPLICATION" for non-updateable Transactio

Hi, Replication has been a blind stop for me. I've always tried to avoid it. Anyway, I've got (non-updatable) transactional replication of data from a production database (SQL2008 R2) into a reporting database (SQL2005 SP3). There are a handful of tables with Identity columns. Looking at the Article table properties of the tables in question, I can see that "Identity Management" is set to Manual (and greyed so not changeable). Also the Identity columns of the source tables are now marked as NOT FOR REPLICATION. When I look at the replica tables, I can see that they also have an Identity on the Primary Key column. Looking at the "sp_MSins" procedures, I can see that the procedure does include the column with the Identity on it in the INSERT statement. So, having look at all this, I'm more confused that before. But, the number 1 vitally important thing for me is that the values in the Identity columns are "honoured" when replicated to the reporting database (i.e. as if I used the -E flag of BCP). And my first question is, is this the case? I've examined the data from a couple of tables, and currently it appears to be true, but I'm worried that this is just luck. Secondly, as the insert procedure includes the Identity column in the Insert (the the Identity is present in the replica), I'm assuming / guessing that the Distribution agen

Getting list of tables with Identity Columns



I'm new to SQL Server, could somebody help me with the query for

Getting the list of tables with indentity columns i.e to check the key info in the system tables along with column data types.

Thanks in Advance


Replcation -> Subscriber no Identity on Columns


Hi All,


My publisher database has identities set on columns.

We load the snapshot into the subscriber by dropping all the tables and creating them as new.

We are concerned with bringing over the identities on these columns may cause issues.  We would like to just copy the same values from the publisher to the subscriber.


Are we being overly concerned about something we do not need to?  Is it guaranteed to be the same value copied over, even if it is an identity column?

Please advise.

Thank you for any information.

Phil Lamey


Removing columns after Merge Join ?



I have the scenario (using a third party component) where certain columns must not be present as input.

I am doing a Full Outer - Merge Join.

This results in:



but now in some cases the LeftPK may be  null in other cases the RightPK may be null,

so i'm creating a derived column to check:

ISNULL(LeftPK) ? RightPK : LeftPK

and call this the TruePK

this all works however now i'm left with 3 columns

LeftPK; RightPK; TruePK

Is there some kind of transform (other than script task) that i can use to get rid of Left and Right PK columns before sending input to next third party component?

I.W Coetzer

Join related data by using content types and site columns?


Hi guys

Say for instance if I have process column and a sub - process column, and if I chose a process I want to only show the relevant sub - processes instead of all. I need to solve this by using content types and site columns. Any ideas, or guides on how I can acheive that?


Join two tables on multiple columns


SQL Server 2008 R1

I have been successfully joining 2 tables on multiple fields with TSQL like:

SELECT a.* FROM a JOIN b ON b.field1 = a.field2 AND b.field3 = a.field4

My questions is:

1. Is this the best way to do this?

2. Are there limitations on how many fields you can JOIN ON or the size of these fields?

Now the specifics.

This application is inserting property conveyances (sales) from a source file (CombList), possibly with field corrections (CombListChanges). There are over 500,000 records in the source file each with a matching correction record. The UPDATE (shown below) updates the SourceID of the inserted records (approximately 350,000). The Conveyance INSERT (not shown) and the Conveyance UPDATE (shown below) each took 3-5 minutes to run.

The second query is an INSERT matching the Conveyances with a Real Property record. As written it runs for more than 2 hours and expands the tempdb to 10s of Gigabytes in size. I have never had it successfully execute. However if I run just the SELECT portion and eliminate the deedamount (money) and deedtype (nvarchar(10)) JOINs it runs in about 30 seconds (and gives me slightly too many records). The SourcePage (nvarchar(100)) is made up of a concatenation of several fields and text.

3. Any idea on why this INSERT won't run in a reasonable time?

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Adding Multiple Columns and Rows in GridView without using a Database

This article describes on how to add multiple columns and rows in GridView without using a database. Basically the GridView will be populated with data based on the values entered in each TextBoxes on Button Click and retain the GridView data on post back.

add columns with textboxes in gridview using C#

'm working on a shopping cart in a website and I have my items(which have been added to the cart) in an arraylist. And using these values I've read the rest of the values from the db and and have populated on the gridview successfully.

Custom Editing Behavior for DataGridView TextBox Columns

For various reasons I'm using a DataGridView instead of a ListView, and while configuring the DataGridView to look like a ListView wasn't much of a problem, there's one thing that got on my nerves, which is the behavior of textbox cells in edit mode: It is much too easy to leave the edit mode accidentally, simply by pressing the cursor keys at the wrong time. For example when the text caret is positioned behind the last character of the textbox cell content, and you press the right arrow key

Sorting GridView Columns Manually

We all know that GridView columns can be sorted automatically when SQLDataSource is being used. But what if you are not using SqlDataSource to populate the GridView. Sorting the GridView manually is pretty straight forward task take a look at the code below

dynamically adding sql server columns based on number of files in fileuploader


I want to create a new table to sql database at runtime.  The column names would be "Name", "Date", "Event".  I would also like to have one more column name "Image" but the problem is that since the amount of images will vary, I want the application to count the amount of files in the Multiple fileuploader and then add a image column to the table for the respected amount of files. 

For example if i am uploading 3 files in the multiple fileuploader, the names should be "Name", "Date", "Event", "Image1", "image2", Image3"

I also would like that table name to be name dynamically from the textbox Name when the user enters the information.


I have the following code to make a new table, but I dont know how to name the fields at runtime like previously asked please help!!!


Dim objConn As New SqlConnection("Server=<servername>;uid=<userid>;pwd=<password>;database=master")


How to let user edit selected gridview columns, not entire row



I've got a gridview I would like to let the user edit a couple of columns.  Seems like everything I'm coming across so far makes the entire row editable.  

Hopefully the solution isn't too complicated.  Can someone point me in the right direction?


Pass ASP.NET membership identity object across domain?


I am trying to figure a way to SSO with ASP.NET membership and role model.

I can implement custom membership provider which consume web services hosting on a server.

With encryption of data. No problem.

But my question is, If I sign on a website with my provider, got my identity object on that application,

could I pass it to another website on another domain which use the same provider and by doing so, do not need to login again?

If this is possible, I am going to implement this solution. Please tell me what's your take on this.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend