Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts

Wednesday, March 28, 2012

How to use variables as a Counter?

While inserting data into a target table, I'm trying to populate a primary key ID field sequentially. For each record, the value of the primary key field needs to be incremented by one (a counter).

I've tried to use the RowCount transformation to store the values in a variable. I'm able to successfully do that; however, I don't know how to read or update the variable incrementally.

If someone knows how to perform this task, please let me know. I would greatly appreciate it.

I think this: http://www.sqlis.com/default.aspx?37 should do what you need.


As will this: http://www.sqlis.com/default.aspx?93

They both do the same thing. One uses script, one is a custom component.

-Jamie

|||

I used the script component and it does exactly what I need it to do.

Thanks, Jamie.

Monday, March 26, 2012

How to use value of a variable in defining data type

HI Experts,

I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare
Tables in both tha database and as per result i have to do insert,update or delete.

for that i have written query :

DECLARE @.rowcount_mastertable FLOAT
SET @.rowcount_mastertable = (select count(*) from master_table)

DECLARE @.TABLE_ID float,
@.TABLE_NAME varchar (100),
@.primary_key varchar (100),
@.Primarykey_DATATYPE varchar (50),

DECLARE @.COUNTER FLOAT
SET @.COUNTER = 1

WHILE (@.Counter <= @.rowcount_mastertable)

Begin

SET @.TABLE_NAME = (SELECT TABLE_NAME FROM MASTER_TABLE TABLE_ID = @.COUNTER)
SET @.primary_key = (SELECT primary_key FROM MASTER_TABLE WHERE TABLE_ID = @.COUNTER)
SET @.Primarykey_DATATYPE = (SELECT Primarykey_DATATYPE FROM MASTER_TABL WHERE TABLE_ID = @.COUNTER)

--In below line i want to declare a variable and datatype should be same as what we got from master table so that i can use this @.MAX_primary_key to fetch max of primary key from table name where table id is 1

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE
SELECT @.MAX_primary_key = MAX(@.primary_key) FROM @.TABLE_NAME
WHERE TABLE_ID = @.COUNTER

--But by running it i am getting error that "Incorrect syntax near '@.Primarykey_DATATYPE'. and "Must declare the variable '@.MAX_primary_key'.

Please suggest

Thanks in Advance

Quote:

Originally Posted by aviansh

HI Experts,

I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare
Tables in both tha database and as per result i have to do insert,update or delete.

for that i have written query :


DECLARE @.rowcount_mastertable FLOAT
SET @.rowcount_mastertable = (select count(*) from master_table)

DECLARE @.TABLE_ID float,
@.TABLE_NAME varchar (100),
@.primary_key varchar (100),
@.Primarykey_DATATYPE varchar (50),

DECLARE @.COUNTER FLOAT
SET @.COUNTER = 1

WHILE (@.Counter <= @.rowcount_mastertable)

Begin

SET @.TABLE_NAME = (SELECT TABLE_NAME FROM MASTER_TABLE TABLE_ID = @.COUNTER)
SET @.primary_key = (SELECT primary_key FROM MASTER_TABLE WHERE TABLE_ID = @.COUNTER)
SET @.Primarykey_DATATYPE = (SELECT Primarykey_DATATYPE FROM MASTER_TABL WHERE TABLE_ID = @.COUNTER)

--In below line i want to declare a variable and datatype should be same as what we got from master table so that i can use this @.MAX_primary_key to fetch max of primary key from table name where table id is 1

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE
SELECT @.MAX_primary_key = MAX(@.primary_key) FROM @.TABLE_NAME
WHERE TABLE_ID = @.COUNTER


--But by running it i am getting error that "Incorrect syntax near '@.Primarykey_DATATYPE'. and "Must declare the variable '@.MAX_primary_key'.


Please suggest

Thanks in Advance



Examine this line more closely...

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE

at what point are you declaring either of these variable datatypes?

Jim :)

Wednesday, March 21, 2012

How to Use SMO to Create Primary Key

All,
I am using Microsoft Management Objects (SMO) to create a database with it's
associated tables.
All works well until I try to define more than one index as a primary key.
See code below:
// Add two primary key indices
Index pkIndex = new Index(myTable, "PK_MyTable");
pkIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex, "Column1"));
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex2, "Column2"));
// Add index to the table
myTable.Indexes.Add(pkIndex);
// Create table in database
myTable.Create(); // Throws exception!
Also tried it this way:
// Add two primary key indices
Index pkIndex = new Index(myTable, "PK_MyTable1");
pkIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex, "Column1"));
Index pkIndex2 = new Index(myTable, "PK_MyTable2");
pkIndex2.IndexKeyType = IndexKeyType.DriPrimaryKey;
pkIndex2.IndexedColumns.Add(new IndexedColumn(pkIndex2, "Column2"));
// Add index to the table
myTable.Indexes.Add(pkIndex);
myTable.Indexes.Add(pkIndex2);
// Create table in database
myTable.Create(); // Throws exception!
Does anyone know how to define more than one primary key for a table using
SMO?
Thanks in advance.All,
Disregard this question.
Problem was with a column definition, not with the primary key index creatio
n.
Note that the following code works:
// Add two primary key indices
Index pkIndex = new Index(myTable, "PK_MyTable");
pkIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex, "Column1"));
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex2, "Column2"));
// Add index to the table
myTable.Indexes.Add(pkIndex);
// Create table in database
myTable.Create();

Friday, February 24, 2012

How to use BETWEEN with custom-ordered values

We have a 10 digit primary key value in this format: M000123456. The
order for this key is first determined by positions 3 and 4 in this
example, then positions 1 and 2. So a brief sample of correct ordering
would look like this:
M000001501
M000011501
M000021501
M000001601
M000011601
M000021601

Now my question: how can I use a BETWEEN (or > and <) in my WHERE
clause to get a range of values for this column? I use the following
ORDER BY clause to control how the results are sorted, but I can't get
the same logic to work with BETWEEN in a WHERE clause.

ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)
How do I return values between M000011501 and M000011601 for example?On 19 Dec 2004 16:36:14 -0800, ian.proffer@.gmail.com wrote:

>We have a 10 digit primary key value in this format: M000123456. The
>order for this key is first determined by positions 3 and 4 in this
>example, then positions 1 and 2. So a brief sample of correct ordering
>would look like this:
>M000001501
>M000011501
>M000021501
>M000001601
>M000011601
>M000021601
>Now my question: how can I use a BETWEEN (or > and <) in my WHERE
>clause to get a range of values for this column? I use the following
>ORDER BY clause to control how the results are sorted, but I can't get
>the same logic to work with BETWEEN in a WHERE clause.
>ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)
>How do I return values between M000011501 and M000011601 for example?

Hi Ian,

Usually, this kind of requirement is a sign of a bad table design. I have
the suspicion that both the two digits in position 3 and 4 and the two
digits in position 1 and 2 have a specific meaning in your business. If
that is the case, you should probably store these as seperate columns. You
can always paste the different values together for outputting as one
column.

Anyway, based on your ORDER BY clause, the BETWEEN predicate would read

WHERE SUBSTRING(<columnname>, 7, 2) + SUBSTRING (<columnname>, 5, 2)
BETWEEN SUBSTRING('M000011501', 7, 2) + SUBSTRING ('M000011501', 5, 2)
AND SUBSTRING('M000011601', 7, 2) + SUBSTRING ('M000011601', 5, 2)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi

It may be easier if you stored the correctly ordered string rather than the
one used for display, and have the display string in a view/computed column
or part of the select statement.

You don't say how the rest of the string is arranged, but something like:

SELECT SUBSTRING(<fieldname>, 7, 2) + SUBSTRING (<fieldname>, 5, 2) +
SUBSTRING (<fieldname>, 1, 4) + SUBSTRING (<fieldname>, 9, 2),
* FROM <tablename>
WHERE SUBSTRING(<fieldname>, 7, 2) + SUBSTRING (<fieldname>, 5, 2) +
SUBSTRING (<fieldname>, 1, 4) + SUBSTRING (<fieldname>, 9, 2) between
SUBSTRING('M000011501', 7, 2) + SUBSTRING ('M000011501', 5, 2) + SUBSTRING
('M000011501', 1, 4) + SUBSTRING ('M000011501', 9, 2)
and SUBSTRING('M000011601', 7, 2) + SUBSTRING ('M000011601', 5, 2) +
SUBSTRING ('M000011601', 1, 4) + SUBSTRING ('M000011601', 9, 2)
ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)

will give your result.

John

<ian.proffer@.gmail.com> wrote in message
news:1103502974.453433.144600@.f14g2000cwb.googlegr oups.com...
> We have a 10 digit primary key value in this format: M000123456. The
> order for this key is first determined by positions 3 and 4 in this
> example, then positions 1 and 2. So a brief sample of correct ordering
> would look like this:
> M000001501
> M000011501
> M000021501
> M000001601
> M000011601
> M000021601
> Now my question: how can I use a BETWEEN (or > and <) in my WHERE
> clause to get a range of values for this column? I use the following
> ORDER BY clause to control how the results are sorted, but I can't get
> the same logic to work with BETWEEN in a WHERE clause.
> ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)
> How do I return values between M000011501 and M000011601 for example?|||Thanks for the reply Hugo. (I don't usually multi-post, btw,
but...sorry.) Your solution works great, even though I didn't
accurately post my sample data (where M000251501 is followed by
M000001601).

And oh, if only I could redesign the table! Out of my control however
with this application <sigh>.

Thanks again,
-- Ian