How to ensure the rest of the records will be inserted even if there is an error

Hi,
I have a sql statement that perform bulk insert into another table.
How can I ensure that if an error occurs, maybe due to primary key
constraint, the rest of the records will be inserted.
Thanks alot.Hi
Take a look at this example, even this transaction will generate a violation
of primary key constraint the rest of the data will be inserted
Please read SET ARITHABORT commant in the BOL to get a whole picture
create table #t (col int not null primary key)
begin tran
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)
insert into #t values (3)
insert into #t values (4)
insert into #t values (5)
commit
select * from #t
drop table #t
"Shelby" <shelby@.singnet.com.sg> wrote in message
news:%23X6PcA6XGHA.3868@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a sql statement that perform bulk insert into another table.
> How can I ensure that if an error occurs, maybe due to primary key
> constraint, the rest of the records will be inserted.
> Thanks alot.
>
>
>|||That depends on the type of error that occures. Fatal Errrors for
example will always abort the entire procedure and rollback all
transactions. There might be a way for non-fatal errors using if-blocks
like
if @.err<>0
// do something else with the datasql

How to enfore a primary key range ??

I've inherited the following situation...
The table contains 4 columns... script below
Note that the first 3 columns denote the primary Key...
Actually what is really meant is the following...
Let's say the values for one row are as follows...
Code= A
LowVal = 25
HighVal=50
UseThis=Fred
What they want to be implied by this row... if Code=A and the test val is
between 25 and 50 UseThis= Fred
They want to disallow any row that overlaps from being added... such as the
following...
Code= A
LowVal = 30
HighVal=40
UseThis=Joe
How can you enforce something like this ?
CREATE TABLE [dbo].[Table1] (
[Code] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
[LowVal] [decimal](6, 0) NOT NULL ,
[HighVal] [decimal](6, 0) NOT NULL ,
[UseThis] [char] (10) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code],
[LowVal],
[HighVal]
) ON [PRIMARY]"Rob" <rwchome@.comcast.net> wrote in message
news:l4WdnX_h78I-eyzeRVn-vA@.comcast.com...
> I've inherited the following situation...
> The table contains 4 columns... script below
> Note that the first 3 columns denote the primary Key...
> Actually what is really meant is the following...
> Let's say the values for one row are as follows...
> Code= A
> LowVal = 25
> HighVal=50
> UseThis=Fred
> What they want to be implied by this row... if Code=A and the test val is
> between 25 and 50 UseThis= Fred
> They want to disallow any row that overlaps from being added... such as
> the following...
> Code= A
> LowVal = 30
> HighVal=40
> UseThis=Joe
> How can you enforce something like this ?
>
> CREATE TABLE [dbo].[Table1] (
> [Code] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
> [LowVal] [decimal](6, 0) NOT NULL ,
> [HighVal] [decimal](6, 0) NOT NULL ,
> [UseThis] [char] (10) COLLATE Latin1_General_BIN NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code],
> [LowVal],
> [HighVal]
> ) ON [PRIMARY]
>
You'll have to use a trigger for this, eg:
create trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 l
join Table1 r
on l.LowVal < r.LowVal
and l.HighVal > r.LowVal
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
David|||Thanks David,
Maybe I am doing something wrong, but I was able to add the following rows
after applying the trigger...
insert into Table1 Values('A',20,100,'Joe')
insert into Table1 Values('A',20,500,'FRED')
Rob
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eDxVWD1CGHA.344@.TK2MSFTNGP11.phx.gbl...
> "Rob" <rwchome@.comcast.net> wrote in message
> news:l4WdnX_h78I-eyzeRVn-vA@.comcast.com...
> You'll have to use a trigger for this, eg:
> create trigger Table1_no_overlap
> on Table1 for insert, update
> as
> begin
> if exists
> (
> select *
> from Table1 l
> join Table1 r
> on l.LowVal < r.LowVal
> and l.HighVal > r.LowVal
> )
> begin
> raiserror('Change would create overlapping range.',16,1)
> rollback transaction
> end
> end
>
> David
>|||Hi, Rob
Use the following trigger:
alter trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 t
join inserted i
on t.LowVal between i.LowVal and i.HighVal
or i.LowVal between t.LowVal and t.HighVal
where i.Code<>t.Code or i.LowVal<>t.LowVal or i.HighVal<>t.HighVal
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
If you want to allow overlapping ranges for different codes (but not
for the same code), the trigger would be like this:
alter trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 t
join inserted i
on t.Code=i.Code and (
t.LowVal between i.LowVal and i.HighVal
or i.LowVal between t.LowVal and t.HighVal
)
where i.LowVal<>t.LowVal or i.HighVal<>t.HighVal
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
Razvan|||Rob wrote:

> Thanks David,
> Maybe I am doing something wrong, but I was able to add the following rows
> after applying the trigger...
> insert into Table1 Values('A',20,100,'Joe')
> insert into Table1 Values('A',20,500,'FRED')
> Rob
>
Try it like this. Notice that I've added an extra constraint, modified
the join in the trigger and added CODE to the join. That's my reading
of what you want to achieve. Test carefully.
ALTER TABLE table1 ADD CONSTRAINT ck_table1_lowval_highval
CHECK (lowval <= highval) ;
GO
create trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 l
join Table1 r
on l.LowVal < r.HighVal
and l.HighVal > r.LowVal
and l.code = r.code
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
GO
David Portas
SQL Server MVP
--|||Hi, David
Your trigger doesn't allow any row to be inserted.
Razvan|||Razvan Socol wrote:
> Hi, David
> Your trigger doesn't allow any row to be inserted.
> Razvan
You're right. Here's a correction:
create trigger Table1_no_overlap
on Table1 for insert, update
as
begin
if exists
(
select *
from Table1 l
join Table1 r
on l.LowVal < r.HighVal
and l.HighVal > r.LowVal
and l.code = r.code
and (l.LowVal <> r.LowVal
or l.HighVal <> r.HighVal)
)
begin
raiserror('Change would create overlapping range.',16,1)
rollback transaction
end
end
GO
David Portas
SQL Server MVP
--|||Hi, David
My understanding of the original post is that the following rows are
not allowed (but your trigger allows them):
insert into Table1 Values('A',20,100,'Joe')
insert into Table1 Values('A',100,150,'FRED')
Rob wrote:
> What they want to be implied by this row... if Code=A and the test val is
> between 25 and 50 UseThis= Fred
The following rows would be ok:
insert into Table1 Values('A',20,100,'Joe')
insert into Table1 Values('A',101,150,'FRED')
Razvan|||Thank you both Razvan and David...
Sorry I was not clear on this, actually same HighVal on one row may be equal
to LowVal on another...
The code applied uses > LowVal and <= HighVal...
Rob
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1135755688.241085.105660@.g49g2000cwa.googlegroups.com...
> Hi, David
> My understanding of the original post is that the following rows are
> not allowed (but your trigger allows them):
> insert into Table1 Values('A',20,100,'Joe')
> insert into Table1 Values('A',100,150,'FRED')
> Rob wrote:
> The following rows would be ok:
> insert into Table1 Values('A',20,100,'Joe')
> insert into Table1 Values('A',101,150,'FRED')
> Razvan
>|||You might want to add some other constraints. I would not allow the
low and high values to be the same; disjoint ranges will allow you to
use a more readable BETWEEN predicate.
CREATE TABLE Table1
(foo_code CHAR (10) NOT NULL,
low_val DECIMAL(6,0) NOT NULL,
high_val DECIMAL(6,0) NOT NULL,
use_this CHAR (10) DEFAULT '{{ none }}' NOT NULL,
CHECK (low_val <= high_val)
PRIMARY KEY (foo_code,low_val, high_val),
UNIQUE (foo_code,low_val),
UNIQUE (foo_code, high_val)
);
Besides not having overlaps, you might want to avoid gaps in the
ranges.
CREATE TRIGGER Table1_No_Gaps
ON Table1 FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS
(SELECT *
FROM Table1 AS T1
GROUP BY T1.foo_code
HAVING MAX(high_val)- MIN(low_val) +1
= SUM(high_val - low_val + 1)
BEGIN
RAISERROR ('Code Range Errors',16,1);
ROLLBACK TRANSACTION;
END;
END;sql

How to drop Primary key by specifying column only?

I checked column from xsd file which created from DataBase.

And before installing my application I will check any column in destination base whether have complete column or not.

And if there is any column which is not wanted column(not same as xsd structure)

I will delete it by creating sql command as follow "ALTER TABLE tableName DROP COLUMN column1, column2, ....."

and Execute it by program initialization.

So

I need delete it by run-time

However some column may be Primary Key with any reason

That's why I can't delete them by simple command

I expect to delete them by specific column instead of specific constraint name which is not sure name.

Please advise me...

I don't think you can do it by just specifying a column but you can do it this way:

To get the name of the primary key:

SELECT constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE (TABLE_NAME = 'TestTable') AND (CONSTRAINT_TYPE = 'PRIMARY KEY')

To drop the primary key:

ALTER TABLE TestTable
DROP CONSTRAINT PK_TestTable

|||

Thanks a lot

:D

How to drop primary in script

Hi, does someone know how to drop primary key using TSQL? We need to put
everything into TSQL script. Somehow, I could not find anything in BOL.
please help.
Thanks, Anna
Look at Alter Table..drop constraint.. in BOL
"Anna Linne" <Anna Linne@.discussions.microsoft.com> wrote in message
news:C330A43E-CE07-428B-98FD-A56EB60B9634@.microsoft.com...
> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna
|||HI
ALTER TABLE dbo.Table1
DROP CONSTRAINT PK_Table1
Andras Jakus MCDBA
"Anna Linne" wrote:

> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna
|||alter table table1 add constraint pk primary key(column1)
alter table table1 drop constraint pk
"Anna Linne" wrote:

> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna

How to drop primary in script

Hi, does someone know how to drop primary key using TSQL? We need to put
everything into TSQL script. Somehow, I could not find anything in BOL.
please help.
Thanks, AnnaLook at Alter Table..drop constraint.. in BOL
"Anna Linne" <Anna Linne@.discussions.microsoft.com> wrote in message
news:C330A43E-CE07-428B-98FD-A56EB60B9634@.microsoft.com...
> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna|||HI
ALTER TABLE dbo.Table1
DROP CONSTRAINT PK_Table1
Andras Jakus MCDBA
"Anna Linne" wrote:
> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna|||alter table table1 add constraint pk primary key(column1)
alter table table1 drop constraint pk
"Anna Linne" wrote:
> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna

How to drop all primary key of all tables in DB?

How to drop all primary key of all tables in DB?
After reading a document for explaning how to use clustered index, I knew
set all primary key to be clustered is not correct for me.
I would like to drop them. recreate a new nonclustered primary key and
create other clustered indexes.
Now I would like to know how to drop all primary key of all tables in DB by
T-SQL, because there are too many tables to drop primary key by hand.
Thanks
--UsingSQL2005DevFrank
declare @.pklist table
(
ident int identity(1, 1),
pkname sysname,
tablename sysname
)
insert into
@.pklist
(
pkname,
tablename
)
select
constraint_name,
table_name
from
information_schema.table_constraints
where
constraint_type = 'primary key'
set @.counter = @.@.rowcount
while @.counter > 0
begin
select @.constraint = pkname, @.table = tablename from @.pklist where ident =
@.counter
exec ('alter table [' + @.table + '] drop constraint [' + @.constraint + ']')
set @.counter = @.counter - 1
end
"Frank Lee" <Reply@.to.newsgroup> wrote in message
news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
> How to drop all primary key of all tables in DB?
> After reading a document for explaning how to use clustered index, I knew
> set all primary key to be clustered is not correct for me.
> I would like to drop them. recreate a new nonclustered primary key and
> create other clustered indexes.
> Now I would like to know how to drop all primary key of all tables in DB
> by T-SQL, because there are too many tables to drop primary key by hand.
> Thanks
> --UsingSQL2005Dev
>|||Hi
You should extend Uri's solution so that all Foreign Keys that reference
your Primary Key is removed before trying to remove the Primary Key.
John
"Frank Lee" <Reply@.to.newsgroup> wrote in message
news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
> How to drop all primary key of all tables in DB?
> After reading a document for explaning how to use clustered index, I knew
> set all primary key to be clustered is not correct for me.
> I would like to drop them. recreate a new nonclustered primary key and
> create other clustered indexes.
> Now I would like to know how to drop all primary key of all tables in DB
> by T-SQL, because there are too many tables to drop primary key by hand.
> Thanks
> --UsingSQL2005Dev
>|||I see. Thx.
"John Bell" <jbellnewsposts@.hotmail.com> glsD:eNvQsxGEGHA.3892@.TK2MSFTNGP10.phx.g
bl...
> Hi
> You should extend Uri's solution so that all Foreign Keys that reference
> your Primary Key is removed before trying to remove the Primary Key.
> John
> "Frank Lee" <Reply@.to.newsgroup> wrote in message
> news:ezgjh5CEGHA.3528@.TK2MSFTNGP12.phx.gbl...
>

How to drop all PKs on tables in database?

I have a list of 35 tables that need to drop the primary key index from in my database.

My problem is as follows for these 35 tables:

1. How can I get a list of all the primary keys for this subset of tables in my database
2. How can I drop just the PK for each of these tables?

I want an easy quick way to do this without having to manually do this for each of the 35 tables in my database. I dont want to do this for all tables just the subset.

Thanksdeclare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='PK' and object_name(parent_obj) in ('table1','table2',.....)
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur|||I think it would be safer to do this inside the loop:

print 'alter table ' + @.Table + ' drop constraint ' + @.PK

rather than this:

exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )

that way, you can inspect the result for correctness, make sure you really want to execute it, etc.

when playing the sql-from-sql game, you should execute only after inspecting the result, IMO.|||the answer mention above would goes wrong if foreign keys are there so we need to drop all foreing keys first then apply above mention procedure
1 step first
drop all foreign keys relation ship
declare @.Table varchar(100)
declare @.FK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='F' and object_name(parent_obj) in ('Table1','Table2',...)
open cur
fetch next from cur into @.FK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.FK )
fetch next from cur into @.FK, @.Table
end
close cur
deallocate cur
2. step second now drop all primary key relation ship
declare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='PK' and object_name(parent_obj) in ('table1','table2',.....)
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
exec ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur|||right... we need to drop the FKs first.
but the code from greenindia is having the following problems

1) xtype for foriegn-key is "F" and not "FK"
2) who says that the tables with FKs belongs to the same set of that with PKs? since the same set of tables are used in the code
object_name(parent_obj) in ('Table1','Table2',...)

u need a trip from sysforeignkeys to trap the relation properly :rolleyes:|||you are my dear friend upalsen , xtype for foreign key should have been F instead of FK.|||anybody think of asking our friend why he would want to do this before we hand him a loaded gun?|||anybody think of asking our friend why he would want to do this before we hand him a loaded gun?

What would be the fun in that?|||Hi all,

Thanks for your help, however I cannot see the output of the print commands in SQL Server Query Analyzer when I run the cursor script:

declare @.Table varchar(100)
declare @.PK varchar(100)
declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='P' and object_name(parent_obj) in ('table1', 'table2', 'table3')
open cur
fetch next from cur into @.PK, @.Table
while @.@.fetch_status = 0
begin
print ('alter table ' + @.Table + ' drop constraint ' + @.PK )
fetch next from cur into @.PK, @.Table
end
close cur
deallocate cur

print 'alter table ' + @.Table + ' drop constraint ' + @.PK|||does this query return anything? if not, there's your answer.

select name,object_name(parent_obj) from sysobjects where xtype='P' and object_name(parent_obj) in ('table1', 'table2', 'table3')

how to drop all keys & indexes

I'm trying to drop all indexes and primary keys so that i can rebuild them
(from a script created from same database on another server).

when i go to the 'generate sql scripts', it has the ability to drop or
generate all
tables. it also has the ability to generate all keys only. but i cant find
a way
to drop all of these keys...

any ideas?

tia
woody rao"Woody Rao" <nvwoody@.dreamscape.com> wrote in message
news:10b9lq618pmaoec@.corp.supernews.com...
> I'm trying to drop all indexes and primary keys so that i can rebuild them
> (from a script created from same database on another server).
> when i go to the 'generate sql scripts', it has the ability to drop or
> generate all
> tables. it also has the ability to generate all keys only. but i cant
find
> a way
> to drop all of these keys...
> any ideas?
> tia
> woody rao

One way is to copy and paste the result of a query like this one (assuming
all tables are owned by dbo):

select 'alter table dbo.'+ TABLE_NAME + ' drop constraint ' +
CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE in ('PRIMARY KEY', 'FOREIGN KEY')

This can be automated with a cursor and EXEC() if necessary, although you
may find it easier to look at a database comparison tool which will build
migration/synchronization scripts for you.

Simon

how to do this select query?

I have a table that looks something like this:

CREATE TABLE Oval_Import
(
IdNum INT NOT NULL PRIMARY KEY,
DOB datetime NOT NULL,
.
.
.
.
.
)

I'm trying to select all the records from the table (notice the DOB date field returns only the date part), but I ran into problems displaying the rest of the fields after the DOB.

I tried a select query like this but it returned every column 'again' after the DOB:

select IdNum, convert(varchar,DOB,111), * from Oval_Import;
I don't want to explicitly select each individual column after that either because there are very many after the DOB.

So how can I select the rest of the fields with the * but excluding the IdNum and the DOB columns?

It's a good practice to explicitly define each column in the select statement. Also, when you do special conversion on a column, you are essentially creating a new column. It's not possible to remove the original column from the select if you don't explicitly do so.|||

Ok, how about when I do an insert.

I can perform a query like this in sql server:

insert into Oval_Import (IdNum, DOB) values (226882, '1982/1/9');

but I can't do the following, it gives me a "Incorrect syntax near the keyword 'set'." error:

insert into Oval_Import set IdNum=226882, DOB='1982/1/9';
I know in mysql you can perform the latter query for insertion, but how can you do something similar in sql server? I need an insertion query where I can explicitly see which columns are being assigned to which values because my table have many columns.

|||

The INSERT statement syntax supported by SQL Server is the same as that of the ANSI SQL standards. You can only use the VALUES clause to specify the column values. There are extensions to the insert statement that allow you to do insert...Select or insert..exec for example. Perhaps you can do:

insert into Oval_Import (IdNum, DOB)
select 226882 as IdNum, '1982/1/9' as DOB

But note that the columns between the select and insert statement list is still by position.

How to do bulk Delete

I have a table that has a primary key made of 3 fields (we don't want to use a surrogate key in this situation). In a particular process there is a work table that contains these 3 PK fields and we want to bulk delete them from the base table. Without looping thru the work table, how can I write a Delete statement to delete records in the base table using the work table rows as the criteria?

This syntax illustrates what I want to do, but is not allowed by SQL.

DELETE bt.* FROM basetable bt
INNER JOIN #work wk On bt.fld1 = wk.fld1 And bt.fld2 = wk.fld2 And bt.fld3 = wk.fld3

The correct DELETE statement using TSQL extension is below:

DELETE basetable

FROM basetable bt
INNER JOIN #work wk

ON bt.fld1 = wk.fld1 And bt.fld2 = wk.fld2 And bt.fld3 = wk.fld3

But best is to use the ANSI SQL syntax which doesn't have any ambiguity:

DELETE FROM basetable

WHERE EXISTS(SELECT * FROM #work as wk

WHERE wk.fld1 = basetable.fld1

AND wk.fld2 = basetable.fld2

AND wk.fld3 = basetable.fld3)

|||Ahhh!!! I tried so many variations .... except that one. Thanks.