copy database: column

Showing posts with label column. Show all posts

Friday, March 30, 2012

How to Create Rank column

If I have a column like this

value

34
45
54

How can I write an expression to create corresponding Rank column.

value Rank
--
34 3
45 2
54 1
Thanks
well, Lest this thread should remain hanged...I have solved this at the stored procedure level. I did not find any means by which I could do it in the reporting server.

Thanks
Ragz.|||Why couldn't you sort the column by value and use the RowCount() function?|||I have the same problem.
I have many Cognos Reports to be converted in Reporting Services.
Out of which there are several Ranking Reports.
Cognos has inbuilt Rank Function while SSRS does not have.
I dont want to do grouping on Database level, rather trying to find a solution in RS.
But unfortunately I couldnt find any solution.
I sorted the group according to requirement, now only thing left is showing the rank number and limiting top 60 rows only.
I tried filters to show TopN, but always gives and error and also tried custom code to show the row number for that group but didn't work.

Anyone has any idea how to overcome this limitation of Reporting Services.
One more interesting is when I try to create a field in dataset having IIF and / within.. RS terminates, all unsaved work gone.
Sometimes I think Microsoft cannot have a product which is 100% perfact and working.

|||

If you are using SQL Server 2005 and creating based on a query you can certainly use the row_number() function; something like

Code Snippet

select value,

row_number() over

( order by value

) as rank

from yourTable

|||I wanted to implement the Rank function in Reporting Services but I couldn't,
so the only solution left is at database level, so I grouped everything in PL-SQL query itself.
I also used Partition by clause in Rank function as per the report requirement.

Anyway thank you guys for your help, I hope microsoft would add more features in its upcoming 2008 products.

Nik|||

The rowcount() feature in RS as suggested above might be able to solve this, as well as the Top N issue. I currently use it to shade every other row by combining it with Mod.

|||rowcount() and TopN in RS are for simple reports, I have very complex reports and the rank() function in PL/SQL is working perfact for me.
And as I said RS (visual studio) terminates when you add a new formula column in a dataset which has sum and '/' within IIF expression.

How to Create Rank column

If I have a column like this

value

34
45
54

How can I write an expression to create corresponding Rank column.

|||

If you are using SQL Server 2005 and creating based on a query you can certainly use the row_number() function; something like

Code Snippet

select value,

row_number() over

( order by value

) as rank

from yourTable

Anyway thank you guys for your help, I hope microsoft would add more features in its upcoming 2008 products.

Nik|||

The rowcount() feature in RS as suggested above might be able to solve this, as well as the Top N issue. I currently use it to shade every other row by combining it with Mod.

How to Create Rank column

If I have a column like this

value

34
45
54

How can I write an expression to create corresponding Rank column.

value Rank
--
34 3
45 2
54 1
Thankswell, Lest this thread should remain hanged...I have solved this at the stored procedure level. I did not find any means by which I could do it in the reporting server.

If you are using SQL Server 2005 and creating based on a query you can certainly use the row_number() function; something like

Code Snippet

select value,

row_number() over

( order by value

) as rank

from yourTable

Anyway thank you guys for your help, I hope microsoft would add more features in its upcoming 2008 products.

Nik|||

The rowcount() feature in RS as suggested above might be able to solve this, as well as the Top N issue. I currently use it to shade every other row by combining it with Mod.

Wednesday, March 28, 2012

How to Create Rank column

If I have a column like this

value

34
45
54

How can I write an expression to create corresponding Rank column.

|||

If you are using SQL Server 2005 and creating based on a query you can certainly use the row_number() function; something like

Code Snippet

select value,

row_number() over

( order by value

) as rank

from yourTable

Anyway thank you guys for your help, I hope microsoft would add more features in its upcoming 2008 products.

Nik|||

The rowcount() feature in RS as suggested above might be able to solve this, as well as the Top N issue. I currently use it to shade every other row by combining it with Mod.

How to Create Rank column

If I have a column like this

value

34
45
54

How can I write an expression to create corresponding Rank column.

|||

If you are using SQL Server 2005 and creating based on a query you can certainly use the row_number() function; something like

Code Snippet

select value,

row_number() over

( order by value

) as rank

from yourTable

Anyway thank you guys for your help, I hope microsoft would add more features in its upcoming 2008 products.

Nik|||

The rowcount() feature in RS as suggested above might be able to solve this, as well as the Top N issue. I currently use it to shade every other row by combining it with Mod.

How to create PK in duplicate column?

Hi,

How to create PK in duplicate column?

for example:

id fname lname age departments

1 aaa aaa NULL NULL
3 aaa bbb NULL NULL
7 aaa b NULL NULL

...

I want to create PK on fname column.

you can't...how would the foreign key know where to link to....row 1 or row 2 or row 3 or all of them?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Primary Keys are by definition "unique". You cannot have duplicates.

From your example, a index on fname is what you want.|||

Thanks,

So the question becomes "How to delete duplicate records"?

|||

fastest way would be (change realtable to your table name)

select distinct * into backuptable

from realtable

truncate realtable

insert into realtable

select * from backuptable

drop table backuptable

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Hi,

the results are same when using select distinct * from table1

select distinct * from table1

1 aaa aaa NULL NULL
3 aaa bbb NULL NULL
7 aaa b NULL NULL

|||

The answer i gave you won't work because of the id column and the rest is not unique either

what the code below does is get the min id per fname and dumps it into a temp table

then we delete from the real table everything that is not in the temp table

I used min, you can use max

select min(id),fname into #temp
from YourTable
group by fname

delete YourTable
where not exists (select * from #temp where YourTable.id = #temp.id)

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

you are only allowed one PK per table! you can have multiple columns as a primary key however.

sql server2000 wrote:

Hi,
How to create PK in duplicate column?
for example:
id fname lname age departments
1 aaa aaa NULL NULL
3 aaa bbb NULL NULL
7 aaa b NULL NULL
...
I want to create PK on fname column.

Monday, March 26, 2012

how to create key time column and key column for a case table and a nested table for time series

Hi, all experts here,

Thanks for your kind attention.

I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Can you please clarify if you're creating an OLAP mining model or a relational mining model?

Thanks

|||

Hi, Shuvro,

At the moment I am trying a create a relational mining model.

But it will be brilliant to hear from you for both how to create an OLAP mining model as well using case table and nested table with Time Series Algorithm.

Thanks and I am looking forward to hearing from you further for your advices.

With best regards,

Yours sincerely,

|||

Can you please post the schema of your case and nested table for the relational scenario?

For the OLAP mining model, the design would be on the same lines as the relational, but instead using the dimension attribute and the measure. The TIME KEY usually can be selected from your date dimension, depending on the granularity of your data, but you'll have to do some work to make it correctly sortable. (e.g. Month names are not a valid time key).

|||

You can mine just the fact table and ignore the date table. The fact table needs to have the date value anyway. Your model would look something like this

CREATE MINING STRUCTURE TS_Structure

(

[Date] LONG KEY TIME,

[Region] TEXT KEY,

Value1 DOUBLE CONTINUOUS PREDICT,

Value2 DOUBLE CONTINUOUS PREDICT

)

ALTER MINING STRUCTURE ADD MINING MODEL TS_Model

(

[Date],

[Region],

Value1 PREDICT,

Value2 PREDICT

) USING Microsoft_Time_Series

When using the tools, you would simply mark your source table as the "Case" table (not nested) and then mark both the [Date] column and the [Region] column as keys. The tools will figure it out.|||

Hi, Jamie,

Thanks for the advices.

With best regards,

Yours sincerely,

How to Create Index

If I run query mostly on column expressions, like I have two columns in table
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:
> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:
> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > If I run query mostly on column expressions, like I have two columns in
> > table
> > one for date and other is for time, when i want to build index, then i
> > build
> > index on both columns, but will these separate index work when i write
> > query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be
> > utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
> > If I run query mostly on column expressions, like I have two columns in table
> > one for date and other is for time, when i want to build index, then i build
> > index on both columns, but will these separate index work when i write query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
>> The expression is not sargable because you are applying functions to the
>> column value. If your data contains the default values for the time and
>> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
>> WHERE
>> saleDate = '20050101' AND
>> saleTime = '11:30:00'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Imran" <Imran@.discussions.microsoft.com> wrote in message
>> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>> > If I run query mostly on column expressions, like I have two columns in
>> > table
>> > one for date and other is for time, when i want to build index, then i
>> > build
>> > index on both columns, but will these separate index work when i write
>> > query
>> > with this expression,
>> > where convert(varchar(10),saleDate,121) + ' ' +
>> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
>> > both columns have data type of datetime.
>> > My question is, will the separate indexes of both columns will be
>> > utilized'
>> > Or what is the best practice, when you are searching on expressions in
>> > queries? not on just simply column.
>> >
>> >
>> >
>>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) ='12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:
> > The expression is not sargable because you are applying functions to the
> > column value. If your data contains the default values for the time and
> > date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> >
> > WHERE
> > saleDate = '20050101' AND
> > saleTime = '11:30:00'
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Imran" <Imran@.discussions.microsoft.com> wrote in message
> > news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > > If I run query mostly on column expressions, like I have two columns in
> > > table
> > > one for date and other is for time, when i want to build index, then i
> > > build
> > > index on both columns, but will these separate index work when i write
> > > query
> > > with this expression,
> > > where convert(varchar(10),saleDate,121) + ' ' +
> > > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > > both columns have data type of datetime.
> > > My question is, will the separate indexes of both columns will be
> > > utilized'
> > > Or what is the best practice, when you are searching on expressions in
> > > queries? not on just simply column.
> > >
> > >
> > >
> >
> >
> >sql

How to Create Index

Friday, March 23, 2012

How to create column in existing table

Hi i want to create a store procedure
i have table called ABC which has 10 column and have lot of data now i want
to design a store procedure to add another emplty column into that table
which i will fill with another store procedure that i already have thanksUse Alter Table..ADD
see example
CREATE TABLE ABC (id INT,SomeColumn VARCHAR(49))
SELECT * FROM ABC
ALTER TABLE ABC
ADD SomeOtherColumn int
SELECT * FROM ABC
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||You're going to do this once, or all the time? If this is something you're
going to be doing repeatedly, I'd strongly recommend reviewing your
requirements and coming up with a more sane implementation plan.
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:DB18DF5B-16CF-4918-A42D-8406A1617675@.microsoft.com...
> Hi i want to create a store procedure
> i have table called ABC which has 10 column and have lot of data now i
> want
> to design a store procedure to add another emplty column into that table
> which i will fill with another store procedure that i already have thankssql

how to create array column and how to retrive in sqlserver

i am using database sqlserver,
i am searching for varray concept like in oracle to store multiple values in a single column(as array column) like that shell i do in sql server

1.how to create array column in a table using sqlserver

if possible how can i use select query for that

there is no array concept in sql server. you can however store the values as a concatenated string with a delimiter and use some custom function to parse through the string to split them up.|||

Try the link below for samples using the string functions in SQL Server and Oracle PL/SQL is closer to C++ than T-SQL. I would also look at Ken Henderson books at my local bookstore run a search for String functions in the BOL(books online). Hope this helps.

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

|||Why do you want to store multiple values in a single column? Thishas some pretty huge implications from a relational modelingstandpoint, not to mention the fact that it's going to kill performanceif you ever need to query the thing. If you can post someinformation about what business problem you're trying to solve, I'mcertian we can help you find a better solution.

|||

Sorry I forgot I have a link with SQL Server Arrays. Try the link below. Hope this helps.

http://www.sommarskog.se/arrays-in-sql.html

How to create and retrieve a calculated column

Table: Names
Columns: Name_RID char(10)
Name_Type smallint
Name_Last char(50)
Name_First char(25)
Name_MI char

I have search for and see how to put the columns for the last, first mi together (Name_Last + ', ' + Name_First + ' ' + Name_MI) as Name
But how can I test the value of the Name_Type field to determine how the Name column looks
if Name_Type = 1 then
Name = (Name_Last + ', ' + Name_First + ' ' + Name_MI)
else
Name = Name_Last

The Name_Type represents Individual versus an Entity
0,ABC Pipeline,,
1,Williams, John,AHave you looked at the CASE statement?|||I will look into it thanks...|||Look no further

SELECT CASE WHEN Name_Type = 1 THEN Name_Last + ', ' + Name_First + ' ' + Name_MI
ELSE Name_Last
END AS [Name]sql

How to create and fill a random varbinary table?

Hi all,

For a test I'm performing I need to create a table with varbinary(120000) column.
I need to fill this table with 1000000 rows of random varbinary values. (The values should be from 0x1 till 0xfff....f)
Does anyone have a "nice" way to create this table?

Thanks in advance,
Shai.

You might be able to leverage (1) a table of numbers and (2) a scalar UDF that allows each row to have a different random results. Give a look to this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330539&SiteID=1

|||

You can do something like this:

set nocount on
go
alter function getHexCharacter
(
@.i int
)

--used to return a character 0-9 and A-F for inputs from 0-15
returns char(1)
as
begin
return char(case when @.i <= 9 then @.i + 48
when @.i > 9 and @.i <= 15 then @.i + 55
else null end)
end
go

create table #randomBinaryValue
(
value varbinary(2) constraint PKrandomBinaryValue primary key
)
go
declare @.i int, @.newValue char(4) set @.i = 0
while (1=1)
begin
--build up the text of the
select @.newValue =
'0x' + dbo.getHexCharacter(cast(rand() * 15 as int))
+ dbo.getHexCharacter(cast(rand() * 15 as int))

--add more getHexCharacters to get a longer result
-- + dbo.getHexCharacter(cast(rand() * 15 as int))

begin try
exec (' insert into #randomBinaryValue select ' + @.newValue)
set @.i = @.i + 1
end try
begin catch
select 'don''t update the counter',error_message()
end catch

if @.i > 100 break
end

select *
from #randomBinaryValue
go
drop table #randomBinaryValue

|||

If you really want to fill the random numbers between the given range the following query is more enough..

Code Snippet

CreateTable #randomBinaryValue

(

valuevarbinary(32)constraint PKrandomBinaryValueprimary key nonclustered --Just for verfication/avoid duplicate

);

SETNOCOUNTON;

Declare @.Iasint;

Set @.I= 0;

WHILE @.I< 10000

Begin

InsertINto #randomBinaryValuevalues(convert(varbinary,newid()));

Set @.I= @.I+ 1;

End

Select*From #randomBinaryValue

DropTable #randomBinaryValue

|||

All the ideas are nice but...

casting newid will only generate me a 16 byte varbinary and I need 120000 byte varbinary.

create a 120000 varbinary with a loop and then inserting it into a table will take a long time for 1000000 rows...

Does any one have a quicker way?

Thanks,

Shai.

|||

Shai:

I used this query as the basis to generate the script that follows:

Code Snippet

select ' convert(binary(16), newid()) + '
from small_iterator (nolock)
where iter <= 500

What I found when I ran my mockup is that on my desktop server it takes about 67 ms to create each varbinary(max) record with a length of 120000. Therefore, just to generate 1000000 rows of such on my desktop unit might take on the order of 18 hours! The problem is that creating very long VARBINARY(MAX) data takes a fair amount of time. I used a query like this:

Code Snippet

declare @.begDt datetime set @.begDt = getdate()

declare @.loopLimit integer set @.loopLimit = 10

declare @.loopCt integer
declare @.hugeVarbinary varbinary (max)

set @.loopCt = 1
while @.loopCt <= @.loopLimit
begin

select @.hugeVarbinary =
( select convert(binary(16), newid()) +
convert(binary(16), newid()) +
...

convert(binary(16), newid()) +
convert(binary(16), newid())
)

select @.hugeVarbinary = @.hugeVarbinary +
( select convert(binary(16), newid()) +
...

convert(binary(16), newid()) +
convert(binary(16), newid())
)
from small_iterator (nolock)
where iter <= 14

set @.loopCt = @.loopCt + 1

end

print ' '
select @.loopLimit as [@.loopLimit],
datediff (ms, @.begDt, getdate()) as [Elapsed Time]

/* Times in seconds
10 Records: 0.690 0.686 0.673 Average: 0.686
100 Records: 6.966 6.766 6.890 Average: 6.874
1000 Records: 68.030 67.156 67.436 Average: 67.541
10000 Records: 681.720
*/

|||

Honestly, if you really only just need a random varbinary value, why not just build each new one on the fly? Somthing like this:

create view randomVarbinary as

select

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) as crazyBigVarbinary

select *

from randomVarbinary

How to create and fill a random varbinary table?

Hi all,

Thanks in advance,
Shai.

You might be able to leverage (1) a table of numbers and (2) a scalar UDF that allows each row to have a different random results. Give a look to this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330539&SiteID=1

|||

You can do something like this:

set nocount on
go
alter function getHexCharacter
(
@.i int
)

--used to return a character 0-9 and A-F for inputs from 0-15
returns char(1)
as
begin
return char(case when @.i <= 9 then @.i + 48
when @.i > 9 and @.i <= 15 then @.i + 55
else null end)
end
go

--add more getHexCharacters to get a longer result
-- + dbo.getHexCharacter(cast(rand() * 15 as int))

begin try
exec (' insert into #randomBinaryValue select ' + @.newValue)
set @.i = @.i + 1
end try
begin catch
select 'don''t update the counter',error_message()
end catch

if @.i > 100 break
end

select *
from #randomBinaryValue
go
drop table #randomBinaryValue

|||

If you really want to fill the random numbers between the given range the following query is more enough..

Code Snippet

Create Table #randomBinaryValue

(

valuevarbinary(32) constraint PKrandomBinaryValue primary key nonclustered --Just for verfication/avoid duplicate

);

SET NOCOUNT ON;

Declare @.I as int;

Set @.I = 0;

WHILE @.I < 10000

Begin

Insert INto #randomBinaryValue values(convert(varbinary,newid()));

Set @.I = @.I+ 1;

End

Select * From #randomBinaryValue

Drop Table #randomBinaryValue

|||

All the ideas are nice but...

casting newid will only generate me a 16 byte varbinary and I need 120000 byte varbinary.

create a 120000 varbinary with a loop and then inserting it into a table will take a long time for 1000000 rows...

Does any one have a quicker way?

Thanks,

Shai.

|||

Shai:

I used this query as the basis to generate the script that follows:

Code Snippet

select ' convert(binary(16), newid()) + '
from small_iterator (nolock)
where iter <= 500

Code Snippet

declare @.begDt datetime set @.begDt = getdate()

declare @.loopLimit integer set @.loopLimit = 10

declare @.loopCt integer
declare @.hugeVarbinary varbinary (max)

set @.loopCt = 1
while @.loopCt <= @.loopLimit
begin

select @.hugeVarbinary =
( select convert(binary(16), newid()) +
convert(binary(16), newid()) +
...

convert(binary(16), newid()) +
convert(binary(16), newid())
)

select @.hugeVarbinary = @.hugeVarbinary +
( select convert(binary(16), newid()) +
...

convert(binary(16), newid()) +
convert(binary(16), newid())
)
from small_iterator (nolock)
where iter <= 14

set @.loopCt = @.loopCt + 1

end

print ' '
select @.loopLimit as [@.loopLimit],
datediff (ms, @.begDt, getdate()) as [Elapsed Time]

/* Times in seconds
10 Records: 0.690 0.686 0.673 Average: 0.686
100 Records: 6.966 6.766 6.890 Average: 6.874
1000 Records: 68.030 67.156 67.436 Average: 67.541
10000 Records: 681.720
*/

|||

Honestly, if you really only just need a random varbinary value, why not just build each new one on the fly? Somthing like this:

create view randomVarbinary as

select

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) as crazyBigVarbinary

select *

from randomVarbinary

Wednesday, March 21, 2012

how to create an copy of a certain record except one specific column that must be different &

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

try this:

insert into your_table ( user , field2 , field3 , ... )

select 'user2', field2 , field3, ....

from your_table

where user = 'user1'

|||

I WANTED TO AVOID SPECIFYING ALL THE COLUMNS SINCE THERE ARE MANY COLUMNS.

THANKS.

|||

Here is a little trick

in Query Analuzer Or SSMS press F8, this will display the Object Explorer

Drill down to the table that you need, click on the Columns folder (hold the button down) and drag it into the query window

Now all your columns will be listed in the query window, just exclude the one that you don;'t want

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Beautiful trick thanks a lot for sharing it

how to create an copy of a certain record except one specific column that must be different

Hi
I have a table with a user column and other columns. User column id the primary key.

Thanks.

try this:

insert into your_table ( user , field2 , field3 , ... )

select 'user2', field2 , field3, ....

from your_table

where user = 'user1'

|||

I WANTED TO AVOID SPECIFYING ALL THE COLUMNS SINCE THERE ARE MANY COLUMNS.

THANKS.

|||

Here is a little trick

in Query Analuzer Or SSMS press F8, this will display the Object Explorer

Drill down to the table that you need, click on the Columns folder (hold the button down) and drag it into the query window

Now all your columns will be listed in the query window, just exclude the one that you don;'t want

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Beautiful trick thanks a lot for sharing it

Monday, March 19, 2012

how to create a sequence invoice number and insert or update to a column?

Hi, can anyone teach me how to automatic create a invoice number and insert or update it to a column?search in your nearest textbook or search engine for "sql server IDENTITY column"|||C'mon Rudy. You know what is coming next:

"I want my invoice number to have a product code in it, and I want it to look up the last number used for each product code and increment it."

How to create a relationship?

How do I create a relationship between two tables when the two columns that should connect each table have different names?

i.e. Table Person Column Name < -- > Table Employee Column Person Name

The two columns actually contain the same data type but different field name and size. I know this involves a series of steps. What are they? Could you please include some sample code.

A question for you, are you going to be enforcing this relationship via a constraint?

Scenario 1 - You are going to be enforcing this relationship.

SQL Server requires that both columns in a referential constraint have the same data type and length (size). They are not required to have the same name. If you are going to do this my recommendation is to use a SQL Server Management Studio 2005 to create the relationship while using diagram view, it will be easiser for a person who is just starting with SQL Server.

Scenario 2 - You are not going to be enforcing this relationship, you just need to query data ...

In this case you could just write a fancy T-SQL Select statement to get the data from the Database regardles of DataType, Length (size), or Field Names . The SELECT statement you would have to write would use a JOIN statement to specify that you are going to be querying the database for the information on two tables, and you would specify the fields (condition) to join the information by on the ON statement.

Ex.

SELECT *
FROM table1
JOIN table2
ON table1.field1 = table2.field2

Hope this helps,

Roberto Hernández-Pou
http://www.rhpconsulting.net

|||

open the database diagram

add the tables that you need

drag the field from the parent table to the child table.

this will create a relationship

requirements. the field from the parent table

should either have unique index or is a primary key

|||

Hi,

The basic requirements for establishing relationship(s) among tables is to set a logical DB design and then identify the objects:

Tables and their names (ENTITIES)

How to create a percent of variance column in a matrix

I have used SSRS 2005 to create a matrix and need to add a % of variance column. Is this possible? I hope I'm missing something simple. Basically the report should look like this:

Item CurVol PriorVol % of Var CurSales PriorSales % of Var

abc 100 90 11% 1250 990 26%

cde 96 128 -25% 192 243 -21%

Dataset looks this this:

Select item, vol, sales, 'Current' AS Per

FROM Table
WHERE (invoice_date >= @.curStartDate) AND (invoice_date < @.curEndDate + 1)
UNION

Select item, vol, sales, 'Prior' AS Per

FROM Table
WHERE (invoice_date >= @.priorStartDate) AND (invoice_date < @.priorEndDate + 1)

The column group on the matrix is using the Per column because the accounting periods cross over calendar months.

gusina

What is the calculation that you use to get % of variance?|||(Current - Prior) / Prior|||

Item CurVol PriorVol % of Var CurSales PriorSales % of Var

abc 100 90 11% 1250 990 26%

cde 96 128 -25% 192 243 -21%

OK so I am assuming you have the columns for current and prior with the data correct in the matrix. To get your % of Var column, just add a new column to the matrix. For the expression of that column use something like the following:

= (Fields!CurrentVol.Value - Fields!PriorVol.Value) / Fields!Prior.Value

Do similar to the above for Sales.

|||

The matrix for this project is structured with two data fields (Vol and Sales), one row group (item), and one column group (per) with values of Current or Prior. The available fields are Fields!Vol.Value and Fields!Sales.Value. How can the recommended expression be created?

Friday, March 30, 2012

How to Create Rank column

How to Create Rank column

How to Create Rank column

Wednesday, March 28, 2012

How to Create Rank column

How to Create Rank column

How to create PK in duplicate column?

Monday, March 26, 2012

how to create key time column and key column for a case table and a nested table for time series

How to Create Index

How to Create Index

How to Create Index

Friday, March 23, 2012

How to create column in existing table

how to create array column and how to retrive in sqlserver

How to create and retrieve a calculated column

How to create and fill a random varbinary table?

How to create and fill a random varbinary table?

Wednesday, March 21, 2012

how to create an copy of a certain record except one specific column that must be different &

how to create an copy of a certain record except one specific column that must be different

Monday, March 19, 2012

how to create a sequence invoice number and insert or update to a column?

How to create a relationship?

How to create a percent of variance column in a matrix

copy database

Blog Archive

About Me