Time as Key Field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Time as Key Field

Author

Topic

mmfarmani
Starting Member

22 Posts

Posted - 2007-07-25 : 11:17:18

Hello,
I need to insert all records from one table to another table and time is the key filed for the table that I need to insert records in.
Because the SQL works in nanoseconds I get error duplication…Any idea about solving this issue?

Thanks in advance

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 11:23:24

Post some sample code and which of those records you like to import.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-07-25 : 11:24:49

put a an ID column on the new table too. make it an identity column and then put your PK on both your datetime column then your ID column (in that order). Make the index a unique clustered index. THen insert your data.

NOTE: You will need to name the columns in your select/insert because a select * will not work becuase of the extra column (plus, that is just bad form

)

-js

mmfarmani
Starting Member

22 Posts

Posted - 2007-07-25 : 11:48:08

I am not able to touch the tables structure...I just can insert data ...

mmfarmani
Starting Member

22 Posts

Posted - 2007-07-25 : 11:56:18

insert into dbo.AFW_DefaultCommission ( DComId, PersCode, LineOfBus, LineOfBusSub, TranCode, CoCode, Method, Percentage, FlatAmt, EffDate, LastModified)
(select getdate(),'N' ,tblDefaultCommissions.LineOfBus, tblDefaultCommissions.LineOfBusSub, tblDefaultCommissions.TranCode, tblDefaultCommissions.CoCode, tblDefaultCommissions.Method, tblDefaultCommissions.Percentage, tblDefaultCommissions.FlatAmt, tblDefaultCommissions.EffDate, Date())

GetDate(), gives me teh error... how should I change the function to get the time as Key?
Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 11:58:18

Is PersCode PK in the table AFW_DefaultCommission ?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mmfarmani
Starting Member

22 Posts

Posted - 2007-07-25 : 12:00:24

No it is not...

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 12:02:51

quote:
Originally posted by mmfarmani

insert into dbo.AFW_DefaultCommission ( DComId, PersCode, LineOfBus, LineOfBusSub, TranCode, CoCode, Method, Percentage, FlatAmt, EffDate, LastModified)
(select getdate(),'N' ,tblDefaultCommissions.LineOfBus, tblDefaultCommissions.LineOfBusSub, tblDefaultCommissions.TranCode, tblDefaultCommissions.CoCode, tblDefaultCommissions.Method, tblDefaultCommissions.Percentage, tblDefaultCommissions.FlatAmt, tblDefaultCommissions.EffDate, Date())

GetDate(), gives me teh error... how should I change the function to get the time as Key?
Thanks

What is the error message?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mmfarmani
Starting Member

22 Posts

Posted - 2007-07-25 : 12:06:11

Key Duplication, because getdate() puts the same date and time for all the records from the second table...

mmfarmani
Starting Member

22 Posts

Posted - 2007-07-25 : 12:17:17

Let me explain the whole situation…
I have three tables:
Broker
Defaultcomission
tblDefaultCommissions
1- I need to find all brokers that don’t excists in Defaultcomission table
2- Insert all records from tblDefaultCommissions into Defaultcomission, for those Brokers I found in first step.

Defaultcomission.DComId is a time stmp and the Key...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-25 : 12:27:00

As Mmfarmani already said; you are doing set operation. In this case an Insert. When you do the insert you are using GETDATE(). This is going to apply the same date and time for each record as the insert is happening as a single batch. So, each and every record you insert in that batch are going to have the same date time from GETDATE and thus the first will (probably) succeed and all subsequent rows will fail if you have a DATETIME Primary or Unique key on that column.

Eyechart gave you one solution to your problem. But, since you cannot change the table structure or primary keys you will have to loop through each record you want to insert and make sure that 1/300 of a second passes before each insert to avoid a duplicate key. If you are not sure how or why to do this, let us know and we can help you understand.

Additionally, I'd try to get whoever created this table to change it and make it better.

-Ryan

mmfarmani
Starting Member

22 Posts

Posted - 2007-07-25 : 12:31:21

Thanks...
Yes, I am not sure how to add this data one by one for each Broker...And we have no control over table creators in insurance companies!
I'll be waiting for your help on inserting data...

mmfarmani
Starting Member

22 Posts

Posted - 2007-07-25 : 12:34:00

I get Brokers:

SELECT BrokerCode
FROM dbo.AFW_Broker
WHERE (BrokerCode NOT IN (SELECT PersCode
FROM dbo.AFW_DefaultCommission))

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-25 : 12:43:48

here you go:


select yourcolumns
into #temp
from ....

declare @inc datetime
select @inc = getdate() -- you can subtract time here if you need to
UPDATE #temp
SET @inc = yourDateTimeColumn = dateadd(ms, 10, @inc)

insert into destinationTable(columns)
select columns
from #temp

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 12:49:57

quote:
Originally posted by spirit1

here you go:

select yourcolumns
into #temp
from ....

declare @inc datetime
select @inc = getdate() -- you can subtract time here if you need to
UPDATE #temp
SET @inc = yourDateTimeColumn = dateadd(ms, 10, @inc)

insert into destinationTable(columns)
select columns
from #temp

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Thats a good trick Mladen.. I saw it somewhere earlier and completely forgot about it.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-25 : 12:52:23

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mmfarmani
Starting Member

22 Posts

Posted - 2007-07-25 : 13:01:18

sorry, but it is not clear for me... For example, I have 10 Brokers for Brokers table that don't exsit in Defaultcomission Table. I get BrokerCode and for each Brokercode I need to insert for example 23 records from tblDefaultCommissions into Defaultcomission ...Broker.BrokerCode=DefaultCommission.PersCode

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-25 : 13:03:29

well if you want it without an update you can do this:


insert into destinationTable(columns)
select  dateadd(ms, (row_number() over(order by yourDateTimeColumn))*10, GETDATE()) as row_number, 
        otherColumns
from    sourceTable

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mmfarmani
Starting Member

22 Posts

Posted - 2007-07-25 : 13:05:40

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-25 : 13:06:03

well mmfarmani then please provide info requested here
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

and we'll be able to help you better

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mmfarmani
Starting Member

22 Posts

Posted - 2007-07-25 : 13:17:50

It got very confusing here...
I explained the problem earlier:
I have three tables:
1- Broker 2- Defaultcomission 3- tblDefaultCommissions
First Step: I need to find all brokers that don’t exist in Defaultcomission table
SELECT BrokerCode
FROM dbo.AFW_Broker
WHERE (BrokerCode NOT IN (SELECT PersCode
FROM dbo.AFW_DefaultCommission))

Second Step: Insert all records from tblDefaultCommissions into Defaultcomission, for those Brokers I found in first step.
insert into dbo.AFW_DefaultCommission ( DComId, PersCode, LineOfBus, LineOfBusSub, TranCode, CoCode, Method, Percentage, FlatAmt, EffDate, LastModified)
(select getdate(), ,tblDefaultCommissions.LineOfBus, tblDefaultCommissions.LineOfBusSub, tblDefaultCommissions.TranCode, tblDefaultCommissions.CoCode, tblDefaultCommissions.Method, tblDefaultCommissions.Percentage, tblDefaultCommissions.FlatAmt, tblDefaultCommissions.EffDate, Date())

Defaultcomission.DComId is a time stmp and the Key...and PersCode is the BrokerCode..
...
- How should I one by one insert the records for the missing Broker?
- How should I getdate() for each record? Because the Defaultcomission.DComId is the Key and Getdate() gets the firat record but gives duplication error for other records...
...
I hope this time my questions are clear...
I appreciate your attention and help..
Maryam

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources