How To Construct Table With Unique PK

Oct 31, 2006

Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate. Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks

View 8 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Convert Binary String To Table Value Construct?

Mar 21, 2014

Code snippet for changing '00001001001' into a table value construct containing (64,8,1)?

View 6 Replies View Related

Integration Services :: Load Incremental Data Into Fact Table When Source Table Not Have Timestamp And Unique Key

Sep 24, 2015

I have a transaction table having about 40 crore rows in source. It don't have timestamp and unique key columns. It have only Bill_month and Bill_Year columns. Actually for loading this table into staging I have added a new datetime column by adding default bill_date as 01. Then

* First we delete last 3 month data from staging tables.
* Get last 3 months data from source table.
* Load that 3 months data from source to staging table. 

We do this because we only get update for last three months data. Now I have to include this transaction table as Fact table in DW. What will be the best practice for loading the fact table by picking data form staging table. Also we have to look up with dimensions for Foreign Keys. 

* Should I implement the same method of deleting last 3 months records and loading them again. 

View 3 Replies View Related

How To Archiv Table To Another Table With Unique Number For All Rows Once + Date

Apr 28, 2008

need help
how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute)
i need whan i insert to the another table add 2 more fields (unique number , date_time )

this is the table 1 i select from
ID fname new_date val_holiday
----------------------------------------------------

111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1

222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3

333 ccc 03/04/2008 4
333 ccc 04/04/2008 4

this is the table 2 i insert into
----------------------------------
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 666 15/04/2008 17:03
111 aaaa 16/03/2008 1 666 15/04/2008 17:03
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 666 15/04/2008 17:03
111 aaaa 20/03/2008 1 666 15/04/2008 17:03
111 aaaa 21/03/2008 1 666 15/04/2008 17:03

222 bbb 02/05/2008 3 666 15/04/2008 17:03
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3 666 15/04/2008 17:03
222 bbb 05/05/2008 3 666 15/04/2008 17:03
222 bbb 06/05/2008 3 666 15/04/2008 17:03
222 bbb 07/05/2008 3 666 15/04/2008 17:03
222 bbb 08/05/2008 3 666 15/04/2008 17:03
222 bbb 09/05/2008 3 666 15/04/2008 17:03

333 ccc 03/04/2008 4 666 15/04/2008 17:03
333 ccc 04/04/2008 4 666 15/04/2008 17:03

for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute)

next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 667 15/04/2008 17:15
111 aaaa 16/03/2008 1 667 15/04/2008 17:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 667 15/04/2008 17:15

.........................
.....................................................................667 15/04/2008 17:15


next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 668 15/04/2008 08:15
111 aaaa 16/03/2008 1 668 15/04/2008 08:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 668 15/04/2008 08:15

.........................
.....................................................................668 15/04/2008 08:15



TNX

View 3 Replies View Related

SQL Construct

Dec 19, 2006

Hi all,

I had a question regarding SQL construct.

I had a table named "info" and I would like to show Lot_Id as distinct. Besides that , I would also like to show wafer_id and wafer_starttime. May I know how to construct this using select statement?

Thanks

View 1 Replies View Related

Can Someone Help Me Construct A SQL Command....

Oct 18, 2005

My head hurts...it might be an easy one for many people, but cant seem to figure a way to do it.Ok, I have a table with a field called Ad_Price.  This field is a nvarchar one, theres many different value in it, all of them corresponding to a price.  Now, what I want to do is only get the row that are not written in a good price format (like123$  is wrong, but 123.00 is good, but only value following this exemple)For exemple, if I have all these value145.876785.34654$45 to negociate1bvcaa0.01876.556the value I want to have are:654$45 to negociate1bvcaa876.556because they dont follow the format I wantso im not sure if my explication was clear enough..all in all what I want to complete is the WHERE part of my SQL instruction...what should I put in:SELECT *FROM AdsWHERE ???????thansk for taking the time to read this

View 2 Replies View Related

CASE SQL Construct

Sep 27, 2005

I am using the following code to construct an SQL 7.0 View Column

"CASE WHEN [DailyHours] > SUM([TransAmt]) THEN 0 ELSE 1 END"

and it works great!!

However, when I try the same line in SQL 2000, I get the message "The query designer does not support the case SQL construct"

The help screen is no help as all it says is "the syntax you entered is valid but is not supported visually by Query Designer. Be sure the verify your syntax before saving."

It will not let me save, so I'm not sure what to do from here now????

View 8 Replies View Related

Construct Variable Name?

Mar 16, 2006

Can you contruct a variable name from another variable? For example, I want the following to PRINT 10


DECLARE @var1 INT
DECLARE @var2 INT

SET @var1 = 10
SET @var2 = 20

PRINT '@var' + '1'


This prints the variable name, not the contents of the var. I tried to parse it with square brackets, but no luck.

Thanks,
Carl

View 6 Replies View Related

Construct A SelectCommand From Three VB Variables In ASP.NET 2.0

Jun 18, 2008

Dear Group,
I'm upgrading a "classic" ASP app to ASP.NET 2.0.  The database is stored on a SQL Server box in-house.  I'm looking to inject a bit of flexibility into the SQLDataSource SelectCommand property.
What I mean is this: depending on a selection a user makes in the form, I'd like to present a GridView object that displays data filtered by the user's selection.
So I took the original long query string from ASP and broke it in two pieces.  The first piece contains the bulk of the query and it ends just after the WHERE clause.  The second piece is the ORDER BY clause.  I'd like to have the ability to inject an additional term at the end of the WHERE clause (using AND).  That would be the filter.
For example,SELECT LastName, FirstName, InOut, TimeEntered, PhoneExt
FROM TimeClock
WHERE TimeEntered > '6/17/2008'
AND EmploymentState > ''
ORDER BY LastName, FirstName
So, in between the "AND State > '' " and the ORDER BY clause I'd like to insert something like this:AND Department = 'Production'
I'd like the SelectCommand of the SQLDataSource to look like this:SelectCommand="<%= (sql1 & " AND Department = 'Production' " & sql2) %>"
sql1 and sql2 are defined in a <script runat="server"></script> block in the page <head>. The following errors occur even if I have a single variable containing the whole SQL query string (i.e., SelectCommand="<%= sql1 %>").
When I try to run the page I get this error:Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '<'.
If I remove the quotation marks from around the SelectCommand statement I get:Parser Error Message: Server tags cannot contain <% ... %> constructs.
What's funny about this is that the ConnectionString property of the SQLDataSource looks like this:ConnectionString="<%$ ConnectionStrings:PersonnelDatabaseConnectionString %>"
No complaints from the compiler about the ConnectionString.
Any ideas? Am I simply going to be unable to construct a SelectCommand on-the-fly?

View 9 Replies View Related

How To Construct SQL SELECT Statement

Dec 31, 2003

I want to search an entire table for a particular keyword but i'm not sure how, if the keyword was TEST then I want to return rows where any of the fields contain TEST, THIS IS A TEST, PLEASE TEST THIS etc etc i.e. the keyword can be anywhere in the fields value

I believe I need to use the LIKE clause but i'm not sure how.

Thanks

Ben

View 4 Replies View Related

SQL Construct For Filtering Data

Nov 15, 2005

Hi,I have three dropdown lists branch, month & year. Based on these I need to retrieve data in my Datagrid.I have written the following SQL statement & code in my Refreshgrid():       SqlCmd.CommandText  = " SELECT * "       SqlCmd.CommandText += " FROM HSECData"       SqlCmd.CommandText += " where branchno=" &Branchno       SqlCmd.CommandText += " And yearno=" &YearNo       SqlCmd.CommandText += " And monthno=" &monthNoThis is working fine, initially the datagrid is empty and then displays data when I select say, branch- Melbourne, Year-2005, Month-October  and other exisiting combinations.The problem I am stuck at is when the page loads, initially I want all the data to be displayed in datagrid, i.e. for 'All' branches, 'All' years and 'All' months and then when I select combinations of these dropwdown lists as above the data should be filtered and displayed in those selected combinations. but as of now I get and empty datagrid when I select 'All' from these 3 drop down lists or when the page opens the first time.Can someone kindly guide me with the correct SQL cinstruct to achieve both the requirements?Thanks,Aartee.P.S. I am sorry if I have posted this question in a wrong category in the forum, I was unsure whether I should post it in Datagrid category or SQL server category, I thought this was my best bet.My aplogies.

View 1 Replies View Related

The NOT SQL Construct Or Statement Is Not Supported.

Sep 19, 2007

I need to transmit data from a ##table to an .xls file. The data is exported only for the first time. However, after a while the table will be dropped by SQL Server 2005 automatically.


I'm using Execute SQL Task to check for the existence of an ##Table. And, if does not exists the ##table should be created or something like the example below.

Ex:
IF NOT EXISTS (
SELECT *
FROM ##StateProvince
)

SELECT * INTO ##StateProvince
FROM Person.StateProvince
GO


To keep the session of the ## Table active, but the following error is thrown :

The NOT SQL construct or statement is not supported.

View 4 Replies View Related

Best Way To Construct Dynamic Queries

Apr 17, 2008

I am working with a form that I wish to construct a dynamic query from the results of. The forum has a date range and two radio buttons. Each radio button enables a list of items that can be clicked. So for example, if we assume the question,

"What is your favorite food, and what toppings do you like on it?" where the radio buttons are foods, the list boxes are toppings. Assuming the user can choose a Hamburger or a Salad with generic toppings, their choices are as such:

They can choose a Hamburger, with every topping
They can choose a Hamburger with a single topping.
They can choose a Hamburger with multiple toppings.
They can choose a Salad with the same combinations as above.
They cannot choose both a Hamburger and a Salad - mutually exclusive items.

Then, I wish to construct a query that, based on the conditions above, retrieves information relavent to their criteria, such a the number of food items to choose from, their price, etc. - basic information. What is the most efficient way to do this? Should I write a stored procedure with numerous conditionals and all available parameters, constructing the sproc as such:





Code Snippet

CREATE PROCEDURE GetFoodInfo
@from datetime,
@to datetime,
@FoodType varchar(20),

@toppings varchar(20)

AS

BEGIN
DECLARE @query varchar(300)
SET @query = 'SELECT COUNT(DISTINCT ' + @FoodType + ') '


IF @FoodType = 'Hamburger'
SET @query = @query + 'FROM Hamburgers '

ELSE
SET @query = @query + 'FROM Salads '

IF @toppings <> 'ALL'

SET @query = @query ' WHERE Toppings = ' + @toppings



EXEC (@query)

Apologies of this syntax is incorrect, but you get the general idea. Of course, this is a small example - in reality, I would have 5-10 conditional requirements Or, should I generate a stored procedure (or simple query) for each operation? For example, assuming each is a stored procedure:

GetHamburgers <-- would get Hamburgers with all toppings
GetHamburgersWithToppings
GetSalads
GetSaladsWithToppings

What is the best method for what I wish to achieve? What is fastest? Is there a better way than I have listed? Thank you.

Again, this is a small example, but I hope someone can help.

View 3 Replies View Related

The OVER SQL Construct Or Statement Is Not Supported.

Jun 14, 2007



Hi there,



I'm trying to run following script in sql command mode in OLE DB Source and it giving me "The OVER SQL construct or statement is not supported." error msg.






Code Snippet

Drop

Table #Tmp_EXT_AATransactions

Select row_number() over (partition by A3.jrnentry,A32.aaGLDistID order by A32.aaGLDistID,A33.aaTrxDimID ) as rownum,

A33.aaTrxDimID,

A33.aaTrxCodeID,

A3.jrnentry,

A32.aaGLDistID,

G2.TRXDATE,

A3.GLPOSTDT,

A3.aaTRXType,

A31.ACTINDX,

A32.DEBITAMT,

A32.CRDTAMNT,

(A32.DEBITAMT - A32.CRDTAMNT) AS Amount

into #Tmp_EXT_AATransactions

From dbo.AAG30000 A3

Inner Join dbo.AAG30001 A31

On A3.aaGLHdrID = A31.aaGLHdrID

Inner Join dbo.AAG30002 A32

On A31.aaGLHdrID = A32.aaGLHdrID

And A31.aaGLDistID = A32.aaGLDistID

Right Outer Join dbo.AAG30003 A33

On A32.aaGLHdrID = A33.aaGLHdrID

And A32.aaGLDistID = A33.aaGLDistID

And A32.aaGLAssignID = A33.aaGLAssignID

Inner Join dbo.GL20000 G2

On A3.jrnentry = G2.jrnentry

And A31.ACTINDX = G2.ACTINDX

And A31.SEQNUMBR = G2.SEQNUMBR

--Where A3.jrnentry in ( '54227','54222','54225')

Select [JrnEntry],

[aaGLDistId],

[TrxDate],

[GLPostDT],

[aaTrxType],

Isnull([1],0) as Dim1,

Isnull([2],0) as Dim2,

Isnull([3],0) as Dim3,

Isnull([4],0) as Dim4,

Isnull([5],0) as Dim5,

Isnull([6],0) as Dim6,

Isnull([7],0) as Dim7,

Isnull([8],0) as Dim8,

Isnull([9],0) as Dim9,

Isnull([10],0) as Dim10,

[ActIndx],

[DEBITAMT],

[CRDTAMNT],

[Amount]

into #Tmp_EXT_AATransactions_Final

From

(

Select [aaTrxDimID],

[aaTrxCodeID],

[aaGLDistID],

[JrnEntry],

[TrxDate],

[GLPostDT],

[aaTrxType],

[ACTINDX],

[DebitAmt],

[CRDTAMNT],

[Amount],

Row_Number() Over (Partition By [aaTrxDimID],[aaGLDistID],[JrnEntry] Order By [aaTrxDimID],[aaGLDistID],[JrnEntry]) RowId

From #Tmp_EXT_AATransactions

) as Data

Pivot

(

Max([aaTrxCodeID]) For [aaTrxDimID] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])

) As PVT

Select *

From #Tmp_EXT_AATransactions_Final



I can parse and preview it successfully in OLE DB Source Editor but i can't see any columns in columns mapping. So when i click on Build Query in OLE DB Source Editor i get this error message. Also Its working fine in Management Studio so don't know what's the problem.



Regards,



Vivek

View 3 Replies View Related

Unique PK In Each Table

Oct 30, 2006

Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate.  Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks 

View 13 Replies View Related

Unique Id In More Than One Table

Jun 6, 2007

Hello,



I have a little problem with DB, I'm using MS SQL 2005 Express edition.

I have a few tables in my DB, every table contains a list of objects and I need unique ID for every object, I can do it for one table, but not for more then one.



Is there any solution for this problem?



Thanks

John

View 2 Replies View Related

How To Programmatically Construct The UpdateCommand For SqlDataSource

Aug 1, 2007

Hi, All
I'm using Gridview and SqlDataSource to dynamically display the contents in different tables, as followed:
<%   dataSource.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"];   dataSource.SelectCommand = "SELECT * FROM " + tableName;   gridView.DataBind();
   dataSource.UpdateCommand = "";%>
<asp:SqlDataSource ID="dataSource" runat="server"></asp:SqlDataSource><asp:GridView ID="gridView" runat="server" DataSourceID="dataSource" AllowPaging="True" AllowSorting="True" AutoGenerateEditButton="True" BorderColor="Silver" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" PageSize="20" DataKeyNames="ID" OnRowDataBound="tableGridView_RowDataBound">    <HeaderStyle BackColor="#C0C0FF" />    <AlternatingRowStyle BackColor="#C0FFC0" /></asp:GridView>
The datasource take the "tableName" as argument to determine which table to display. My problem is I can't figure out a way to programmatically construct the UpdataCommand for the SqlDataSource. I try to get the field names from the HeaderRow, but all the cells are empty in this row.
Does anyone know what causes the problem or how to construct the UpdateCommand properly. Thanks!

View 3 Replies View Related

How To Construct The Query In Stored Proc

Jun 20, 2005

Hi,In the past I build up the query string within VB.NET page and easy to add the filtering statements in the SQL statement since it is just a string. For example, if user selected an option then include it in the filter, otherwise, just return all rows from table:Codes in ASPX.VB:   Dim SQL as String="SELECT * From Table1"   IF UserOption <> Null then      SQL = SQL & " WHERE Column1=" UserOption"   End ifNow, since I have a complicated page which need to use Stored Procedure to manapulate a temporary table before the final result. But I found when I want to add some user options similar to above, I found I don't know how to do it in Stored Procedure. In the Stored Procedure Property screen, I can't insert a IF..THEN statement within a SELECT statement. Seems I can only check the user option first and then determine the SELECT statement to use. That is: IF UserOption THEN SELECT statement 1 ELSE SELECT statement 2.But it is impossible for me to do this way since I'm not only one user option on the page. User usually can have several filters/selections on his screen. So if check which user option(s) are selected and write a static SELECT statement for it, I will have to program a complicated store procedure to cater all combinations for all user options (where some options may be null).Hope you can understanding what my mean and give me advices.Regards,Raymond

View 8 Replies View Related

HELP On Construct Of Update Stored Proc. .. THANKS!!!

Nov 6, 1998

Referencing the sample stored procedure
below that updates table 'this_table' containing
3 INT columns: 'key', 'col2', 'col3'.

How do I revise the stored procedure so that *** I do not
have to specify ALL the columns *** to update just one column?

e.g. if I want to update @col3 to 9 (exec sp_update_table, @col3 = 9),
how can I set col2 to its existing value?

create proc sp_update_table
@key int,
@col2 int,
@col3 int
as
update this_table
set col2 = @col2,
col3 = @col3
where key = @key
go

View 1 Replies View Related

How To Construct Dynamic Sql In Stored Procedure

Jul 30, 2007

Hi Guys.

I want to construct a SQL in stored procedure in the following way. Please guide me how to achive this



CREATE PROCEDURE P_SAMPLE

(

@P_ONE NVARCHAR(240)

)

AS

BEGIN

DECLARE

@TMP_CNT INT

BEGIN

EXEC ('SELECT @TMP_CNT = 1')

-- PRINT @TMP_CNT

END

END



Regards
Mani


View 2 Replies View Related

The PIVOT SQL Construct Or Statement Is Not Supported.

Oct 25, 2006

I am trying to use a Pivot T-SQL statement in the Table Adapter Configuration Wizard in Visual Studio 2005. I get the error message "The Pivot SQL construct or statement is not supported". Then it executes the SQL statement as if there were no error. Unfortunately, it will not create the table adapter because of the error. Below is the T-SQL statement I am using.



SELECT OrderNumber, OrderDate, custlastname, [1001] AS Dept01, [1002] AS Dept02, [1003] AS Dept03, [1004] AS Dept04, [1005] AS Dept05, [1006] AS Dept06, [1007] AS Dept07, [1008] AS Dept08, [1009] AS Dept09, [1010] AS Dept10, [1011] AS Dept11, [1012] AS Dept12
FROM

(SELECT Orders.Ordernumber, Orders.OrderDate, Customer.custlastname, DeptID, OrderDetail.DetailAmount
FROM OrderDetails od JOIN
Orders ON Orders.OrderNumber = od.OrderNumber JOIN
Customer ON Orders.CustomerID = Customer.CustID
WHERE (DATEPART([Year], OrderDate) = '2006')) p

PIVOT (SUM(OrderDetailAmount) FOR DeptID IN ([1001], [1002], [1003], [1004], [1005], [1006],
[1007], [1008], [1009], [1010], [1011], [1012])) AS pvt
ORDER BY OrderNumber

The statement works fine in Management Studio so I know the syntax is correct.

Any Ideas?

Thanks,

James



View 4 Replies View Related

Reseting The Unique Id If A Table.

Jul 26, 2007

I'm building a web site. there is a database.
 
I've set a primary key and unique of a field.also I set it to auto numbering.
 Everytime I insert a record , that field will increase 1 (type bigint , start from 1).
 
After lots time of inserting record , the id going to be larger number.. I wondering how can I reset that to zero?

View 1 Replies View Related

Unique Identifier Or Int For Table ID

Feb 29, 2008

I am working on some tables and would like to know which is best way to go when deciding what Type to use for Unique ID in my tables please. Int  Or uniqueIdentifier? Or is it all the same??

View 11 Replies View Related

Help Me Getting A Unique Combination From Table

Jun 18, 2004

Hi, I have following table

BetId GameId
==== ======
500 108
500 109
501 108
501 109
501 110
502 108
502 109

My query would have the form: select BetId where GameId in(108,109)
from Bets then it has to get me BetId : 500 and 502.
Not 501,since this is different combination(108,109,110) ;)

View 3 Replies View Related

How Can We Have UNIQUE Constraint To The Table...

May 19, 2008

Hello...
I was confused with UNIQUE key. Its easy to create PRIMARY KEY to the Table. Now how to alter the Table to have UNIQUE key...

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

View 3 Replies View Related

A Table With A Unique Field

Jul 10, 2007

Hello,

I would like to store some frequently asked questions in a database.
As those questions/answers could be translated in several languages, I was thinking of using two tables :

The first table would contain a unique field : the key wich is an id for each faq.

The second table would contain those fields :
FAQ_ID : foreign key
LANG : language
QUESTION : the question translated in the good language.
ANSWER : the answer translated in the good language.

