How to set douplicate id in sql server 2005?

Hi There,

Some one please help me to achieve this task.

I have task to join 2 tables and insert values.The table which i am inserting values has typeid column which is primary key column.I supposed to insert values except this column(TypeId).When i m trying insert values its throw an error saying

Error: Cannot insert the value NULL into column column does not allow nulls. INSERT fails.

Please let me know ther is a way to set duplicate id for this rows?

Thanks in advance.

Don't select the typeID column in your OLE DB destination.|||I don't understand; how is that a column is a PK but you don't want to insert any value on it? a PK has to have a value, right?|||

Rafael Salas wrote:

I don't understand; how is that a column is a PK but you don't want to insert any value on it? a PK has to have a value, right?

It could either be an identity, or he needs to assign a value to it.|||

Hi ,

Yes i want to assign a value automatically for the TypeId for inserting rows.

|||See if this helps you. Take a look at my post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211015&SiteID=1|||

Hi Phil,

Thanks for response.I have gone thru your post.Is there any tutorial or sample available for this?

|||

Tamizhan wrote:

Hi Phil,

Thanks for response.I have gone thru your post.Is there any tutorial or sample available for this?

I wrote one just for you... http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/
Also, a more simplified version can be found at SQLIS.com. It does not start where the table left off. (It assumes starting at 1 always). http://www.sqlis.com/37.aspx
|||

Hi Phil,

I have gone thru your articles i got the ideas to do. But i am facing problem while using theis scripts.

i have no.of sql statements i have droped no.of execute sql tasks for each statements.Please find the below script,

select MaxKey = case
when TypeId is null then 0
else TypeId
end
from tblType

__

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Private NextKey As Int32 = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaximumKey As Int32 = Me.Variables.MaxKey ' Grab value of MaxKey which was passed in

' NextKey will always be zero when we start the package.

' This will set up the counter accordingly

If (NextKey = 0) Then

' Use MaximumKey +1 here because we already have data

' and we need to start with the next available key

NextKey = MaximumKey + 1

Else

' Use NextKey +1 here because we are now relying on

' our counter within this script task.

NextKey = NextKey + 1

End If

Row.TypeId = NextKey ' Assign NextKey to our ClientKey field on our data row

End Sub

End Class

I followe all your steps as mentioned.Please Advice me.

|||Does your query return more than one row? Notice that you aren't selecting the max(typeid) from the table, tblType.

select MaxKey = case
when TypeId is null then 0
else TypeId
end
from tblType|||

Hi Phil,

Yes you are right.It returns more than one row.Please advice how to achieve this?

Please correct the script if there is a problem.

Many thanks in advance.

|||

Tamizhan wrote:

Hi Phil,

Yes you are right.It returns more than one row.Please advice how to achieve this?

Please correct the script if there is a problem.

Many thanks in advance.

Change your query to select the max(typeid) from the table. Your query isn't the same as mine.