Database

Re: Build your own identity value

4-Aug-09 22:48

First of all many thanks to David Skelly and David Mujica for the work-around about the IDENT_CURRENT bug.

Second, i'm an sql script beginner.

I know that about IDENT_CURRENT Microsoft report "Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENTITY_INCR
because of insertions performed by other sessions."

The job I have to do is to move rows from DB_SRC to DB_DST having the same schema.
DB_DST can be empty or not.

---------------------------
TAB. tA
---------------------------
a_id int identity not null,
b_id int not null
---------------------------

alter table tA
add constraint tB_tA_FK1 foreign key (b_id)
	 references tB (b_id)

---------------------------
TAB. tB
---------------------------
b_id int identity not null
---------------------------

this was my first implementation...

USE [DB_SRC]

CREATE TABLE #corr_id
(
   b_id_src  INT NULL,
   b_id_dst  INT NULL
}

DECLARE @IdNext AS INT
SET @IdNext = IDENT_CURRENT('[DB_DST].dbo.tB')

-- Set the correspondence
INSERT INTO #corr_id (b_id_src, b_id_dst)
	SELECT 	b_id, ROW_NUMBER() OVER (order by b_id) + @IdNext 
		FROM tB WHERE b_id IN .....code depending on user choise

-- Use the correspondence
INSERT INTO [DB_DST].dbo.tA
	SELECT	#corr_id.b_id_src FROM tA INNER JOIN #corr_id ON tA.b_id = #corr_id.b_id_src

but the IDENT_CURRENT bug (explained in previous post) occured and than...

USE [DB_DST]
ALTER TABLE tB	ADD id_src INT NULL
GO

USE [DB_SRC]

-- Set the correspondence
INSERT INTO [DB_DST].dbo.tB (id_src)
	SELECT	b_id FROM tB WHERE b_id IN .....code depending on user choise

-- Use the correspondence
INSERT INTO [DB_DST].dbo.tA
	SELECT	tBDST.b_id FROM tA INNER JOIN [DB_DST].dbo.tB AS tBDST 
			ON tA.b_id = tBDST.id_src

USE [DB_DST]
ALTER TABLE tB	DROP COLUMN id_src
GO

BTW, turning back to the post object, is there a built-in function or sp to know if a table was never populated?

modified on Wednesday, August 5, 2009 8:26 AM

David Mujica5-Aug-09 2:59

David Mujica

5-Aug-09 2:59

Is DB_DST a read-only database ? Will records every be inserted into this database ? If not, then maybe for table, tB, you can drop the "Identity" qualifier and just do a brute force copy from tA to tB. This will guarantee that the ID values are the same between the two tables.

David

Re: Build your own identity value

Roberto Mazzone5-Aug-09 22:11

Re: How to check for a never populated table on sql server 2005

5-Aug-09 22:11

David, see my reply to Mike...

Mike Ellison4-Aug-09 3:13

Mike Ellison

4-Aug-09 3:13

Why do you need to predict the next identity value on a table? I'm just curious.

www.MishaInTheCloud.com

Re: How to check for a never populated table on sql server 2005 [modified]

Roberto Mazzone4-Aug-09 22:51

Re: How to check for a never populated table on sql server 2005

4-Aug-09 22:51

Mike, anyway i'm an sql script beginner.

I know that about IDENT_CURRENT Microsoft report "Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENTITY_INCR
because of insertions performed by other sessions."

The job I have to do is to move rows from DB_SRC to DB_DST having the same schema.
DB_DST can be empty or not.

----------------------------
TAB. tA
----------------------------
a_id int identity not null,
b_id int not null
----------------------------

alter table tA
add constraint tB_tA_FK1 foreign key (b_id)
	 references tB (b_id)

----------------------------
TAB. tB
----------------------------
b_id int identity not null
----------------------------

this was my first implementation...

USE [DB_SRC]

CREATE TABLE #corr_id
(
   b_id_src  INT NULL,
   b_id_dst  INT NULL
}

DECLARE @IdNext AS INT
SET @IdNext = IDENT_CURRENT('[DB_DST].dbo.tB')

-- Set the correspondence
INSERT INTO #corr_id (b_id_src, b_id_dst)
	SELECT 	b_id, ROW_NUMBER() OVER (order by b_id) + @IdNext 
		FROM tB WHERE b_id IN .....code depending on user choise

-- Use the correspondence
INSERT INTO [DB_DST].dbo.tA
	SELECT	#corr_id.b_id_src FROM tA INNER JOIN #corr_id ON tA.b_id = #corr_id.b_id_src

but the IDENT_CURRENT bug (explained in previous post) occured and than...

USE [DB_DST]
ALTER TABLE tB	ADD id_src INT NULL
GO

USE [DB_SRC]

-- Set the correspondence
INSERT INTO [DB_DST].dbo.tB (id_src)
	SELECT	b_id FROM tB WHERE b_id IN .....code depending on user choise

-- Use the correspondence
INSERT INTO [DB_DST].dbo.tA
	SELECT	tBDST.b_id FROM tA INNER JOIN [DB_DST].dbo.tB AS tBDST 
			ON tA.b_id = tBDST.id_src

USE [DB_DST]
ALTER TABLE tB	DROP COLUMN id_src
GO

BTW, turning back to the post object, is there a built-in function or sp to know if a table was never populated?

modified on Wednesday, August 5, 2009 8:36 AM

Mike Ellison5-Aug-09 6:28

Mike Ellison

5-Aug-09 6:28

It may be that my intuition is off, but I am getting the sense that you are making something much more difficult than it needs to be. If I understand your post, you are simply moving data from a source database to a destination? And your destination needs to have an identity column? Why not just let it auto-increment itself the normal way?

I think I must be missing something here.

www.MishaInTheCloud.com

Re: How to check for a never populated table on sql server 2005

Roberto Mazzone5-Aug-09 22:08

5-Aug-09 22:08

Mike,
I have to preserve the referential integrity of this constraint.

alter table tA
add constraint tB_tA_FK1 foreign key (b_id)
	 references tB (b_id)

Situation before moving data from DB_SRC to DB_DST
on DB_SRC.tA
a_id b_id
1 100

on DB_SRC.tB
b_id
100

on DB_DST.tA
a_id b_id
1 200

on DB_DST.tB
b_id
200

when i insert data from DB_SRC.tB to DB_DST.tB. b_id became 201 and when i insert data from DB_SRC.tA to DB_DST.tA
DB_DST.tA.b_id have to became 201. To achieve this goal i used the IDENT_CURRENT for DB_DST.tB (200) plus ROW_NUMBER (the rank progressive, from 1 to n) as previously reported.

I solved adding an extracolumn DB_DST.tB.id_src, than

on DB_DST.tB
b_id id_src
200
201 100

in this way i have the right correspondence to do the work as explained in the previous post.

Anyway many thanks for your interest.

NDoc to SQL scripts?

devvvy3-Aug-09 5:43

devvvy

3-Aug-09 5:43

hello

Can we use NDoc for SQL scripts?

Thanks

dev

Re: NDoc to SQL scripts?

leckey3-Aug-09 8:51

leckey

3-Aug-09 8:51

As far as I know NDoc is used only for C# code. You could probably use it if your C# code calls a SQL script, but you can't use it to actually write or create a SQL script.

Back in the blog beatch!
http://CraptasticNation.blogspot.com/[^]

Re: NDoc to SQL scripts?

devvvy3-Aug-09 14:51

devvvy

3-Aug-09 14:51

Great thanks.

dev

Stored Procedure Pl/SQL

Civic063-Aug-09 4:35

Civic06

3-Aug-09 4:35

how to write a stored procedure in which a temporary table is created and inserts are made into that table.

Vimalsoft(Pty) Ltd3-Aug-09 4:47

3-Aug-09 4:47

CREATE PROC prc_insert_example
AS

SELECT * INTO #TEMP
FROM MYTABLE

CREATE PROC prc_insert_example
AS

CREATE TABLE #My_Temp
(
Age int,
FName varchar(15)
)

insert into  #My_Temp
select Age,FName from MYTABLE

Do you understand ?

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Civic063-Aug-09 4:59

Civic06

3-Aug-09 4:59

THis syntax is not correct for a oracle stored procedure unless i am doing something wrong

Vimalsoft(Pty) Ltd3-Aug-09 5:06

3-Aug-09 5:06

I thought you wanted a SQl example , but am worried because you posted here without using google

http://www.google.co.za/search?hl=en&q=create+a+temp+table+in+oracle&meta=&aq=1&oq=Create+a+temp+table+in+[^]

Please don't do this again, its against the Forums policy. i forgive you for that. Smile | :)

Civic063-Aug-09 5:33

Civic06

3-Aug-09 5:33

I tried it but it doesn't seem to work. Also, I just read that in oracle stored procedures you shouldn't create the temp table in the procedure it should exist and then you may use it in the procedure.

but how can it exist in advance.

Error in Stored Procedure.

David Skelly3-Aug-09 6:09

David Skelly

3-Aug-09 6:09

Oracle (PL-SQL) doesn't have temporary tables in the same way as SQL Server (T-SQL). Read up about Oracle Global Temporary Tables. These work in a different way from SQL Server #temp tables, but give you the same end result.

Satish - Developer3-Aug-09 3:29

Satish - Developer

3-Aug-09 3:29

Hi, i have writeen below stored procedure. I will give input as one zipcode and querystr. If there is no record with given zipcode, it has to go for another query. But it is not going in second if condition. When i try to print the query always showing the first one.

--Exec Test '48019','t1.engl,t1.germ,t1.iris,t1.jewi,t1.scot,t1.wels'

