Importing Data - 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 2005 Forums

Transact-SQL (2005)

Importing Data

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-06 : 04:16:08

Hi Guys
I’m using the following script within a stored procedure to import data from a temp table to a live table:

INSERT INTO dbo.UserInteractionsRR(
[ReceiptId]
,[Order Placed Date]
,[Order Placed Time]
,[Operator]
,[OldStatusId]
,[StatusId]
,[Action]
,[Action Date]
,[Action Time]
,[Severity]
,[GBP]
)
SELECT DISTINCT * FROM #UserInteractionsRR

The problem that I am having is that on occasions there exists a record in the temp table that already exists in the live table, as in all field values match.

I originally modified that script to the following:

INSERT INTO dbo.UserInteractionsRR(
[ReceiptId]
,[Order Placed Date]
,[Order Placed Time]
,[Operator]
,[OldStatusId]
,[StatusId]
,[Action]
,[Action Date]
,[Action Time]
,[Severity]
,[GBP]
)
SELECT DISTINCT * FROM #UserInteractionsRR
WHERE #UserInteractionsRR.[ReceiptId] NOT IN (SELECT [ReceiptId] FROM UserInteractionsRR)

However I found this to cause a problem as it wouldn’t import all the correct records as Receipt ID may exists twice but with different a StatusID.

So my question is really is there way to stop importing the exact same row into the table?
Thanks

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-01-06 : 04:44:13

quote:
Originally posted by rcr69er

Hi Guys
I’m using the following script within a stored procedure to import data from a temp table to a live table:

INSERT INTO dbo.UserInteractionsRR(
[ReceiptId]
,[Order Placed Date]
,[Order Placed Time]
,[Operator]
,[OldStatusId]
,[StatusId]
,[Action]
,[Action Date]
,[Action Time]
,[Severity]
,[GBP]
)
SELECT DISTINCT * FROM #UserInteractionsRR

The problem that I am having is that on occasions there exists a record in the temp table that already exists in the live table, as in all field values match.

I originally modified that script to the following:

INSERT INTO dbo.UserInteractionsRR(
[ReceiptId]
,[Order Placed Date]
,[Order Placed Time]
,[Operator]
,[OldStatusId]
,[StatusId]
,[Action]
,[Action Date]
,[Action Time]
,[Severity]
,[GBP]
)
SELECT DISTINCT * FROM #UserInteractionsRR
WHERE (cast(#UserInteractionsRR.[ReceiptId] as varchar) + cast(#UserInteractionsRR.[StatusId] as varchar))NOT IN (SELECT (cast([ReceiptId] as varchar)+ cast([StatusId] as varchar)) FROM UserInteractionsRR)

However I found this to cause a problem as it wouldn’t import all the correct records as Receipt ID may exists twice but with different a StatusID.

So my question is really is there way to stop importing the exact same row into the table?
Thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-06 : 04:45:00

try this

INSERT INTO dbo.UserInteractionsRR(
[ReceiptId]
,[Order Placed Date]
,[Order Placed Time]
,[Operator]
,[OldStatusId]
,[StatusId]
,[Action]
,[Action Date]
,[Action Time]
,[Severity]
,[GBP]
)
SELECT DISTINCT * FROM #UserInteractionsRR trr
left join UserInteractionsRR urr on urr.ReceiptId = trr.ReceiptId and urr.StatusId = trr.StatusId
where ( urr.ReceiptId is null and urr.StatusId is null )

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-01-06 : 04:48:20

Sorry... I was confused on your last sentence, my reply might have been wrong... was it? Sorry.

quote:
Originally posted by chrianth

quote:
Originally posted by rcr69er

Hi Guys
I’m using the following script within a stored procedure to import data from a temp table to a live table:

INSERT INTO dbo.UserInteractionsRR(
[ReceiptId]
,[Order Placed Date]
,[Order Placed Time]
,[Operator]
,[OldStatusId]
,[StatusId]
,[Action]
,[Action Date]
,[Action Time]
,[Severity]
,[GBP]
)
SELECT DISTINCT * FROM #UserInteractionsRR

The problem that I am having is that on occasions there exists a record in the temp table that already exists in the live table, as in all field values match.

I originally modified that script to the following:

INSERT INTO dbo.UserInteractionsRR(
[ReceiptId]
,[Order Placed Date]
,[Order Placed Time]
,[Operator]
,[OldStatusId]
,[StatusId]
,[Action]
,[Action Date]
,[Action Time]
,[Severity]
,[GBP]
)
SELECT DISTINCT * FROM #UserInteractionsRR
WHERE (cast(#UserInteractionsRR.[ReceiptId] as varchar) + cast(#UserInteractionsRR.[StatusId] as varchar))NOT IN (SELECT (cast([ReceiptId] as varchar)+ cast([StatusId] as varchar)) FROM UserInteractionsRR)

However I found this to cause a problem as it wouldn’t import all the correct records as Receipt ID may exists twice but with different a StatusID.

So my question is really is there way to stop importing the exact same row into the table?
Thanks

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-06 : 04:54:57

Hi

Thanks for all that.

Basically I want to ensure that I am not inserting the same record twice.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 08:57:43

quote:
Originally posted by rcr69er

Hi

Thanks for all that.

Basically I want to ensure that I am not inserting the same record twice.

Thanks


INSERT INTO dbo.UserInteractionsRR(
[ReceiptId]
,[Order Placed Date]
,[Order Placed Time]
,[Operator]
,[OldStatusId]
,[StatusId]
,[Action]
,[Action Date]
,[Action Time]
,[Severity]
,[GBP]
)
SELECT DISTINCT * FROM #UserInteractionsRR
WHERE  NOT EXISTS (SELECT 1 FROM UserInteractionsRR WHERE [ReceiptId]=#UserInteractionsRR.[ReceiptId]
AND [StatusId]=#UserInteractionsRR.[StatusId])

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-06 : 09:47:49

Hey Visakh

Thanks for that, can you just explain your logic as I'm not totally sure what is going on?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 09:55:19

quote:
Originally posted by rcr69er

Hey Visakh

Thanks for that, can you just explain your logic as I'm not totally sure what is going on?

Thanks

i'm just checking before you insert from #UserInteractionsRR to dbo.UserInteractionsRR whether any record with same values for [ReceiptId] AND [StatusId] exists in dbo.UserInteractionsRR and inserts only those which dont.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-06 : 09:59:42

Thanks that helped alot!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 10:02:31

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources