how to insert records where PK is being violated on some records?

Hello,
I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and
tbl2.ID is a PK. The problem is that tbl2 already contains some of the
records from tbl1 whcih is causing a PK violation. what tsql statement can
perform the insert without violating PK?
Insert Into tbl2(ID, fld2, fld3)
Select ID, fld2, fld3 from tbl1
where tbl1.ID Not In (Select ID from tbl2)
Any suggestions appreciated.
Thanks,
RichBegin by writing a query that only selects those rows from [tbl1] that do
not exist in [tbl2]. This can be done using a LEFT JOIN on ID. Once done,
you may then insert this result into [tbl2]. This may perform better than
the NOT IN or NOT EXISTS method.
Insert Into
tbl2 (ID, fld2, fld3)
Select
T1.ID,
T1.fld2,
T1.fld3
from tbl1
left join tbl2 as T2
on T2.ID = T1.ID
where
T2.ID is null
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:05766B06-11C8-45D1-BC2E-D1CD0C30576F@.microsoft.com...
> Hello,
> I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and
> tbl2.ID is a PK. The problem is that tbl2 already contains some of the
> records from tbl1 whcih is causing a PK violation. what tsql statement
> can
> perform the insert without violating PK?
> Insert Into tbl2(ID, fld2, fld3)
> Select ID, fld2, fld3 from tbl1
> where tbl1.ID Not In (Select ID from tbl2)
> Any suggestions appreciated.
> Thanks,
> Rich|||Thank you all for your replies.
"Rich" wrote:

> Hello,
> I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and
> tbl2.ID is a PK. The problem is that tbl2 already contains some of the
> records from tbl1 whcih is causing a PK violation. what tsql statement c
an
> perform the insert without violating PK?
> Insert Into tbl2(ID, fld2, fld3)
> Select ID, fld2, fld3 from tbl1
> where tbl1.ID Not In (Select ID from tbl2)
> Any suggestions appreciated.
> Thanks,
> Rich

how to insert records where PK is being violated on some recor

How about this
Insert Into tbl2(ID, fld1, fld2)
Select Id,fld1, fld2 from
tbl1 t1 join tbl3 t3 on t1.ID = t3.ID
Left Join ttbl2 t2 on t1.id = t2.id
where t2.id Is Null
Would this work also? I forgot that I left out another table.
"JT" wrote:

> Begin by writing a query that only selects those rows from [tbl1] that do
> not exist in [tbl2]. This can be done using a LEFT JOIN on ID. Once done,
> you may then insert this result into [tbl2]. This may perform better than
> the NOT IN or NOT EXISTS method.
> Insert Into
> tbl2 (ID, fld2, fld3)
> Select
> T1.ID,
> T1.fld2,
> T1.fld3
> from tbl1
> left join tbl2 as T2
> on T2.ID = T1.ID
> where
> T2.ID is null
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:05766B06-11C8-45D1-BC2E-D1CD0C30576F@.microsoft.com...
>
>It looks like it would.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:00FBAEDE-5940-48DA-806D-5EB176AD10D7@.microsoft.com...
> How about this
> Insert Into tbl2(ID, fld1, fld2)
> Select Id,fld1, fld2 from
> tbl1 t1 join tbl3 t3 on t1.ID = t3.ID
> Left Join ttbl2 t2 on t1.id = t2.id
> where t2.id Is Null
> Would this work also? I forgot that I left out another table.
> "JT" wrote:
>

How to insert records into two tables that references each other?

I have two tables , A1 and B1 . B1 has a reference key pointing to A1.ID which is a primary key. Now I want to INSERT a record into both table.

CREATE TABLE a1 (
T_ID INTEGER IDENTITY(1,1) PRIMARY KEY,
T_Test INTEGER NOT NULL,
);

CREATE TABLE B1 (
B_ID INTEGER IDENTITY(1,1) PRIMARY KEY,
T_SID INTEGER NOT NULL REFERENCES a1(T_ID),
);

-- A function to get the next primary key value

-- Get the next unique key from A1
CREATE FUNCTION [dbo].[getTicketNo]
(
)
RETURNS INT
AS
BEGIN
RETURN (SELECT Max(T_ID) from A1)
END

I can only insert a record into a table at a time. and I rely on dbo.getTicketNo() to get the lastest T_ID

I wonder how do you insert a new record into A1 and B1 and maintain referential integrity? You must call Insert twice to accomplish the job right?

What I want to insert a record into B1.SID is the primary key value (T_ID) that was created during "Insert into A1 values (0)", using dbo.getTicketNo()
I worry if I am going to insert a record into A1 first, then insert a record into B1 next, there is a chance the data wont be consistent; as I illustrated a situation below:

t A1 B1
0 (0)Insert
1 (1)Insert
2 (0)Insert
3 (1)Insert

dbo.getTicketNo() at t3 may return the wrong value if another process (1)insert is executed, that will change the primary key number to 2 which is incorrect

Can I lock up A1 and then unlock A1 after I finished working with B1?

If so, what would happen to the application if it tries to access into A1 during it is locked?

If you ask me this not a good approach to get the identity value.
You can use the following objects to get the last inserted identity value.

@.@.Identity or Scope_Identity()

1.Insert the data on Master table (A1)
2.Then get the last inserted value from those system functions or global varibale
3.Use the values on Child table (B1)

Example,

Insert into A1(T_Test) values (@.T_Test);
Insert into B1(T_SID) values (@.@.Identity)
--or
Insert into B1(T_SID) values (Scope_Identity())

NOTE: Don't use MAX function to get the last inserted identity value. It may be
Inserted by other concurrent users.

|||

Thank you ManiD

From your code, is there any chance that another INSERT query is executed by other concurrent users after the 1st Insert and before the 2nd Insert in your case?

time 0:00:00 (User 0) Insert into A1(T_Test) values (@.T_Test);
time 0:00:20 (User 1) Insert into A1(T_Test) values (@.T_Test);
time 0:00:21 (User 0) Insert into B1(T_SID) values (@.@.Identity)

If it is possible, what is the best approach to ensure that the second Insert into B1 is consistent to the master A1?

Thanks for your precious time

|||

In this case,

@.@.Identity hold the User1 Identity data..

So it always good idea to use SCOPE_IDENTITY() instead of using @.@.Identity..

SCOPE_IDENTITY always hold the current user's identity data...

|||

Ok,,, thank you ManiD

Let me do some research on SCOPE_Indentity() ...

Thank you for your kind replies

|||

When I google it, found some people mispelled SCOPE_IDENTITY() to SCOPE_INDENTITY() .....

Here is the MSDN for SCOPE_IDENTITY() if anyone got the similar issues can have a look

http://msdn2.microsoft.com/en-us/library/ms190315.aspx

How to insert records into related tables?

Hi,
I have 3 tables:
Employees with the fields:idEmployee and employeeName
Roles with the fields:idRole and roleName.
An employee can have one or many roles.
I created the table EmployeeRoles with the fields: id,idEmployee,idRole.
idEmployee and idRole are foreign keys.
I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.

How can I do this?
Thanks

This should be handled by your application. If you are using identity fields you can use the SCOPE_IDENTITY function to return the last value for the identity column.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Hi,
Can you please help me with the sintax?
Thanks|||To create a table, you can do something like:

CREATE TABLE Employee

(

EmployeeID INT NOT NULL

,EmployeeFirstName VARCHAR(15) NOT NULL

,EmployeeLastName VARCHAR(15) NOT NULL
)

CREATE TABLE Role

(

RoleID INT NOT NULL

,RoleName VARCHAR(15) NOT NULL

)

CREATE TABLE EmployeeRole

(

RoleID INT NOT NULL

,EmployeeID INT NOT NULL

)

INSERT Employee

(

EmployeeID

,EmployeeFirstName

,EmployeeLastName

)

VALUES

(

1

,'John'

,'Wayne'

)

INSERT Role

(

RoleID

,RoleName

)

VALUES

(

1

,'Rooster Cogburn'

)

INSERT EmployeeRole

(

RoleID

,EmployeeID

)

VALUES

(

1

,1

)

As WesleyB mentioned, you can take this a step further and make the ID columns INDENTITY columns and use the SCOPE_INDENTITY function to retrieve the value for the most recently inserted row. You can also add some declarative referential integrity if you wish. Check out Books Online for more information on this.

BTW - I'm writing this without access to a SQL Server to test the scripts so please forgive any typos.

HTH...

Joe

|||Ok,
Thanks.
I used scope_identity and it worked.|||

I addition you could have used OUTPUT to redirect the output to a resultset with the assigned IDENTITIES per row. This feature is SQL Server 2005 and above only.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to insert records into related tables?

Hi,
I have 3 tables:
Employees with the fields:idEmployee and employeeName
Roles with the fields:idRole and roleName.
An employee can have one or many roles.
I created the table EmployeeRoles with the fields: id,idEmployee,idRole.
idEmployee and idRole are foreign keys.
I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.

How can I do this?
Thanks

This should be handled by your application. If you are using identity fields you can use the SCOPE_IDENTITY function to return the last value for the identity column.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Hi,
Can you please help me with the sintax?
Thanks|||To create a table, you can do something like:

CREATE TABLE Employee

(

EmployeeID INT NOT NULL

,EmployeeFirstName VARCHAR(15) NOT NULL

,EmployeeLastName VARCHAR(15) NOT NULL
)

CREATE TABLE Role

(

RoleID INT NOT NULL

,RoleName VARCHAR(15) NOT NULL

)

CREATE TABLE EmployeeRole

(

RoleID INT NOT NULL

,EmployeeID INT NOT NULL

)

INSERT Employee

(

EmployeeID

,EmployeeFirstName

,EmployeeLastName

)

VALUES

(

1

,'John'

,'Wayne'

)

INSERT Role

(

RoleID

,RoleName

)

VALUES

(

1

,'Rooster Cogburn'

)

INSERT EmployeeRole

(

RoleID

,EmployeeID

)

VALUES

(

1

,1

)

As WesleyB mentioned, you can take this a step further and make the ID columns INDENTITY columns and use the SCOPE_INDENTITY function to retrieve the value for the most recently inserted row. You can also add some declarative referential integrity if you wish. Check out Books Online for more information on this.

BTW - I'm writing this without access to a SQL Server to test the scripts so please forgive any typos.

HTH...

Joe

|||Ok,
Thanks.
I used scope_identity and it worked.|||

I addition you could have used OUTPUT to redirect the output to a resultset with the assigned IDENTITIES per row. This feature is SQL Server 2005 and above only.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

how to insert records from tiggers at batch update process

I want to split the records from table1 and insert the splited records to table2. The trigger is working fine when i am trying to update single record. but the trigger is not working properly at the time of batch update. At the time of batch update it is split the last record from the query and insert that into table2 instead of inserting all the records.

Pls help me........I suspect you wrote your trigger to work with one record at a time. I say this because of your comment of the trigger working for a one record update and the LAST record in a batch. Take a look at your trigger and ask yourself "What happens if I have three records to deal with in either the inserted OR deleted temp tables?"

Post your trigger and maybe we can offer some suggestions.|||Hi

This is the trigger i have used to split the records for various conditions from table1 and insert the splitted records into another table table2

The Problem i'm facing is if it is single update(if only one row is affected) the trigger is working fine,
but not for batch update.

if you are unable to follow this trigger pls give your own batch update example, we will try to implement that.

pls help me.

CREATE trigger udt_Break_Split
ON Hours_tmp
/*
this trigger is used to do the break split in the table hours_tmp
and insert the records into the table selnmuamid

*/

AFTER UPDATE

AS
Declare @.Id int
Declare @.Res_id nvarchar(8)
Declare @.DTime smalldatetime
Declare @.StartTime char(5)
Declare @.EndTime char(5)
Declare @.Pauze char(5)
Declare @.Hourworked nvarchar(5)
Declare @.Status char(1)
--Declare @.PPayroll bit
Declare @.Project nvarchar(10)
Declare @.Naam char(50)
Declare @.Type char(5)
Declare @.Hryear nvarchar(4)
Declare @.Totmin char(5)
Declare @.Endtimebr char(5)
Declare @.Stime char(5)
Declare @.Start char(5)
Declare @.Processed char(1)
Declare @.del_DTime smalldatetime
Declare @.del_StartTime char(5)
Declare @.del_EndTime char(5)
Declare @.Del_id int
Declare @.Totrows int

--update hours_tmp set hours_tmp.processed_payroll =1 from hours_tmp , inserted where hours_tmp.id = inserted.id

Select @.Id=Id,
@.Res_id=Res_id,
@.DTime=[Datetime],
@.Start=Starttime,
@.EndTime=Endtime,
@.Pauze=Pauze,
@.Hourworked=Hoursworked,
@.Status=Status,
@.Project=Project,
@.Naam=Naam,
@.Type=Type,
@.Hryear=Hryear,
@.Processed=Processed
From inserted -- i ,Hours_tmp h where i.[Id] = h.[id]

Select @.del_DTime=[DateTime],
@.del_Starttime=Starttime,
@.del_endTime=Endtime ,
@.del_id=id
From Deleted

Delete From Selnmuamid
Where Res_id=@.Res_id
and Datetime=@.del_DTime
and Starttime >=@.del_starttime
and Endtime <=@.del_endtime

-- This is To Get total hours (hourworked column in the table ) in minutes

Select @.Totmin=Sum(Convert(Integer,(Left(@.hourworked,Char index('.',@.hourworked)-1) *
60)) +Convert(Integer,(Right(@.hourworked,Len(@.hourworke d) -
Charindex('.',@.hourworked))))),@.Endtimebr=Sum(Conv ert(Integer,(Left(@.Endtime,Charindex(':',@.Endtime) -1) *
60)) + Convert(Integer,(Right(@.Endtime,Len(@.Endtime) -
Charindex(':',@.Endtime))))),@.Stime=Sum(Convert(Int eger,(Left(@.Start,Charindex(':',@.Start)-1) * 60)) +
Convert(Integer,(Right(@.Start,Len(@.Start) - Charindex(':',@.Start)))))

--Values Passed as Parameter to Stored procedure Break_Split

Exec Break_Split @.Id,@.Res_id,@.DTime,@.Start,@.EndTime,@.Pauze,
@.Hourworked,@.Status,@.Project,@.Naam,@.Type,
@.Hryear,@.Totmin,@.Endtimebr,@.STime,@.Processed|||basically I just wrapped your code in a while loop to step through each record in the inserted temp table. Then I moveded the delete Selnmuamid to the end.

This has NOT been tested!

Code:
------------------------------

CREATE trigger udt_Break_Split
ON Hours_tmp
/*
this trigger is used to do the break split in the table hours_tmp
and insert the records into the table selnmuamid

*/

AFTER UPDATE

AS

-- ----------------
-- One Declare is more efficient than multipule declares
-- ----------------
Declare @.Id int
, @.Res_id nvarchar(8)
, @.DTime smalldatetime
, @.StartTime char(5)
, @.EndTime char(5)
, @.Pauze char(5)
, @.Hourworked nvarchar(5)
, @.Status char(1)
-- , @.PPayroll bit
, @.Project nvarchar(10)
, @.Naam char(50)
, @.Type char(5)
, @.Hryear nvarchar(4)
, @.Totmin char(5)
, @.Endtimebr char(5)
, @.Stime char(5)
, @.Start char(5)
, @.Processed char(1)
, @.del_DTime smalldatetime
, @.del_StartTime char(5)
, @.del_EndTime char(5)
, @.Del_id int
, @.Totrows int

--update hours_tmp set hours_tmp.processed_payroll =1 from hours_tmp , inserted where hours_tmp.id = inserted.id

select @.Id = min(ID) from inserted
while (@.Id is not null) begin
Select @.Res_id = Res_id
, @.DTime = [Datetime]
, @.Start = Starttime
, @.EndTime = Endtime
, @.Pauze = Pauze
, @.Hourworked = Hoursworked
, @.Status = Status
, @.Project = Project
, @.Naam = Naam
, @.Type = Type
, @.Hryear = Hryear
, @.Processed = Processed
-- ---------------------------------------------------
-- Hoursworked, Endtime and StartTime need to be converted to minutes
-- ---------------------------------------------------
, @.Totmin = Convert(Integer,(Left(Hoursworked,Charindex('.',Ho ursworked)-1) * 60)) + Convert(Integer,(Right(Hoursworked,Len(Hoursworked ) - Charindex('. ',Hoursworked))))
, @.Endtimebr = Convert(Integer,(Left(Endtime, Charindex(':',Endtime)-1) * 60)) + Convert(Integer,(Right(Endtime, Len(Endtime) - Charindex(':',Endtime))))
, @.Stime = Convert(Integer,(Left(Starttime, Charindex(':',Starttime)-1) * 60)) + Convert(Integer,(Right(Starttime, Len(Starttime) - Charindex(':',Starttime)))))
From inserted i
where id = @.id


-- This is To Get total hours (hourworked column in the table ) in minutes
/*
Select @.Totmin = Sum(Convert(Integer,(Left(@.hourworked,Charindex('. ',@.hourworked)-1) * 60)) +
Convert(Integer,(Right(@.hourworked,Len(@.hourworked ) - Charindex('. ',@.hourworked)))))
, @.Endtimebr = Sum(Convert(Integer,(Left(@.Endtime,Charindex(':',@. Endtime)-1) * 60)) +
Convert(Integer,(Right(@.Endtime,Len(@.Endtime) - Charindex(':',@.Endtime)))))
, @.Stime = Sum(Convert(Integer,(Left(@.Start,Charindex(':',@.St art)-1) * 60)) +
Convert(Integer,(Right(@.Start,Len(@.Start) - Charindex(':',@.Start)))))
*/
-- Values Passed as Parameter to Stored procedure Break_Split

Exec Break_Split @.Id,@.Res_id,@.DTime,@.Start,@.EndTime,@.Pauze,@.Hourwor ked,@.Status,@.Project,@.Naam,@.Type,@.Hryear,@.Totmin,@. Endtimebr,@.STime,@.Processed

select @.Id = min(ID) from inserted where id > @.id
end

-- -------------------
-- The deleted temp table can be joined to the Selnmuamid table
-- to deleted the unwanted records. If you need the ID from the
-- inserted table then join the inserted table with the
-- deleted table and then join the Selnmuamid table.
-- This is a good example of how you can ditch the row-at-a-time
-- mentality and start using set theory for processing records.
-- -------------------
delete
from Selnmuamid s
join deleted d on s.Res_id = d.Res_id
where [s.Datetime] = d.del_DTime
and s.Starttime >= d.del_starttime
and s.Endtime <= d.del_endtime

/*
Select @.del_DTime = [DateTime]
, @.del_Starttime = Starttime
, @.del_endTime = Endtime
, @.del_id = [id]
From Deleted
where Id = @.Id

Delete
From Selnmuamid
Where Res_id = @.Res_id
and [Datetime] = @.del_DTime
and Starttime >= @.del_starttime
and Endtime <= @.del_endtime
*/

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

How to Insert or Update Records from Source to Destination using Oracle 8i DB

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

How to Insert One Row with Multiple time depding on Source Column value in SSIS Pacakge

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

how to insert multiple records into table

insert into table1 (colname) values (value1)

can only insert one record into the table. How to insert multiple records as value1, value2, value3... into a table?

We can of course use the above repeatedly, but if I don't know how many records (which is a variable), and I want to write a code which just take

value1, value2, value3 ...

from the clipboard, to paste as a input. How to insert those multiple records into table without split it. Thanks

What is the source of the data? from another table?

|||

If you are taking values from another table then you can insert multiple records.

Any ways i think it internally dosen't make much difference if you use multiple records or single record multiply internall it will fire that many insert statements only.

I find this intresting article guess it will help

http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

|||

U want place into the same table r other clarity is required

Insert into x select * from x

insert into x select * from y

If u want to copy the data of entire table use * other wise U have to specify column clause

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

I hope I'm getting your question right.

Are you looking for something like

insert into table1 ( colname )
select value1 union all
select value2 union all
.
.
.
select valueN

If you can specify your problem in details then we can help you better.

|||

Thank everyone for your input. What I want was taking the contents of multiple records delimited by delimiter from clipboard as a string, then insert into a table. I complished by writing a user function which takes a string, then output a table with multiple records, it works as

insert into table1 (ID)

select values from fn_StringToTable(@.myString, @.delimiter) -- here @.myString='00a1,00a2,00a3,...'; @.delimiter=','

output table1 got the IDs from the clipboard which has'00a1,00a2,00a3,...

Thanks

How to Insert Multiple Records into sql 2000 table at once ?

hello,
I am new to Slq 2000 Database,Now I create an asp.net application with sql 2000,
in my database I have two 2 table lets' say "OrderHead" and "OrderDetail",they look like this:
OrderHead orderdetail
--order no --orderno
--issuedate --itemname
--supplier --desccription
--amount --price
--Qty
Now I created a user-defined Collection class to storage order detail data in memory
and bind to a datagrid control.
I can transfer Collection data to xml file ,my problem as below :
There have multiple records data in my xml file,and I want to send the xml file as argument to a store procedure in sql 2000

anyone can give me some advise or some sample code ?

thanks in advanced!See links below:

1. http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
2. http://www.eggheadcafe.com/articles/20030627c.asp|||hi, thanks a lot,I will browse the web page you give me .

How to insert into a table with a function?

I want to add a record into a table, using a function.

This function needs to give back the number of records of a given table.

Function ImportResults

@.NumberOfRecords int

@.TableName varchar(60)

@.ImportDate datetime

@.Result bit

@.Result = 1

@.ImportDate =getdate()

--Open Table with tablenames

select TableName from Tbl_ImportTables

For each record in Tbl_ImportTables

@.TableName = Tbl_ImportTables.TableName

@.NumberOfRecords = select count(*) from @.TableName

-- add to Import Results

Insert into Tbl_Import_Results

(ImportDate

,TableName

,NumberOfRecords

)

VALUES

(

@.ImportDate

, @.TableName

, @.NumberOfRecords

)

-- Check 0 records (I only want to know if there is a table with 0 records)

IF @.NumberOfRecords=0 then @.Result=0

Next record

RETURN @.Result

End function

Can somebody help me with the right syntax (I am new to functions and sp's)?

Is this a scalar valued function?

Thanks,

Frans

DML operations (except local table variables) are not allowed in SQL Functions, you will have to use a stored procedure for that.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Yes, this is really a stored procedure.

As you are dynamically selecting the tablename you may want to look at the following topics in Books Online:

1) Cursors - to replace your For Each Loop
2) EXEC or sp_executesql - to dynamically execute a sql string built.

You may want to consider inserting the importdate and tablename first and then just updating the numberofrecords value in your cursor:

ie INSERT INTO Tbl_Import_Results (ImportDate, TableName) SELECT GETDATE(), TableName FROM tbl_ImportTables

HTH!

|||

OK,

I have started to built a basic function in which a sp is called.

ALTER FUNCTION [dbo].[fnImportResult]

RETURNS bit

AS

BEGIN

EXEC dbo.[TestInsert]

@.ControleDatum= '12-10-2007'

,@.BronDatabase = N'aaa'

,@.Tabelnaam = N'bbb'

,@.AantalRecords = 3333

RETURN 0

END

If I run this function I get the following message:

Only functions and extended stored procedures can be executed from within a function.

When only the EXEC part is run then no error message

What am I doing wrong?

Thanks again,

Frans

|||As the message indicates, you cannot call Stored Procedures from a UDF.

|||

OK, so no DML operation or calling a Stored Procedure are allowed in a user defined function. Get it now

Is it correct that a sp cannot return a result?

If so, how can I make it to work? I need to know if one of the records which has been inserted intoTbl_Import_Results has 0 records (for the right ImportDate).

Please advise how you would do this (headlines are fine).

Thanks,

Frans

|||

Stored Procedures can return a results set. They can also return values to indicate success or failure or some other integer value.

Check out OUTPUT parameters and Return Codes in Books Online.

Good Luck!

|||

Slowly I am getting there. The Fetch is working

Now syntax is the problem:

This works:

set @.NumberOfRecords = select count(*) from [1_Exact_DS].[dbo].artbst

But not this (Error converting data type varchar to bigint.)

declare @.TableName as varchar(110)

set @.TableName= '[1_Exact_DS].[dbo].artbst'

set @.NumberOfRecords = (select count(*) from @.TableName)

This is not allowed:

set @.NumberOfRecords = EXEC(select count(*) from @.TableName)

How can I take care that first the @.TableName is resolved and than the select string?

Thanks again,

Frans

|||

You need to dynamically build the sql string to use EXEC

EXEC('SELECT COUNT(*) FROM ' + @.TableName)

|||

Thanks for your quick response

This gives 'incorrect syntax' :

set @.NumberOfRecords = EXEC('select count(*) from ' + @.TableName)

The result of the EXEC should go into the variable @.NumberOfRecords. But how?

Thanks,

Frans

|||

In this case, you won't be able to use EXEC to set the result of a query to a variable. You can however use sp_executesql instead of EXEC:

Code Snippet

declare @.TableName varchar(110)
declare @.NumberOfRecords int
declare @.sql nvarchar(200)

set @.TableName = 'dbo.sysobjects'

set @.sql = 'SELECT @.out = COUNT(*) FROM ' + @.TableName

exec sp_executesql @.SQL, N'@.out INT OUTPUT', @.NumberOfRecords output

select @.NumberOfRecords

HTH!

|||

This works great!!!! Exactly what I wanted

I even start to understand the syntax now.....

Thanks a lot for your help.

Frans

How to insert into a table with a function?

I want to add a record into a table, using a function.

This function needs to give back the number of records of a given table.

Function ImportResults

@.NumberOfRecords int

@.TableName varchar(60)

@.ImportDate datetime

@.Result bit

@.Result = 1

@.ImportDate =getdate()

--Open Table with tablenames

select TableName from Tbl_ImportTables

For each record in Tbl_ImportTables

@.TableName = Tbl_ImportTables.TableName

@.NumberOfRecords = select count(*) from @.TableName

-- add to Import Results

Insert into Tbl_Import_Results

(ImportDate

,TableName

,NumberOfRecords

)

VALUES

(

@.ImportDate

, @.TableName

, @.NumberOfRecords

)

-- Check 0 records (I only want to know if there is a table with 0 records)

IF @.NumberOfRecords=0 then @.Result=0

Next record

RETURN @.Result

End function

Can somebody help me with the right syntax (I am new to functions and sp's)?

Is this a scalar valued function?

Thanks,

Frans

DML operations (except local table variables) are not allowed in SQL Functions, you will have to use a stored procedure for that.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Yes, this is really a stored procedure.

As you are dynamically selecting the tablename you may want to look at the following topics in Books Online:

1) Cursors - to replace your For Each Loop
2) EXEC or sp_executesql - to dynamically execute a sql string built.

You may want to consider inserting the importdate and tablename first and then just updating the numberofrecords value in your cursor:

ie INSERT INTO Tbl_Import_Results (ImportDate, TableName) SELECT GETDATE(), TableName FROM tbl_ImportTables

HTH!

|||

OK,

I have started to built a basic function in which a sp is called.

ALTER FUNCTION [dbo].[fnImportResult]

RETURNS bit

AS

BEGIN

EXEC dbo.[TestInsert]

@.ControleDatum= '12-10-2007'

,@.BronDatabase = N'aaa'

,@.Tabelnaam = N'bbb'

,@.AantalRecords = 3333

RETURN 0

END

If I run this function I get the following message:

Only functions and extended stored procedures can be executed from within a function.

When only the EXEC part is run then no error message

What am I doing wrong?

Thanks again,

Frans

|||As the message indicates, you cannot call Stored Procedures from a UDF.

|||

OK, so no DML operation or calling a Stored Procedure are allowed in a user defined function. Get it now

Is it correct that a sp cannot return a result?

If so, how can I make it to work? I need to know if one of the records which has been inserted intoTbl_Import_Results has 0 records (for the right ImportDate).

Please advise how you would do this (headlines are fine).

Thanks,

Frans

|||

Stored Procedures can return a results set. They can also return values to indicate success or failure or some other integer value.

Check out OUTPUT parameters and Return Codes in Books Online.

Good Luck!

|||

Slowly I am getting there. The Fetch is working

Now syntax is the problem:

This works:

set @.NumberOfRecords = select count(*) from [1_Exact_DS].[dbo].artbst

But not this (Error converting data type varchar to bigint.)

declare @.TableName as varchar(110)

set @.TableName= '[1_Exact_DS].[dbo].artbst'

set @.NumberOfRecords = (select count(*) from @.TableName)

This is not allowed:

set @.NumberOfRecords = EXEC(select count(*) from @.TableName)

How can I take care that first the @.TableName is resolved and than the select string?

Thanks again,

Frans

|||

You need to dynamically build the sql string to use EXEC

EXEC('SELECT COUNT(*) FROM ' + @.TableName)

|||

Thanks for your quick response

This gives 'incorrect syntax' :

set @.NumberOfRecords = EXEC('select count(*) from ' + @.TableName)

The result of the EXEC should go into the variable @.NumberOfRecords. But how?

Thanks,

Frans

|||

In this case, you won't be able to use EXEC to set the result of a query to a variable. You can however use sp_executesql instead of EXEC:

Code Snippet

declare @.TableName varchar(110)
declare @.NumberOfRecords int
declare @.sql nvarchar(200)

set @.TableName = 'dbo.sysobjects'

set @.sql = 'SELECT @.out = COUNT(*) FROM ' + @.TableName

exec sp_executesql @.SQL, N'@.out INT OUTPUT', @.NumberOfRecords output

select @.NumberOfRecords

HTH!

|||

This works great!!!! Exactly what I wanted

I even start to understand the syntax now.....

Thanks a lot for your help.

Frans

sql

How to insert into a table with a function?

I want to add a record into a table, using a function.

This function needs to give back the number of records of a given table.

Function ImportResults

@.NumberOfRecords int

@.TableName varchar(60)

@.ImportDate datetime

@.Result bit

@.Result = 1

@.ImportDate =getdate()

--Open Table with tablenames

select TableName from Tbl_ImportTables

For each record in Tbl_ImportTables

@.TableName = Tbl_ImportTables.TableName

@.NumberOfRecords = select count(*) from @.TableName

-- add to Import Results

Insert into Tbl_Import_Results

(ImportDate

,TableName

,NumberOfRecords

)

VALUES

(

@.ImportDate

, @.TableName

, @.NumberOfRecords

)

-- Check 0 records (I only want to know if there is a table with 0 records)

IF @.NumberOfRecords=0 then @.Result=0

Next record

RETURN @.Result

End function

Can somebody help me with the right syntax (I am new to functions and sp's)?

Is this a scalar valued function?

Thanks,

Frans

DML operations (except local table variables) are not allowed in SQL Functions, you will have to use a stored procedure for that.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Yes, this is really a stored procedure.

As you are dynamically selecting the tablename you may want to look at the following topics in Books Online:

1) Cursors - to replace your For Each Loop
2) EXEC or sp_executesql - to dynamically execute a sql string built.

You may want to consider inserting the importdate and tablename first and then just updating the numberofrecords value in your cursor:

ie INSERT INTO Tbl_Import_Results (ImportDate, TableName) SELECT GETDATE(), TableName FROM tbl_ImportTables

HTH!

|||

OK,

I have started to built a basic function in which a sp is called.

ALTER FUNCTION [dbo].[fnImportResult]

RETURNS bit

AS

BEGIN

EXEC dbo.[TestInsert]

@.ControleDatum= '12-10-2007'

,@.BronDatabase = N'aaa'

,@.Tabelnaam = N'bbb'

,@.AantalRecords = 3333

RETURN 0

END

If I run this function I get the following message:

Only functions and extended stored procedures can be executed from within a function.

When only the EXEC part is run then no error message

What am I doing wrong?

Thanks again,

Frans

|||As the message indicates, you cannot call Stored Procedures from a UDF.

|||

OK, so no DML operation or calling a Stored Procedure are allowed in a user defined function. Get it now

Is it correct that a sp cannot return a result?

If so, how can I make it to work? I need to know if one of the records which has been inserted intoTbl_Import_Results has 0 records (for the right ImportDate).

Please advise how you would do this (headlines are fine).

Thanks,

Frans

|||

Stored Procedures can return a results set. They can also return values to indicate success or failure or some other integer value.

Check out OUTPUT parameters and Return Codes in Books Online.

Good Luck!

|||

Slowly I am getting there. The Fetch is working

Now syntax is the problem:

This works:

set @.NumberOfRecords = select count(*) from [1_Exact_DS].[dbo].artbst

But not this (Error converting data type varchar to bigint.)

declare @.TableName as varchar(110)

set @.TableName= '[1_Exact_DS].[dbo].artbst'

set @.NumberOfRecords = (select count(*) from @.TableName)

This is not allowed:

set @.NumberOfRecords = EXEC(select count(*) from @.TableName)

How can I take care that first the @.TableName is resolved and than the select string?

Thanks again,

Frans

|||

You need to dynamically build the sql string to use EXEC

EXEC('SELECT COUNT(*) FROM ' + @.TableName)

|||

Thanks for your quick response

This gives 'incorrect syntax' :

set @.NumberOfRecords = EXEC('select count(*) from ' + @.TableName)

The result of the EXEC should go into the variable @.NumberOfRecords. But how?

Thanks,

Frans

|||

In this case, you won't be able to use EXEC to set the result of a query to a variable. You can however use sp_executesql instead of EXEC:

Code Snippet

declare @.TableName varchar(110)
declare @.NumberOfRecords int
declare @.sql nvarchar(200)

set @.TableName = 'dbo.sysobjects'

set @.sql = 'SELECT @.out = COUNT(*) FROM ' + @.TableName

exec sp_executesql @.SQL, N'@.out INT OUTPUT', @.NumberOfRecords output

select @.NumberOfRecords

HTH!

|||

This works great!!!! Exactly what I wanted

I even start to understand the syntax now.....

Thanks a lot for your help.

Frans

How to insert a record in the middle of a DB

Well, how do you do it? I've not seen any report on this... so could it be next to impossible?

Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.

Here's an idea:

CREATE TABLE MyTable (id int identity(1,1), col1 varchar(16))
GO

INSERT INTO MyTable SELECT 'value 01'
INSERT INTO MyTable SELECT 'value 02'
INSERT INTO MyTable SELECT 'value 03'
INSERT INTO MyTable SELECT 'value 04'
INSERT INTO MyTable SELECT 'value 05'
INSERT INTO MyTable SELECT 'value 06'
INSERT INTO MyTable SELECT 'value 07'
INSERT INTO MyTable SELECT 'value 08'
INSERT INTO MyTable SELECT 'value 09'
INSERT INTO MyTable SELECT 'value 10'
GO

CREATE TABLE tmpMyTable (id int identity(1,1), col1 varchar(16))
GO

SET IDENTITY_INSERT tmpMyTable ON
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id, col1
FROM MyTable
WHERE id < 5

INSERT INTO tmpMyTable (id,col1) SELECT 5,'value between'
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id+1, col1
FROM MyTable
WHERE id > 4

SET IDENTITY_INSERT tmpMyTable OFF
GO

DROP TABLE MyTable
GO

EXEC sp_rename 'tmpMyTable', 'MyTable'
GO

SELECT * FROM MyTable

|||

Thanks... I'm not very good at SQL but I figure, you create a new table, add those values to it and them insert them back into the old table?

If I can renumber the ID, that would be fine. I deleted record #2 and when I leave record one (view), it still stops where record #2 was deleted. I have to click again to go to record 3. I don't know why it just doesn't jump to record #3, seeing that #2 is deleted.

|||

Take a look at your other question...

Renumbering ID fields is BAD... I really mean it. It can only be done via another table to temporary store the data. There should be no reason to renumber IDs anyway, and if there IS a reson, then the field should not ba an ID ;)

|||

...ooops! That would be "delete" the old table and make the new table the current table.

Okay, ok, this can solve my problem, how can I advance to the next record using code only? I programed my app to go by ID, so if ID #2 is deleted, nothing happens because #2 is deleted. I have to click until the program counter is up to a ID value that isn't deleted before any records show up.

If I say (not really my code, just for example purposes)

CurrRec=CurrRec+1

Select Data from Table1 WHERE ID = CurrRec

Read

While CurrRec is NullDB then

CurrRec=CurrRec+1

Read? 'I could use a read to get pass the deleted record ID

'however, there's a problem if CurrRec > total records

'because it's at the end of the DB

end While

I could put in a time out loop that exits if no record is found in x tries... but surely there must be a way to jump to the NEXT RECORD and a END OF RECORD and so on.

Any takers?

|||

well to move through your records you should do something like this...

Dim oreader As SqlClient.SqlDataReader

Dim oCmd As SqlClient.SqlCommand

oCmd.CommandText = "Select * from mytable"

oreader = oCmd.ExecuteReader

While oreader.Read

'dostuff

End While

|||

I do also need to... move.first, move.last, move.next, ect. With the currRec to move next, I add 1 to currRec, to move back, I subtract 1, to move first, currRec=1.

I'm now thinking I can using CTE functions, if only I can find the syntax for them. That way, I don't use the ID, just the record number.

Thanks.

|||

zdrae,

To select each record, one record at a time, you can use the Top keyword. Each time you get back a record, just save the ID in a variable so that you may use it to select the next record.

EXAMPLE:
Select Top 1 Data from Table1 Where ID > prevID Order by ID

The idea is to select the next record who has an ID greater than the current ID.

Also, just FYI, I think some people in these forums are confused by your use of the term "ID". It seems to me that you really only mean an identity column, but the other posters in this forum are right that you should not attempt to renumber an identity field.

You may be used to programming in a "line-by-line" oriented way, but SQL is really very different and powerful as a data manipulation language. After using it for a while, you'll love how simple and powerful SQL is compared to "line-by-line" data processing.

Hope this helps...

|||

Actually, I didn't like SQL one bit... until I figured out what should actually be in the connection string (I finally got it open); but still I wasn't able to get any data out until I found that I was missing a read statement.

It's on my notebook and the speed is terrible... but everything is slow on it anyway... even games. Thanks.

|||This works great going forward. Any ideas on going backwards? I could store previous IDs but that won't do any good once it goes off the reservation.|||

Instead of a Datareader you could use the Datareader to fill a Dataset or a Datatable.

Those 2 objects will store the data permanently untill you discard them. By doing so they will allow you to scroll foreward and backwards (Or accessing any record you retrieved)

But be aware that you allways should only fetch those records you need...

|||

Yeah, I'm going to put more investment into tableadapters/binding... I just have to figure out how to program it to get the results I need. Wish there was a book... so now I have to make do.

Thanks.

|||

SQL 2005 has a new function called ROW_NUMER. This generates a sequential number, based on another column in the table. So it may eliminate the need for inserting in the middle of the table, and renumbering the IDs. Sample usage:

Select ROW_NUMBER() over (order by MyColumn) as 'SequenceNumber', MyColumn from MyTable

hth

Fluffy

|||

Hi,

As a lot of other replies have said updating an identity column's value is bad. You certainly should not be doing it as part of normal operation of whatever application you are writing to access the database.

Perhaps if you gave a bit of information about what you are actually trying to do with your application in this situation then someone could suggest a better way of doing it. From what you've said so far it simply sounds like you haven't grasped how SQL works and are trying to doing things in an odd way.

Bye,

Dev.

|||In the first place, there's no concept of up or down in space, and there's no concept of 'middle' in a relational database. Indeed, the very essence of a relational database is that the physical location of the rows is irrelevant. So you can't talk about 'inserting a record into the middle of a database' and be talking, sensibly, about a relational database at all. Records go where they go, and that's all there is to it: you leave it up to the RDBMS to work out how to retrieve them, and display them in a user-defined order, when you issue SQL queries against the database.

Second, autoincrementing columns are used to provide meaningless surrogate keys. You suddenly want to start insisting on inserting a new number in between a series of pre-existing auto-incremented ones and what you are actually telling me is that you are reading meaning into what is meant to be, and what was designed to be, meaningless. If you begin changing the auto-assigned numbers to make room for a new entry, and you're actually updating a primary key... and that's just a relational no-no, usually. If the numbers assigned have to have a specific value, they mean something to you, they cannot therefore be meaningless and you shouldn't therefore have used an autoincrementing column to capture them. If, on the other hand, you are happy with autoincrementing numbers, then be happy with them whatever they may be for a particular record, and the question you ask then becomes redundant.

And those who have answered with code samples proposing turning autoincrementing off for a bit, updating the formerly autoincrementing column to make space for the new insert, and the rest... well, frankly, they've missed the point. The request makes no sense as written, demonstrates a fundamental misunderstanding of relational databases and their use, and cannot sensibly be answered.

If there's a specific business problem to be dealt with, that's a different matter ...and one that almost inevitably will come down to fixing a poor application design or being clearer on poorly-understood business requirements.

How to insert a record in the middle of a DB

Well, how do you do it? I've not seen any report on this... so could it be next to impossible?

Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.

Here's an idea:

CREATE TABLE MyTable (id int identity(1,1), col1 varchar(16))
GO

INSERT INTO MyTable SELECT 'value 01'
INSERT INTO MyTable SELECT 'value 02'
INSERT INTO MyTable SELECT 'value 03'
INSERT INTO MyTable SELECT 'value 04'
INSERT INTO MyTable SELECT 'value 05'
INSERT INTO MyTable SELECT 'value 06'
INSERT INTO MyTable SELECT 'value 07'
INSERT INTO MyTable SELECT 'value 08'
INSERT INTO MyTable SELECT 'value 09'
INSERT INTO MyTable SELECT 'value 10'
GO

CREATE TABLE tmpMyTable (id int identity(1,1), col1 varchar(16))
GO

SET IDENTITY_INSERT tmpMyTable ON
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id, col1
FROM MyTable
WHERE id < 5

INSERT INTO tmpMyTable (id,col1) SELECT 5,'value between'
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id+1, col1
FROM MyTable
WHERE id > 4

SET IDENTITY_INSERT tmpMyTable OFF
GO

DROP TABLE MyTable
GO

EXEC sp_rename 'tmpMyTable', 'MyTable'
GO

SELECT * FROM MyTable

|||

Thanks... I'm not very good at SQL but I figure, you create a new table, add those values to it and them insert them back into the old table?

If I can renumber the ID, that would be fine. I deleted record #2 and when I leave record one (view), it still stops where record #2 was deleted. I have to click again to go to record 3. I don't know why it just doesn't jump to record #3, seeing that #2 is deleted.

|||

Take a look at your other question...

Renumbering ID fields is BAD... I really mean it. It can only be done via another table to temporary store the data. There should be no reason to renumber IDs anyway, and if there IS a reson, then the field should not ba an ID ;)

|||

...ooops! That would be "delete" the old table and make the new table the current table.

Okay, ok, this can solve my problem, how can I advance to the next record using code only? I programed my app to go by ID, so if ID #2 is deleted, nothing happens because #2 is deleted. I have to click until the program counter is up to a ID value that isn't deleted before any records show up.

If I say (not really my code, just for example purposes)

CurrRec=CurrRec+1

Select Data from Table1 WHERE ID = CurrRec

Read

While CurrRec is NullDB then

CurrRec=CurrRec+1

Read? 'I could use a read to get pass the deleted record ID

'however, there's a problem if CurrRec > total records

'because it's at the end of the DB

end While

I could put in a time out loop that exits if no record is found in x tries... but surely there must be a way to jump to the NEXT RECORD and a END OF RECORD and so on.

Any takers?

|||

well to move through your records you should do something like this...

Dim oreader As SqlClient.SqlDataReader

Dim oCmd As SqlClient.SqlCommand

oCmd.CommandText = "Select * from mytable"

oreader = oCmd.ExecuteReader

While oreader.Read

'dostuff

End While

|||

I do also need to... move.first, move.last, move.next, ect. With the currRec to move next, I add 1 to currRec, to move back, I subtract 1, to move first, currRec=1.

I'm now thinking I can using CTE functions, if only I can find the syntax for them. That way, I don't use the ID, just the record number.

Thanks.

|||

zdrae,

To select each record, one record at a time, you can use the Top keyword. Each time you get back a record, just save the ID in a variable so that you may use it to select the next record.

EXAMPLE:
Select Top 1 Data from Table1 Where ID > prevID Order by ID

The idea is to select the next record who has an ID greater than the current ID.

Also, just FYI, I think some people in these forums are confused by your use of the term "ID". It seems to me that you really only mean an identity column, but the other posters in this forum are right that you should not attempt to renumber an identity field.

You may be used to programming in a "line-by-line" oriented way, but SQL is really very different and powerful as a data manipulation language. After using it for a while, you'll love how simple and powerful SQL is compared to "line-by-line" data processing.

Hope this helps...

|||

Actually, I didn't like SQL one bit... until I figured out what should actually be in the connection string (I finally got it open); but still I wasn't able to get any data out until I found that I was missing a read statement.

It's on my notebook and the speed is terrible... but everything is slow on it anyway... even games. Thanks.

|||This works great going forward. Any ideas on going backwards? I could store previous IDs but that won't do any good once it goes off the reservation.|||

Instead of a Datareader you could use the Datareader to fill a Dataset or a Datatable.

Those 2 objects will store the data permanently untill you discard them. By doing so they will allow you to scroll foreward and backwards (Or accessing any record you retrieved)

But be aware that you allways should only fetch those records you need...

|||

Yeah, I'm going to put more investment into tableadapters/binding... I just have to figure out how to program it to get the results I need. Wish there was a book... so now I have to make do.

Thanks.

|||

SQL 2005 has a new function called ROW_NUMER. This generates a sequential number, based on another column in the table. So it may eliminate the need for inserting in the middle of the table, and renumbering the IDs. Sample usage:

Select ROW_NUMBER() over (order by MyColumn) as 'SequenceNumber', MyColumn from MyTable

hth

Fluffy

|||

Hi,

As a lot of other replies have said updating an identity column's value is bad. You certainly should not be doing it as part of normal operation of whatever application you are writing to access the database.

Perhaps if you gave a bit of information about what you are actually trying to do with your application in this situation then someone could suggest a better way of doing it. From what you've said so far it simply sounds like you haven't grasped how SQL works and are trying to doing things in an odd way.

Bye,

Dev.

|||In the first place, there's no concept of up or down in space, and there's no concept of 'middle' in a relational database. Indeed, the very essence of a relational database is that the physical location of the rows is irrelevant. So you can't talk about 'inserting a record into the middle of a database' and be talking, sensibly, about a relational database at all. Records go where they go, and that's all there is to it: you leave it up to the RDBMS to work out how to retrieve them, and display them in a user-defined order, when you issue SQL queries against the database.

Second, autoincrementing columns are used to provide meaningless surrogate keys. You suddenly want to start insisting on inserting a new number in between a series of pre-existing auto-incremented ones and what you are actually telling me is that you are reading meaning into what is meant to be, and what was designed to be, meaningless. If you begin changing the auto-assigned numbers to make room for a new entry, and you're actually updating a primary key... and that's just a relational no-no, usually. If the numbers assigned have to have a specific value, they mean something to you, they cannot therefore be meaningless and you shouldn't therefore have used an autoincrementing column to capture them. If, on the other hand, you are happy with autoincrementing numbers, then be happy with them whatever they may be for a particular record, and the question you ask then becomes redundant.

And those who have answered with code samples proposing turning autoincrementing off for a bit, updating the formerly autoincrementing column to make space for the new insert, and the rest... well, frankly, they've missed the point. The request makes no sense as written, demonstrates a fundamental misunderstanding of relational databases and their use, and cannot sensibly be answered.

If there's a specific business problem to be dealt with, that's a different matter ...and one that almost inevitably will come down to fixing a poor application design or being clearer on poorly-understood business requirements.

How to insert a record in the middle of a DB

Well, how do you do it? I've not seen any report on this... so could it be next to impossible?

Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.

Here's an idea:

CREATE TABLE MyTable (id int identity(1,1), col1 varchar(16))
GO

INSERT INTO MyTable SELECT 'value 01'
INSERT INTO MyTable SELECT 'value 02'
INSERT INTO MyTable SELECT 'value 03'
INSERT INTO MyTable SELECT 'value 04'
INSERT INTO MyTable SELECT 'value 05'
INSERT INTO MyTable SELECT 'value 06'
INSERT INTO MyTable SELECT 'value 07'
INSERT INTO MyTable SELECT 'value 08'
INSERT INTO MyTable SELECT 'value 09'
INSERT INTO MyTable SELECT 'value 10'
GO

CREATE TABLE tmpMyTable (id int identity(1,1), col1 varchar(16))
GO

SET IDENTITY_INSERT tmpMyTable ON
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id, col1
FROM MyTable
WHERE id < 5

INSERT INTO tmpMyTable (id,col1) SELECT 5,'value between'
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id+1, col1
FROM MyTable
WHERE id > 4

SET IDENTITY_INSERT tmpMyTable OFF
GO

DROP TABLE MyTable
GO

EXEC sp_rename 'tmpMyTable', 'MyTable'
GO

SELECT * FROM MyTable

|||

Thanks... I'm not very good at SQL but I figure, you create a new table, add those values to it and them insert them back into the old table?

If I can renumber the ID, that would be fine. I deleted record #2 and when I leave record one (view), it still stops where record #2 was deleted. I have to click again to go to record 3. I don't know why it just doesn't jump to record #3, seeing that #2 is deleted.

|||

Take a look at your other question...

Renumbering ID fields is BAD... I really mean it. It can only be done via another table to temporary store the data. There should be no reason to renumber IDs anyway, and if there IS a reson, then the field should not ba an ID ;)

|||

...ooops! That would be "delete" the old table and make the new table the current table.

Okay, ok, this can solve my problem, how can I advance to the next record using code only? I programed my app to go by ID, so if ID #2 is deleted, nothing happens because #2 is deleted. I have to click until the program counter is up to a ID value that isn't deleted before any records show up.

If I say (not really my code, just for example purposes)

CurrRec=CurrRec+1

Select Data from Table1 WHERE ID = CurrRec

Read

While CurrRec is NullDB then

CurrRec=CurrRec+1

Read? 'I could use a read to get pass the deleted record ID

'however, there's a problem if CurrRec > total records

'because it's at the end of the DB

end While

I could put in a time out loop that exits if no record is found in x tries... but surely there must be a way to jump to the NEXT RECORD and a END OF RECORD and so on.

Any takers?

|||

well to move through your records you should do something like this...

Dim oreader As SqlClient.SqlDataReader

Dim oCmd As SqlClient.SqlCommand

oCmd.CommandText = "Select * from mytable"

oreader = oCmd.ExecuteReader

While oreader.Read

'dostuff

End While

|||

I do also need to... move.first, move.last, move.next, ect. With the currRec to move next, I add 1 to currRec, to move back, I subtract 1, to move first, currRec=1.

I'm now thinking I can using CTE functions, if only I can find the syntax for them. That way, I don't use the ID, just the record number.

Thanks.

|||

zdrae,

To select each record, one record at a time, you can use the Top keyword. Each time you get back a record, just save the ID in a variable so that you may use it to select the next record.

EXAMPLE:
Select Top 1 Data from Table1 Where ID > prevID Order by ID

The idea is to select the next record who has an ID greater than the current ID.

Also, just FYI, I think some people in these forums are confused by your use of the term "ID". It seems to me that you really only mean an identity column, but the other posters in this forum are right that you should not attempt to renumber an identity field.

You may be used to programming in a "line-by-line" oriented way, but SQL is really very different and powerful as a data manipulation language. After using it for a while, you'll love how simple and powerful SQL is compared to "line-by-line" data processing.

Hope this helps...

|||

Actually, I didn't like SQL one bit... until I figured out what should actually be in the connection string (I finally got it open); but still I wasn't able to get any data out until I found that I was missing a read statement.

It's on my notebook and the speed is terrible... but everything is slow on it anyway... even games. Thanks.

|||This works great going forward. Any ideas on going backwards? I could store previous IDs but that won't do any good once it goes off the reservation.|||

Instead of a Datareader you could use the Datareader to fill a Dataset or a Datatable.

Those 2 objects will store the data permanently untill you discard them. By doing so they will allow you to scroll foreward and backwards (Or accessing any record you retrieved)

But be aware that you allways should only fetch those records you need...

|||

Yeah, I'm going to put more investment into tableadapters/binding... I just have to figure out how to program it to get the results I need. Wish there was a book... so now I have to make do.

Thanks.

|||

SQL 2005 has a new function called ROW_NUMER. This generates a sequential number, based on another column in the table. So it may eliminate the need for inserting in the middle of the table, and renumbering the IDs. Sample usage:

Select ROW_NUMBER() over (order by MyColumn) as 'SequenceNumber', MyColumn from MyTable

hth

Fluffy

|||

Hi,

As a lot of other replies have said updating an identity column's value is bad. You certainly should not be doing it as part of normal operation of whatever application you are writing to access the database.

Perhaps if you gave a bit of information about what you are actually trying to do with your application in this situation then someone could suggest a better way of doing it. From what you've said so far it simply sounds like you haven't grasped how SQL works and are trying to doing things in an odd way.

Bye,

Dev.

|||In the first place, there's no concept of up or down in space, and there's no concept of 'middle' in a relational database. Indeed, the very essence of a relational database is that the physical location of the rows is irrelevant. So you can't talk about 'inserting a record into the middle of a database' and be talking, sensibly, about a relational database at all. Records go where they go, and that's all there is to it: you leave it up to the RDBMS to work out how to retrieve them, and display them in a user-defined order, when you issue SQL queries against the database.

Second, autoincrementing columns are used to provide meaningless surrogate keys. You suddenly want to start insisting on inserting a new number in between a series of pre-existing auto-incremented ones and what you are actually telling me is that you are reading meaning into what is meant to be, and what was designed to be, meaningless. If you begin changing the auto-assigned numbers to make room for a new entry, and you're actually updating a primary key... and that's just a relational no-no, usually. If the numbers assigned have to have a specific value, they mean something to you, they cannot therefore be meaningless and you shouldn't therefore have used an autoincrementing column to capture them. If, on the other hand, you are happy with autoincrementing numbers, then be happy with them whatever they may be for a particular record, and the question you ask then becomes redundant.

And those who have answered with code samples proposing turning autoincrementing off for a bit, updating the formerly autoincrementing column to make space for the new insert, and the rest... well, frankly, they've missed the point. The request makes no sense as written, demonstrates a fundamental misunderstanding of relational databases and their use, and cannot sensibly be answered.

If there's a specific business problem to be dealt with, that's a different matter ...and one that almost inevitably will come down to fixing a poor application design or being clearer on poorly-understood business requirements.

How to insert a record in the middle of a DB

Well, how do you do it? I've not seen any report on this... so could it be next to impossible?

Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.

Here's an idea:

CREATE TABLE MyTable (id int identity(1,1), col1 varchar(16))
GO

INSERT INTO MyTable SELECT 'value 01'
INSERT INTO MyTable SELECT 'value 02'
INSERT INTO MyTable SELECT 'value 03'
INSERT INTO MyTable SELECT 'value 04'
INSERT INTO MyTable SELECT 'value 05'
INSERT INTO MyTable SELECT 'value 06'
INSERT INTO MyTable SELECT 'value 07'
INSERT INTO MyTable SELECT 'value 08'
INSERT INTO MyTable SELECT 'value 09'
INSERT INTO MyTable SELECT 'value 10'
GO

CREATE TABLE tmpMyTable (id int identity(1,1), col1 varchar(16))
GO

SET IDENTITY_INSERT tmpMyTable ON
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id, col1
FROM MyTable
WHERE id < 5

INSERT INTO tmpMyTable (id,col1) SELECT 5,'value between'
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id+1, col1
FROM MyTable
WHERE id > 4

SET IDENTITY_INSERT tmpMyTable OFF
GO

DROP TABLE MyTable
GO

EXEC sp_rename 'tmpMyTable', 'MyTable'
GO

SELECT * FROM MyTable

|||

Thanks... I'm not very good at SQL but I figure, you create a new table, add those values to it and them insert them back into the old table?

If I can renumber the ID, that would be fine. I deleted record #2 and when I leave record one (view), it still stops where record #2 was deleted. I have to click again to go to record 3. I don't know why it just doesn't jump to record #3, seeing that #2 is deleted.

|||

Take a look at your other question...

Renumbering ID fields is BAD... I really mean it. It can only be done via another table to temporary store the data. There should be no reason to renumber IDs anyway, and if there IS a reson, then the field should not ba an ID ;)

|||

...ooops! That would be "delete" the old table and make the new table the current table.

Okay, ok, this can solve my problem, how can I advance to the next record using code only? I programed my app to go by ID, so if ID #2 is deleted, nothing happens because #2 is deleted. I have to click until the program counter is up to a ID value that isn't deleted before any records show up.

If I say (not really my code, just for example purposes)

CurrRec=CurrRec+1

Select Data from Table1 WHERE ID = CurrRec

Read

While CurrRec is NullDB then

CurrRec=CurrRec+1

Read? 'I could use a read to get pass the deleted record ID

'however, there's a problem if CurrRec > total records

'because it's at the end of the DB

end While

I could put in a time out loop that exits if no record is found in x tries... but surely there must be a way to jump to the NEXT RECORD and a END OF RECORD and so on.

Any takers?

|||

well to move through your records you should do something like this...

Dim oreader As SqlClient.SqlDataReader

Dim oCmd As SqlClient.SqlCommand

oCmd.CommandText = "Select * from mytable"

oreader = oCmd.ExecuteReader

While oreader.Read

'dostuff

End While

|||

I do also need to... move.first, move.last, move.next, ect. With the currRec to move next, I add 1 to currRec, to move back, I subtract 1, to move first, currRec=1.

I'm now thinking I can using CTE functions, if only I can find the syntax for them. That way, I don't use the ID, just the record number.

Thanks.

|||

zdrae,

To select each record, one record at a time, you can use the Top keyword. Each time you get back a record, just save the ID in a variable so that you may use it to select the next record.

EXAMPLE:
Select Top 1 Data from Table1 Where ID > prevID Order by ID

The idea is to select the next record who has an ID greater than the current ID.

Also, just FYI, I think some people in these forums are confused by your use of the term "ID". It seems to me that you really only mean an identity column, but the other posters in this forum are right that you should not attempt to renumber an identity field.

You may be used to programming in a "line-by-line" oriented way, but SQL is really very different and powerful as a data manipulation language. After using it for a while, you'll love how simple and powerful SQL is compared to "line-by-line" data processing.

Hope this helps...

|||

Actually, I didn't like SQL one bit... until I figured out what should actually be in the connection string (I finally got it open); but still I wasn't able to get any data out until I found that I was missing a read statement.

It's on my notebook and the speed is terrible... but everything is slow on it anyway... even games. Thanks.

|||This works great going forward. Any ideas on going backwards? I could store previous IDs but that won't do any good once it goes off the reservation.|||

Instead of a Datareader you could use the Datareader to fill a Dataset or a Datatable.

Those 2 objects will store the data permanently untill you discard them. By doing so they will allow you to scroll foreward and backwards (Or accessing any record you retrieved)

But be aware that you allways should only fetch those records you need...

|||

Yeah, I'm going to put more investment into tableadapters/binding... I just have to figure out how to program it to get the results I need. Wish there was a book... so now I have to make do.

Thanks.

|||

SQL 2005 has a new function called ROW_NUMER. This generates a sequential number, based on another column in the table. So it may eliminate the need for inserting in the middle of the table, and renumbering the IDs. Sample usage:

Select ROW_NUMBER() over (order by MyColumn) as 'SequenceNumber', MyColumn from MyTable

hth

Fluffy

|||

Hi,

As a lot of other replies have said updating an identity column's value is bad. You certainly should not be doing it as part of normal operation of whatever application you are writing to access the database.

Perhaps if you gave a bit of information about what you are actually trying to do with your application in this situation then someone could suggest a better way of doing it. From what you've said so far it simply sounds like you haven't grasped how SQL works and are trying to doing things in an odd way.

Bye,

Dev.

|||In the first place, there's no concept of up or down in space, and there's no concept of 'middle' in a relational database. Indeed, the very essence of a relational database is that the physical location of the rows is irrelevant. So you can't talk about 'inserting a record into the middle of a database' and be talking, sensibly, about a relational database at all. Records go where they go, and that's all there is to it: you leave it up to the RDBMS to work out how to retrieve them, and display them in a user-defined order, when you issue SQL queries against the database.

Second, autoincrementing columns are used to provide meaningless surrogate keys. You suddenly want to start insisting on inserting a new number in between a series of pre-existing auto-incremented ones and what you are actually telling me is that you are reading meaning into what is meant to be, and what was designed to be, meaningless. If you begin changing the auto-assigned numbers to make room for a new entry, and you're actually updating a primary key... and that's just a relational no-no, usually. If the numbers assigned have to have a specific value, they mean something to you, they cannot therefore be meaningless and you shouldn't therefore have used an autoincrementing column to capture them. If, on the other hand, you are happy with autoincrementing numbers, then be happy with them whatever they may be for a particular record, and the question you ask then becomes redundant.

And those who have answered with code samples proposing turning autoincrementing off for a bit, updating the formerly autoincrementing column to make space for the new insert, and the rest... well, frankly, they've missed the point. The request makes no sense as written, demonstrates a fundamental misunderstanding of relational databases and their use, and cannot sensibly be answered.

If there's a specific business problem to be dealt with, that's a different matter ...and one that almost inevitably will come down to fixing a poor application design or being clearer on poorly-understood business requirements.

How to insert a record in the middle of a DB

Well, how do you do it? I've not seen any report on this... so could it be next to impossible?

Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it? And oh, each record uses the auto ID increment feature.

Here's an idea:

CREATE TABLE MyTable (id int identity(1,1), col1 varchar(16))
GO

INSERT INTO MyTable SELECT 'value 01'
INSERT INTO MyTable SELECT 'value 02'
INSERT INTO MyTable SELECT 'value 03'
INSERT INTO MyTable SELECT 'value 04'
INSERT INTO MyTable SELECT 'value 05'
INSERT INTO MyTable SELECT 'value 06'
INSERT INTO MyTable SELECT 'value 07'
INSERT INTO MyTable SELECT 'value 08'
INSERT INTO MyTable SELECT 'value 09'
INSERT INTO MyTable SELECT 'value 10'
GO

CREATE TABLE tmpMyTable (id int identity(1,1), col1 varchar(16))
GO

SET IDENTITY_INSERT tmpMyTable ON
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id, col1
FROM MyTable
WHERE id < 5

INSERT INTO tmpMyTable (id,col1) SELECT 5,'value between'
GO

INSERT INTO tmpMyTable (id,col1)
SELECT id+1, col1
FROM MyTable
WHERE id > 4

SET IDENTITY_INSERT tmpMyTable OFF
GO

DROP TABLE MyTable
GO

EXEC sp_rename 'tmpMyTable', 'MyTable'
GO

SELECT * FROM MyTable

|||

Thanks... I'm not very good at SQL but I figure, you create a new table, add those values to it and them insert them back into the old table?

If I can renumber the ID, that would be fine. I deleted record #2 and when I leave record one (view), it still stops where record #2 was deleted. I have to click again to go to record 3. I don't know why it just doesn't jump to record #3, seeing that #2 is deleted.

|||

Take a look at your other question...

Renumbering ID fields is BAD... I really mean it. It can only be done via another table to temporary store the data. There should be no reason to renumber IDs anyway, and if there IS a reson, then the field should not ba an ID ;)

|||

...ooops! That would be "delete" the old table and make the new table the current table.

Okay, ok, this can solve my problem, how can I advance to the next record using code only? I programed my app to go by ID, so if ID #2 is deleted, nothing happens because #2 is deleted. I have to click until the program counter is up to a ID value that isn't deleted before any records show up.

If I say (not really my code, just for example purposes)

CurrRec=CurrRec+1

Select Data from Table1 WHERE ID = CurrRec

Read

While CurrRec is NullDB then

CurrRec=CurrRec+1

Read? 'I could use a read to get pass the deleted record ID

'however, there's a problem if CurrRec > total records

'because it's at the end of the DB

end While

I could put in a time out loop that exits if no record is found in x tries... but surely there must be a way to jump to the NEXT RECORD and a END OF RECORD and so on.

Any takers?

|||

well to move through your records you should do something like this...

Dim oreader As SqlClient.SqlDataReader

Dim oCmd As SqlClient.SqlCommand

oCmd.CommandText = "Select * from mytable"

oreader = oCmd.ExecuteReader

While oreader.Read

'dostuff

End While

|||

I do also need to... move.first, move.last, move.next, ect. With the currRec to move next, I add 1 to currRec, to move back, I subtract 1, to move first, currRec=1.

I'm now thinking I can using CTE functions, if only I can find the syntax for them. That way, I don't use the ID, just the record number.

Thanks.

|||

zdrae,

To select each record, one record at a time, you can use the Top keyword. Each time you get back a record, just save the ID in a variable so that you may use it to select the next record.

EXAMPLE:
Select Top 1 Data from Table1 Where ID > prevID Order by ID

The idea is to select the next record who has an ID greater than the current ID.

Also, just FYI, I think some people in these forums are confused by your use of the term "ID". It seems to me that you really only mean an identity column, but the other posters in this forum are right that you should not attempt to renumber an identity field.

You may be used to programming in a "line-by-line" oriented way, but SQL is really very different and powerful as a data manipulation language. After using it for a while, you'll love how simple and powerful SQL is compared to "line-by-line" data processing.

Hope this helps...

|||

Actually, I didn't like SQL one bit... until I figured out what should actually be in the connection string (I finally got it open); but still I wasn't able to get any data out until I found that I was missing a read statement.

It's on my notebook and the speed is terrible... but everything is slow on it anyway... even games. Thanks.

|||This works great going forward. Any ideas on going backwards? I could store previous IDs but that won't do any good once it goes off the reservation.|||

Instead of a Datareader you could use the Datareader to fill a Dataset or a Datatable.

Those 2 objects will store the data permanently untill you discard them. By doing so they will allow you to scroll foreward and backwards (Or accessing any record you retrieved)

But be aware that you allways should only fetch those records you need...

|||

Yeah, I'm going to put more investment into tableadapters/binding... I just have to figure out how to program it to get the results I need. Wish there was a book... so now I have to make do.

Thanks.

|||

SQL 2005 has a new function called ROW_NUMER. This generates a sequential number, based on another column in the table. So it may eliminate the need for inserting in the middle of the table, and renumbering the IDs. Sample usage:

Select ROW_NUMBER() over (order by MyColumn) as 'SequenceNumber', MyColumn from MyTable

hth

Fluffy

|||

Hi,

As a lot of other replies have said updating an identity column's value is bad. You certainly should not be doing it as part of normal operation of whatever application you are writing to access the database.

Perhaps if you gave a bit of information about what you are actually trying to do with your application in this situation then someone could suggest a better way of doing it. From what you've said so far it simply sounds like you haven't grasped how SQL works and are trying to doing things in an odd way.

Bye,

Dev.

|||In the first place, there's no concept of up or down in space, and there's no concept of 'middle' in a relational database. Indeed, the very essence of a relational database is that the physical location of the rows is irrelevant. So you can't talk about 'inserting a record into the middle of a database' and be talking, sensibly, about a relational database at all. Records go where they go, and that's all there is to it: you leave it up to the RDBMS to work out how to retrieve them, and display them in a user-defined order, when you issue SQL queries against the database.

Second, autoincrementing columns are used to provide meaningless surrogate keys. You suddenly want to start insisting on inserting a new number in between a series of pre-existing auto-incremented ones and what you are actually telling me is that you are reading meaning into what is meant to be, and what was designed to be, meaningless. If you begin changing the auto-assigned numbers to make room for a new entry, and you're actually updating a primary key... and that's just a relational no-no, usually. If the numbers assigned have to have a specific value, they mean something to you, they cannot therefore be meaningless and you shouldn't therefore have used an autoincrementing column to capture them. If, on the other hand, you are happy with autoincrementing numbers, then be happy with them whatever they may be for a particular record, and the question you ask then becomes redundant.

And those who have answered with code samples proposing turning autoincrementing off for a bit, updating the formerly autoincrementing column to make space for the new insert, and the rest... well, frankly, they've missed the point. The request makes no sense as written, demonstrates a fundamental misunderstanding of relational databases and their use, and cannot sensibly be answered.

If there's a specific business problem to be dealt with, that's a different matter ...and one that almost inevitably will come down to fixing a poor application design or being clearer on poorly-understood business requirements.