Database

Need a way to clone rows in SQL [modified]

25-Jul-06 1:53

I tried this on XP PRo SQL Server 2005 Developer and this worked fine.

If you using SQL Server Express then this is not supplied with a Reporting Server Install and thus it won't upload into your local enviroments.

If you are using SQL server developer edition 2005 then you have to install the Reproting Server on your local box or a networked server.

To check if you have Reporting services installed go to http://localhost/Reports and your get up the web management tool.

Look where you want to go not where you don't want to crash.

Bikers Bible

PyroManiak24-Jul-06 10:07

24-Jul-06 10:07

I'm literally trying to clone data rows, nothing very complicated, infact, they aren't even going to be changed during this section of the project.

I've captured the rows I want to clone using a custom SQL query, but I'm not sure how to insert them back into the database with new ID's(primary key is autonumber)...

can't I just run a .update of some sort?

I've tried copying the data table from results of my query and merging into the complete table, that doesn't make any change.
I dont seem to be able to .insert anything other then independant values for each column.

I'm using VS 2005 and C#.

Thanks.

-- modified at 17:09 Monday 24th July, 2006

Re: Need a way to clone rows in SQL [modified]

Michael Potter24-Jul-06 11:31

Michael Potter

24-Jul-06 11:31

Just run an insert ignoring the autonumber primary key. It will be filled in for you.

INSERT INTO MyTable
(
   Field1,
   Field2,
   Field3
)
SELECT
   Field1,
   Field2,
   Field3
FROM
   MyTable
WHERE
   Field2 = 'MyCriteria'

PyroManiak24-Jul-06 11:44

24-Jul-06 11:44

I'm trying that, and I dont think I'm doing something right in the query.

Is field1 supposed to be the primary key?(as per default)?

I'm getting "primary key doesn't allow nulls" error, and my field1 is set to the autonum.

By autonum, I mean that SQL is set for INT for primary key, and I've got my dataset flagged to incriment 1, seed 1.

I think this is the correct way to set for autonum? I'm used to there being a default "autonum" from access for the field type...

-- modified at 18:26 Monday 24th July, 2006

Frank Kerrigan25-Jul-06 1:55

25-Jul-06 1:55

try.....

insert into Employees
SELECT * from Employees where name = 'Joe'

Watch out for Unquie fields and indentity.

Look where you want to go not where you don't want to crash.

Bikers Bible

Michael Potter25-Jul-06 4:21

Michael Potter

25-Jul-06 4:21

Do not specify the primary key. A new one will be generated for you.

Note: If you expect to get a full clone (copy the primary key also) then you will never accomplish this in a single table. Primary keys can not be duplicated.

PyroManiak25-Jul-06 7:52

Re: Need a way to clone rows in SQL [modified]

25-Jul-06 7:52

If I use

INSERT INTO tbABC
SELECT (all but primary key)
FROM tbABC
WHERE Name = @Name

I get an error back saying that the Column Name or number of supplied values does not match the table definition.

If I use

INSERT INTO tbABC
SELECT *
FROM tbABC
WHERE Name = @Name

I get a name saying that the primary key can't be duplicated (duh)... I've been looking all over the table design and properties in SQL and I can't find where I can actually pick an autonumber for the field type.

I thought that inside my dataset I'd set to AutoIncriment, seed 1, step 1..
isn't this correctly setting autonumbering for an SQL database? I'm used to access just having autonum as a field type choice.

Thanks.

PyroManiak25-Jul-06 7:57

25-Jul-06 7:57

Sweet! I just found out I can flag the primary key as the identity column, now it works!!! Yay!

Thanks alot for your help guys.

-- modified at 15:04 Tuesday 25th July, 2006

Row based security

martin_hughes24-Jul-06 9:49

martin_hughes

24-Jul-06 9:49

Hi All,

I'm not sure if this should go here, or in the ASP.Net forum, so I'll try here and if someone wants to move it later, that's fine by me Big Grin | :-D

I'm developing a web application using ASP.Net 2.0, which will be back-ended by a SQL Server 2005 database (Express for the moment, but once it prooves to be succesful, I think the bean counters will stump up for enterprise Smile | :)

).