What I find embarrassing, is that first table just contains a unique field : the id. But it allows :
- to have a table in which a line correspond to an object. It is convenient for object relational mapping.
- it is more evolutionary. If, in the future, I have to add some statistics that concerns a faq, I will have the possibility to store them in the first table, avoiding in this way to duplicate data.

But, one more time, I find a bit embarrassing having a table with a unique field...

What is your opinion about that ?

Thank you in advance for any suggestion.

mathmax

View 20 Replies View Related

Import Date Construct Into SQL Database With SSIS

Apr 8, 2008

Hello,

I am currently trying to import following Excel Datasheet into my SQL 2005 Database with SSIS:

Column: Created
For example cells: 18.03.2008 17:47:43

So now I want to get the time, the year, the month and the day seperately out of this column for a later OLAP Analysis.

I created a Data Flow Task, where I imported the Excel source and set the SQL database as target. But now I don't know really how to work with the "Data Conversion" where to extract the dates. In Excel this is just a "customized" cell.

Anyone who can help me in this situation?

Thank you very much.


Greets from Germany,
chle

View 1 Replies View Related

Append Only Unique Records In SQL Table

Nov 23, 2007

I have a stored procedure that appends data from a temp table to a destination table.  The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates.
The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table?
Thanks in advance.SMc

View 2 Replies View Related

Unique Counter Field In A Table - Best Way?

Oct 11, 2005

I need to to have a table that has a counter field (used to generate an id code for a record).  This is not the primary id for the field but just a counter to label a record in another table, similar to a registration code for an event.  This is a sequential counter that will start 1 and go up on each new record inserted into other tables.  What I want to do is be able to query this counter table to get the next valid number and increment the count for the next time.  This is a multi user web app so needs to prevent duplicate use of same number.How best should this be handled?  A transaction to query and update the field?

View 6 Replies View Related

Creating A Unique Temp Table.

Jan 11, 2007

Set Quoted_Identifier On
Go
Set Ansi_Nulls On
Go

Alter Procedure spReport_SomeFooReport
@SearchFromThisDate datetime = null, @SearchToThisDate datetime = null

As
Declare @TableUniqueIdentifier varchar(80), @SQLString varchar(5000)

set @TableUniqueIdentifier = newid()
set @TableUniqueIdentifier = 'Report_SomeFooReport' + @TableUniqueIdentifier
set @TableUniqueIdentifier = replace(@TableUniqueIdentifier, '-', '7')
set @SQLString = 'Create Table ' + @TableUniqueIdentifier + ' (xxx varchar(40))'
exec @SQLString

Return
Go
Set Quoted_Identifier Off
Go
Set Ansi_Nulls On
Go


-------------------------------------------
the error is:
Server: Msg 2812, Level 16, State 62, Line 12
Could not find stored procedure 'Create Table Report_SomeFooReport06EEEC8D7EA6A74D0178EDD79E999B (xxx varchar(40))'.

So may'be a format issue or something,
im trying to create "temp" tables for sql 2005 report services in my Stored procedures which would have a sql job to get deleted at 23:00

View 5 Replies View Related

How To Receive Unique Row In Database Table

Feb 26, 2013

I am getting problem in unique row in database table, but not getting unique row because I have used Distinct keyword but not getting unique row so how can we do?

I have two table one table information another table Id. But second table in two code same but I am using distinct keyword i get some row unique but second table in two row in same code but when i am fetching row same auto_id render same id create duplicate row. But I am getting only unique row.

View 1 Replies View Related

How To Know If A Table Has Non-unique Clustered Index

Oct 31, 2015

Give a user table MyTable. How to know whether the table contains a non-unique clustered index by using SQL query?

View 1 Replies View Related

New Unique Number In History Table

May 23, 2008

Hello,
I am using SQL Server 2005 and am having trouble with making a history table like mentioned in my earlier thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102811

This is the table "People" I have created:

|PersonId (PK)|DateFrom (PK)|DateTo|PersonName|Other Attributes....

Each change to a person's attributes results in a new row formed with the same PersonId as in the row with old attributes and the Date these new attributes are valid (DateFrom). So as shown above the Primary Key is a combination of the PersonId and DateFrom as a change to a person's attributes should never happen at the same time twice.

My problem is when I want to create a new person, how do I get a new unique id? Ideally I want the a new incremented id, so that all peoples' ids are in a sequential order.

As always, thanks for the help!

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved