insert/update/delete without replication

Hi,
i have a peer to peer replication set up between 2 databases. There
was a parallel insert the databases are out of sync. i know the table
where the difference is. Is there any stored procedure with which i
can insert/update/delete without it being replicated?
You could use the SKIPERRORS flag, or you could modify the relevant
rteplication stored procedure on the subscriber to skip the change.
HTH,
Paul Ibison

Insert/Update with dynamic database name

Hi

My problem is as follows:

I need to transmit data between two databases on the same server, but I
have to use dynamic database names (they must be configurable). For
example I need to achive sth like that:

insert into [database1].[dbo].[table1]
(select columns from [table2])

when database1 is not known at implementation stage.

I know I can use EXEC @.t_sql_code, but I wonder if there is any other
way? (OPENROWSET doesn't seem to suit my needs)

Thanks in advance

Amfiamfi1 (amfi1@.poczta.fm) writes:
> I need to transmit data between two databases on the same server, but I
> have to use dynamic database names (they must be configurable). For
> example I need to achive sth like that:
> insert into [database1].[dbo].[table1]
> (select columns from [table2])
> when database1 is not known at implementation stage.
> I know I can use EXEC @.t_sql_code, but I wonder if there is any other
> way? (OPENROWSET doesn't seem to suit my needs)

Well, one thing you can do is to use stored procedures:

SELECT @.spname = @.srcdb + '.dbo.getmydata'
INSERT table1 (...)
EXEC @.spname

Using a dynamic SP name is not as messy as have all the code in dynamic
SQL.

Now, your example indicates that it is the target database that is
unknown to you, in which case my suggestion does not work.

A faint possibility is to set up a linked server that loops back to
your own server. The target database would then be in the connection
string. You could thus say:

INSERT MYSERVER..dbo.table (...)
SELECT...

and you would set up the linked server as you need it. But there is
an overhead for the loopback. And I must that I have not tested if
it actually works.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Insert with multiple tables

Hi All SQLStars

I am kind of new to developing complex databases in SQL Server 2005, so I hope there is someone out there with the answer to all my prayers.

I have 3 distinct tables

tblContainer

ContNum smallint (primary key)

Length smallint

Stop smallint

ContTypeID tinyint

HookUpID tinyint

tblContTypes

ContTypeID tinyint

ContType nvarchar(30)

tblHookUp

HookUpID tinyint

HookUp nvarchar(30)

I need to make an Insert query (and an update query) as stored procedures that can insert/update rows in the tblContainer, but I want the user not to enter ContTypeID or HookUpID but rather ContType and HookUp. Further there are some conditions to be validated:

WHEN ContNum BETWEEN 100 AND 199 THEN HookUpID ='13'

WHEN ContNum BETWEEN 1000 AND 1999 THEN HookUpID ='1'

WHEN ContNum BETWEEN 2000 AND 2499 THEN HookUpID ='2'

WHEN ContNum BETWEEN 2500 AND 2999 THEN HookUpID ='3'

WHEN ContNum BETWEEN 3000 AND 3249 THEN HookUpID ='4'

WHEN ContNum BETWEEN 3250 AND 3499 THEN HookUpID ='5'

WHEN ContNum BETWEEN 3500 AND 3749 THEN HookUpID ='6'

WHEN ContNum BETWEEN 3750 AND 3999 THEN HookUpID ='7'

WHEN ContNum BETWEEN 4000 AND 4249 THEN HookUpID ='8'

WHEN ContNum BETWEEN 4250 AND 4449 THEN HookUpID ='9'

WHEN ContNum BETWEEN 4500 AND 4749 THEN HookUpID ='10'

WHEN ContNum BETWEEN 4750 AND 4999 THEN HookUpID ='11'

WHEN ContNum BETWEEN 5000 AND 5249 THEN HookUpID ='12'

ContNum can only be a number in the above used intervals, there's a constraint on tblContainer to ensure that.

I've tried this code, but obviously it doesn't work, can you tell me why?

CREATE PROCEDURE usp_InsertContainer

(

@.ContNum smallint,

@.Length smallint,

@.Stop smallint,

@.ContType nvarchar(30),

@.HookUp nvarchar(30)

)

INSERT INTO tblContainer (ContNum, Length, Stop, ContTypeID, HookUpID)

WHEN ContNum BETWEEN 100 AND 199 THEN HookUpID ='13'

WHEN ContNum BETWEEN 1000 AND 1999 THEN HookUpID ='1'

WHEN ContNum BETWEEN 2000 AND 2499 THEN HookUpID ='2'

WHEN ContNum BETWEEN 2500 AND 2999 THEN HookUpID ='3'

WHEN ContNum BETWEEN 3000 AND 3249 THEN HookUpID ='4'

WHEN ContNum BETWEEN 3250 AND 3499 THEN HookUpID ='5'

WHEN ContNum BETWEEN 3500 AND 3749 THEN HookUpID ='6'

WHEN ContNum BETWEEN 3750 AND 3999 THEN HookUpID ='7'

WHEN ContNum BETWEEN 4000 AND 4249 THEN HookUpID ='8'

WHEN ContNum BETWEEN 4250 AND 4449 THEN HookUpID ='9'

WHEN ContNum BETWEEN 4500 AND 4749 THEN HookUpID ='10'

WHEN ContNum BETWEEN 4750 AND 4999 THEN HookUpID ='11'

WHEN ContNum BETWEEN 5000 AND 5249 THEN HookUpID ='12'

INNER JOIN tblHookUp on tblContainer.HookUpID=tblHookUp.HookUpID

VALUES(@.ContNum, @.Length, @.Stop, @.HookUp)

The result should look like this:

ContNum, Length, Stop, ContType, HookUp

1, 100, 15, Very Large, Wire

I'd really appriciate your help in this riddle - thanks a bundle.

Kind regards

Tina Nielsen

Denmark

Hi Tina,

Use a "select" statement as the source for the "insert" one.

INSERT INTO dbo.tblContainer (ContNum, Length, Stop, ContTypeID, HookUpID)

select

@.ContNum,

@.Length,

@.Stop,

(select ContTypeID from dbo.tblContTypes where ContType = @.ContType),

case

WHEN @.ContNum BETWEEN 100 AND 199 THEN HookUpID ='13'

WHEN @.ContNum BETWEEN 1000 AND 1999 THEN HookUpID ='1'

WHEN @.ContNum BETWEEN 2000 AND 2499 THEN HookUpID ='2'

WHEN @.ContNum BETWEEN 2500 AND 2999 THEN HookUpID ='3'

WHEN @.ContNum BETWEEN 3000 AND 3249 THEN HookUpID ='4'

WHEN @.ContNum BETWEEN 3250 AND 3499 THEN HookUpID ='5'

WHEN @.ContNum BETWEEN 3500 AND 3749 THEN HookUpID ='6'

WHEN @.ContNum BETWEEN 3750 AND 3999 THEN HookUpID ='7'

WHEN @.ContNum BETWEEN 4000 AND 4249 THEN HookUpID ='8'

WHEN @.ContNum BETWEEN 4250 AND 4449 THEN HookUpID ='9'

WHEN @.ContNum BETWEEN 4500 AND 4749 THEN HookUpID ='10'

WHEN @.ContNum BETWEEN 4750 AND 4999 THEN HookUpID ='11'

WHEN @.ContNum BETWEEN 5000 AND 5249 THEN HookUpID ='12'

else null

end

What about adding columns [ContNum_From] and [ContNum_To] to table [tblHookUp], and using:

INSERT INTO dbo.tblContainer (ContNum, Length, Stop, ContTypeID, HookUpID)

select

@.ContNum,

@.Length,

@.Stop,

(select ContTypeID from dbo.tblContTypes where ContType = @.ContType),

(select HookUpID from dbo.tblHookUp where @.ContNum between [ContNum_From] and [ContNum_To])

AMB

|||

Hi AMB

Thank you very, very much for your rapid reply. I'm quite sure this will solve my problem.

Just to clarify, can I use this as a template for an update and delete query as well?

Again thank very much for helping me out.

Kind regards

Tina Nielsen

Denmark

|||

Hi, Tina!

Another option is to use computed column instead of storing data in Database:

Code Snippet

CREATETABLE tblContainer (

ContNum smallintPRIMARYKEY,

Length smallint,

Stop smallint,

ContTypeID tinyint,

HookUpID AS(CASE

WHEN ContNum BETWEEN 100 AND 199 THEN 13

WHEN ContNum BETWEEN 1000 AND 1999 THEN 1

WHEN ContNum BETWEEN 2000 AND 2499 THEN 2

WHEN ContNum BETWEEN 2500 AND 2999 THEN 3

WHEN ContNum BETWEEN 3000 AND 3249 THEN 4

WHEN ContNum BETWEEN 3250 AND 3499 THEN 5

WHEN ContNum BETWEEN 3500 AND 3749 THEN 6

WHEN ContNum BETWEEN 3750 AND 3999 THEN 7

WHEN ContNum BETWEEN 4000 AND 4249 THEN 8

WHEN ContNum BETWEEN 4250 AND 4449 THEN 9

WHEN ContNum BETWEEN 4500 AND 4749 THEN 10

WHEN ContNum BETWEEN 4750 AND 4999 THEN 11

WHEN ContNum BETWEEN 5000 AND 5249 THEN 12

END)

)

So, whenever you insert or update [ContNum] column, in SELECT queries value of HookUpID is calculated automatically. You don't need to store it in database and use additional tricks during update operation.

NOTE, that this option is applicable only if SELECT statement execution speed is not critical! As I know, computed columns affect SELECT statement performance. Although, in this case, when rows count in the table is not much, it's not significant.

|||

Hi Tina,

For a "delete" you just need the PK to be deleted, and for "update", well it depends if you will be updating the PK also.

declare @.ContNum

set @.ContNum = 10

delete from

where ContNum = @.ContNum

go

declare @.ContNum int, @.Length int, @.Stop int

set @.ContNum = 10

set @.Length = 20

set @.Stop = 30

update

dbo.tblContainer

set

Length = @.Length,

Stop = @.Stop

where

ContNum = @.ContNum

go

AMB