ALTER PROCEDURE [dbo].[test]
(
@Zipcode Varchar(50),
@Querystr Varchar(200)
)
AS
Begin
SET NOCOUNT ON

DECLARE @Result VARCHAR(MAX)

Set @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM Tbl_Origin WHERE Zip_Final =''' + @Zipcode + ''') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u1 ORDER BY theValue DESC'

IF @Result IS NULL OR @Result = ''
Begin
Set @Result = 'Select Top 1 theCol from Tbl_Origin'
End

--Exec(@Result)
Print @Result
End

G. Satish

Re: Error in Stored Procedure.

Vimalsoft(Pty) Ltd3-Aug-09 4:38

designing database tables to store data

3-Aug-09 4:38

Good day Satish

Becacause of time i have, i will not write you a solution , but i will point to a problem

First you use one variable to do all things. Why do you create one variable that will hold the SQl statement and one to store the results
and evauate the result. i think you know what you are doing but you get confused by your code. declare another variable to hold the results after that you must test for only nulls and remove the quotes and try it like this

DECLARE @Result VARCHAR(MAX)
DECLARE @Final_Res VARCHAR(MAX)
Set @Result = (SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM Tbl_Origin WHERE Zip_Final =@Zipcode ) AS t1 UNPIVOT (theValue FOR theCol IN (@Querystr)) AS u1 ORDER BY theValue DESC)

IF @Result IS NULL 

Set @Result = 'Select Top 1 theCol from Tbl_Origin'

else 

select @Result

Hope this Helps

uglyeyes2-Aug-09 14:57

uglyeyes

2-Aug-09 14:57

Hi! I need to do below note sure how can i design database as web application performance is a key to this

development scenario:

1. organisation adds up available fields (label and input field) that comes from synonym table
2. ability to add extra fields(label and input field)

synonym table as i think might have below cols not sure..

SID, SNAME, ORGID, ORGWGID, datatype
--------------------------------------
1, firstname, null, null, string - generic
2, lastname, null, null,string - generic
3, initialname, 1,1,string - orgid 1 specific
4, givenname,1,1,string
5,fname,2,2,string
6,surname,2,2,string

1. change synonym for existing default fields that will appear in the webform
2. add additional fields as per need for individual organisation (orgid=organisationId, orgwgid=organisation workgroup id)

please comment if my requirement is not clear. I am confused how should i design table as user might want to add extra form field i.e a label and textbox/checkbox/listbox/combo/moneyfield..all 27 datatypes available in sql. now I dont know how to do this as there will be about 20 organisation that might use this web application. thanks in advance.

Re: designing database tables to store data

Henry Minute3-Aug-09 2:09

Henry Minute

3-Aug-09 2:09

I have a cunning plan!

Use two (or possibly more) tables, I haven't completely thought this out. Smile | :)

First table for Generic data.

Second table:

OrganizationID
NameForAdditionalField
TypeForAdditionalField
Length...
etc

I hope that you get the idea. One table contains the 'required' fields and the other table(s) allow you to organise the requirements for each client.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Accessing the file of a T-SQL BACKUP call

Uwe Keim2-Aug-09 5:52

Uwe Keim

2-Aug-09 5:52

Trying to provide a backup "button" inside the C# Windows Forms application on a client PC that connects to a SQL Server database, I want to grab the resulting backup file of a call to the BACKUP DATABASE [^] T-SQL function.

Since the BACKUP DATABASE function takes a file path that is relative to the SQL Server itself, I see no reliable way to get the resulting backup file from the client.

My ideal way would be:

1.) Call the BACKUP DATABASE function from my client application.
2.) Specify to backup to a local file (or even better: a C#/.NET stream[^]).
3.) Capture the resulting BAK file from my client application.

Question: Is this possible to achieve?

(I will cross-post this message to the C# forum, since I am unsure whether this is more an ADO.NET topic)

Thanks
Uwe

• My personal 24/7 webcam
• Zeta Test - Intuitive, competitive Test Management environment for Test Plans and Test Cases. Download now!
• Zeta Producer Desktop CMS - Intuitive, very easy to use. Download now!

Re: Accessing the file of a T-SQL BACKUP call

scottgp2-Aug-09 14:34

scottgp

2-Aug-09 14:34

On the link you included for the BACKUP command documentation, you'll see a section named "Backup History Tables". You can try reading those system tables - it looks like physical_name in the backupfile system table might have what you want.
Scott

Re: Accessing the file of a T-SQL BACKUP call

Uwe Keim2-Aug-09 19:14

Uwe Keim

2-Aug-09 19:14

Thank you, Scott!

I will try to investigate. The idea was to have the user do ZERO configuration, except specifying the connection string. Hopefully I will manage to do.

Cheers
Uwe

Re: Accessing the file of a T-SQL BACKUP call

Vimalsoft(Pty) Ltd3-Aug-09 4:50