The application is multi-user, so it must provide security for each user's data in that one user should not be able to view another user's data unless given permission to do so. To this end, I've decided that I need to implement row based security for each record. The plan is that each user is assigned to a role using the ASP.Net 2.0 security / login features, and each role is unique to each customer who uses the system. For example, take Mike - he works for CompanyA; in my application CompanyA is defined as a Role, and Mike is assigned to this Role. Now each and every record Mike enters is identified with the RoleID of CompanyA, and so Mike can only enter records for CompanyA and can only view records for CompanyA.

This seems like a pretty good way of acheiving this, but as always it's one of my home-brew plans, so I wondered if anyone had any experience of this kind of thing they could share, or can point out any pitfalls in my plans.

Thanks,

Martin.

Re: Row based security

Colin Angus Mackay24-Jul-06 11:52

Colin Angus Mackay

24-Jul-06 11:52

SQL Server does not support row based authorisation as you describe. The business logic in the applications will have to ensure that Mike can only create or update the rows he is permitted to.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: Row based security

Frank Kerrigan25-Jul-06 2:03

Bastardized query into one query

25-Jul-06 2:03

Don't you mean "Role Based Security" each user is assigned a role or trust level within the application that can if required use different database access levels as well.

You also have to consider how users with authenicate ( Windwos , Forms or both ?) and get a user id you can assign a Role or Memebership to. This can get fairly complex for mixed authenicated systems.

You could as a user is authenicated assign various Session State variable or better still a serialisable user object that can travel round with the user on each page. This will state user, customername or scope and access or trust level within the application.

Start simply and build from there.

Look where you want to go not where you don't want to crash.

Bikers Bible

Re: Row based security

martin_hughes25-Jul-06 8:04

martin_hughes

25-Jul-06 8:04

Nope - I definitely mean Row based security - but those are some good tips anyway Big Grin | :-D

Nino_124-Jul-06 8:11

Nino_1

24-Jul-06 8:11

Hi,

I have a query that needs to act as one single query:

Here's the first part:

select distinct a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled


from a10 a 
inner join escrow d on a.escrow = d.escrow
inner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21 ' and j.fstatus = 'PAID' group by a.officer

Here's the second part:

x.amount as New from escrow k inner join e120 x on k.escrow = x.escrow
inner join a10 g on x.escrow = g.escrow  where k.ftype = 'S' group by x.amount, g.officer having x.amount <> 0.0

Here's the third part:

b.amount as Refi from escrow c inner join e120 b on c.escrow = b.escrow
inner join a10 f on c.escrow = f.escrow where c.ftype = 'R' group by b.amount, f.officer having b.amount <> 0.0

Is there a way to make this act as one single query? I have an application that reads only one cursor at a time.

Thanks In Advance,

Nino

Hrmm

Ennis Ray Lynch, Jr.24-Jul-06 9:26

Ennis Ray Lynch, Jr.

24-Jul-06 9:26

I must ask why is the second part using different alias names from the third part? The queries seem to only differ by escrow.ftype.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: Hrmm

Nino_124-Jul-06 11:37

Nino_1

24-Jul-06 11:37

My reasoning behind that was to get the values of two different fields.

The second part is getting the values for the 'S' which is defined as a "New" escrow sale.

The third part is getting the values for the 'R' which is defined as a "Refi" or refinance.

After these two fields are retrieved they are then added based on the escrow field based on the join.

Nino

Re: Bastardized query into one query

Michael Potter24-Jul-06 11:43

Michael Potter

24-Jul-06 11:43

I might ask why the second & third part have 'GROUP BY' & 'HAVING' clauses when there is only 1 field selected and it is not an aggregate.

I also question the counting of distinct dates in the first query. Can't an officer handle more then one open/closed/cancelled transaction per day?

SqlException Error - Login

TheJudeDude24-Jul-06 7:06

TheJudeDude

24-Jul-06 7:06

I have a database that I created localy and the owner is listed as me. But when trying to access the database (conn.open()) I get the following error:
SqlException: Cannot open the database requested in login 'labels'.
Login failed for user 'AFFORDABLERTO\brian'

I triple checked the ownership, and the user named in the error is the owner.Here is the connection string
SqlConnection conn = new SqlConnection("Data Source=affordable02;Initial Catalog=Labels;Trusted_Connection=true");
What else could I check....this is the first time I have had this problem.
Thanx in advance!

Jude

Re: SqlException Error - Login

Frank Kerrigan25-Jul-06 2:07