April 14, 2009 at 8:58 am
We need the DDL for the tables and some sample data for the tables (in a readily consumable format). The sample data should have data that should be deleted and not deleted from both tables. Be sure that the DDL for the tables includes the foreign key as well.
And, yes, it is possible to write a procedure to delete data from both tables.
April 14, 2009 at 9:22 am
Here is one table:
[dbo].[package_lists](
[packagelist_id] [int] NOT NULL,
[total_packages] [int] NOT NULL,
[shipper_symbol] [nvarchar](50) NOT NULL,
[shipper_friendlyname] [nvarchar](100) NOT NULL,
[shipdate] [smalldatetime] NOT NULL,
[service_symbol] [nvarchar](50) NOT NULL,
[service_friendlyname] [nvarchar](100) NOT NULL,
[processed_date] [datetime] NULL CONSTRAINT [processed_date_default] DEFAULT (getdate()),
CONSTRAINT [PK_package_lists] PRIMARY KEY CLUSTERED
(
[packagelist_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here is 2nd table:
[dbo].[packages](
[msn] [int] NOT NULL,
[nvarchar](50) NOT NULL,
[computer] [nvarchar](50) NOT NULL,
[packagelist_id] [int] NOT NULL,
[nofn_sequence] [int] NOT NULL,
[nofn_total] [int] NOT NULL,
[bundle_id] [int] NOT NULL,
[consignee_code] [nvarchar](50) NOT NULL,
[consignee_company] [nvarchar](50) NOT NULL,
[consignee_contact] [nvarchar](50) NOT NULL,
[consignee_address_line_1] [nvarchar](50) NOT NULL,
[consignee_address_line_2] [nvarchar](50) NOT NULL,
[consignee_city] [nvarchar](50) NOT NULL,
[consignee_stateprovince] [nvarchar](20) NOT NULL,
[consignee_postalcode] [nvarchar](35) NOT NULL,
[consignee_country_iso] [nvarchar](3) NOT NULL,
[consignee_country_friendlyname] [nvarchar](50) NOT NULL,
[consignee_phone] [nvarchar](20) NOT NULL,
[consignee_residential_flag] [bit] NOT NULL,
[consignee_tax_id] [nvarchar](20) NOT NULL,
[payment_type_symbol] [nvarchar](50) NOT NULL,
[payment_type_friendlyname] [nvarchar](50) NOT NULL,
[consignee_account] [nvarchar](50) NOT NULL,
[third_party_billing_flag] [bit] NOT NULL,
[third_party_billing_account] [nvarchar](50) NOT NULL,
[third_party_billing_code] [nvarchar](50) NOT NULL,
[third_party_billing_company] [nvarchar](50) NOT NULL,
[third_party_billing_contact] [nvarchar](50) NOT NULL,
[third_party_billing_address_line_1] [nvarchar](50) NOT NULL,
[third_party_billing_address_line_2] [nvarchar](50) NOT NULL,
[third_party_billing_city] [nvarchar](50) NOT NULL,
[third_party_billing_stateprovince] [nvarchar](50) NOT NULL,
[third_party_billing_postalcode] [nvarchar](50) NOT NULL,
[third_party_billing_country_iso] [nvarchar](3) NOT NULL,
[third_party_billing_country_friendlyname] [nvarchar](50) NOT NULL,
[third_party_billing_phone] [nvarchar](50) NOT NULL,
[third_party_billing_residential_flag] [bit] NOT NULL,
[package_type_symbol] [nvarchar](20) NOT NULL,
[package_type_friendlyname] [nvarchar](50) NOT NULL,
[weight] [float] NOT NULL,
[weight_units] [smallint] NOT NULL,
[dimensions] [nvarchar](50) NOT NULL,
[dimension_units] [smallint] NOT NULL,
[declared_value] [money] NOT NULL,
[declared_value_currency] [nvarchar](3) NOT NULL,
[cod_amount_goods] [money] NOT NULL,
[cod_amount_goods_currency] [nvarchar](3) NOT NULL,
[cod_amount_collect] [money] NOT NULL,
[cod_amount_collect_currency] [nvarchar](3) NOT NULL,
[cod_payment_method] [smallint] NOT NULL,
[user_handling_charge] [money] NOT NULL,
[user_handling_charge_currency] [nvarchar](3) NOT NULL,
[oversize_flag] [bit] NOT NULL,
[additional_handling_flag] [bit] NOT NULL,
[hazmat_flag] [bit] NOT NULL,
[hazmat_id] [nvarchar](50) NOT NULL,
[hazmat_description] [nvarchar](50) NOT NULL,
[hazmat_class] [nvarchar](50) NOT NULL,
[hazmat_quantity] [float] NOT NULL,
[hazmat_quantity_type] [smallint] NOT NULL,
[hazmat_quantity_units] [smallint] NOT NULL,
[hazmat_packing] [nvarchar](50) NOT NULL,
[hazmat_packing_group] [smallint] NOT NULL,
[hazmat_label] [int] NOT NULL,
[hazmat_cargo_flag] [bit] NOT NULL,
[hazmat_accessible_flag] [bit] NOT NULL,
[proof_flag] [bit] NOT NULL,
[proof_require_signature_flag] [bit] NOT NULL,
[proof_require_signature_adult_flag] [bit] NOT NULL,
[saturday_delivery_flag] [bit] NOT NULL,
[void_flag] [bit] NOT NULL,
[commitment_symbol] [nvarchar](50) NOT NULL,
[commitment_friendlyname] [nvarchar](50) NOT NULL,
[arrival_date] [smalldatetime] NULL,
[results_currency] [nvarchar](3) NOT NULL,
[base] [money] NOT NULL,
[special] [money] NOT NULL,
[discount] [money] NOT NULL,
[vat] [money] NOT NULL,
[total] [money] NOT NULL,
[dual_service_symbol] [nvarchar](50) NOT NULL,
[dual_service_friendlyname] [nvarchar](100) NOT NULL,
[dual_results_currency] [nvarchar](3) NOT NULL,
[dual_base] [money] NOT NULL,
[dual_special] [money] NOT NULL,
[dual_discount] [money] NOT NULL,
[dual_vat] [money] NOT NULL,
[dual_total] [money] NOT NULL,
[hundredweight_rated_flag] [bit] NOT NULL,
[declared_value_fee] [money] NOT NULL,
[return_delivery_fee] [money] NOT NULL,
[saturday_delivery_fee] [money] NOT NULL,
[saturday_pickup_fee] [money] NOT NULL,
[proof_fee] [money] NOT NULL,
[proof_require_signature_fee] [money] NOT NULL,
[additional_handling_fee] [money] NOT NULL,
[oversize_fee] [money] NOT NULL,
[hazmat_fee] [money] NOT NULL,
[cod_fee] [money] NOT NULL,
[cod_number] [nvarchar](15) NOT NULL,
[description] [nvarchar](50) NOT NULL,
[carrier_instructions] [nvarchar](100) NOT NULL,
[tracking_number] [nvarchar](35) NOT NULL,
[bar_code] [nvarchar](100) NOT NULL,
[zone] [nvarchar](10) NOT NULL,
[item_number] [nvarchar](50) NOT NULL,
[sub_number] [nvarchar](50) NOT NULL,
[commodity_class_symbol] [nvarchar](50) NOT NULL,
[commodity_class_friendlyname] [nvarchar](50) NOT NULL,
[waybill_bol_number] [nvarchar](50) NOT NULL,
[bol_comment] [nvarchar](50) NOT NULL,
[sed_method] [int] NOT NULL,
[sed_exemption_number] [nvarchar](20) NOT NULL,
[cert_of_origin_method] [int] NOT NULL,
[parties_related_flag] [bit] NOT NULL,
[documents_only_flag] [bit] NOT NULL,
[declared_value_customs] [money] NOT NULL,
[declared_value_customs_currency] [nvarchar](3) NOT NULL,
[consignee_customs_id] [nvarchar](20) NOT NULL,
[export_reason] [nvarchar](100) NOT NULL,
[ultimate_dest_country_iso] [nvarchar](3) NOT NULL,
[ultimate_dest_country] [nvarchar](50) NOT NULL,
[closed_manifest_flag] [bit] NOT NULL,
[closed_manifest_symbol] [nvarchar](30) NOT NULL,
[closed_manifest_friendlyname] [nvarchar](50) NOT NULL,
[reference_1] [nvarchar](50) NOT NULL,
[reference_2] [nvarchar](50) NOT NULL,
[reference_3] [nvarchar](50) NOT NULL,
[reference_4] [nvarchar](50) NOT NULL,
[reference_5] [nvarchar](50) NOT NULL,
[reference_6] [nvarchar](50) NOT NULL,
[reference_7] [nvarchar](50) NOT NULL,
[reference_8] [nvarchar](50) NOT NULL,
[reference_9] [nvarchar](50) NOT NULL,
[reference_10] [nvarchar](50) NOT NULL,
[reference_11] [nvarchar](50) NOT NULL,
[reference_12] [nvarchar](50) NOT NULL,
[reference_13] [nvarchar](50) NOT NULL,
[reference_14] [nvarchar](50) NOT NULL,
[reference_15] [nvarchar](50) NOT NULL,
[reference_16] [nvarchar](50) NOT NULL,
[reference_17] [nvarchar](50) NOT NULL,
[reference_18] [nvarchar](50) NOT NULL,
[reference_19] [nvarchar](50) NOT NULL,
[reference_20] [nvarchar](50) NOT NULL,
[terms_of_sale_symbol] [nvarchar](50) NOT NULL,
[terms_of_sale_friendlyname] [nvarchar](50) NOT NULL,
[return_address_flag] [bit] NOT NULL,
[return_address_code] [nvarchar](50) NOT NULL,
[return_company] [nvarchar](50) NOT NULL,
[return_contact] [nvarchar](50) NOT NULL,
[return_address_line_1] [nvarchar](50) NOT NULL,
[return_address_line_2] [nvarchar](50) NOT NULL,
[return_city] [nvarchar](50) NOT NULL,
[return_stateprovince] [nvarchar](20) NOT NULL,
[return_postalcode] [nvarchar](35) NOT NULL,
[return_country_iso] [nvarchar](3) NOT NULL,
[return_country_friendlyname] [nvarchar](50) NOT NULL,
[return_phone] [nvarchar](20) NOT NULL,
[return_residential_flag] [bit] NOT NULL,
[special_delivery_flag] [bit] NOT NULL,
[special_delivery_fee] [money] NOT NULL,
[export_information_code] [nvarchar](20) NOT NULL,
[comments] [nvarchar](255) NOT NULL,
[importer_of_record_account] [nvarchar](50) NOT NULL,
[importer_of_record_code] [nvarchar](50) NOT NULL,
[importer_of_record_company] [nvarchar](50) NOT NULL,
[importer_of_record_contact] [nvarchar](50) NOT NULL,
[importer_of_record_address_line_1] [nvarchar](50) NOT NULL,
[importer_of_record_address_line_2] [nvarchar](50) NOT NULL,
[importer_of_record_city] [nvarchar](50) NOT NULL,
[importer_of_record_stateprovince] [nvarchar](20) NOT NULL,
[importer_of_record_postalcode] [nvarchar](35) NOT NULL,
[importer_of_record_country_iso] [nvarchar](3) NOT NULL,
[importer_of_record_country_friendlyname] [nvarchar](50) NOT NULL,
[importer_of_record_phone] [nvarchar](20) NOT NULL,
[importer_of_record_residential_flag] [bit] NOT NULL,
[importer_of_record_tax_id] [nvarchar](50) NOT NULL,
[world_ease_flag] [bit] NOT NULL,
[world_ease_code] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_packages] PRIMARY KEY CLUSTERED
(
[msn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[packages] WITH CHECK ADD CONSTRAINT [FK_packages_package_lists] FOREIGN KEY([packagelist_id])
REFERENCES [dbo].[package_lists] ([packagelist_id])
GO
ALTER TABLE [dbo].[packages] CHECK CONSTRAINT [FK_packages_package_lists]
April 14, 2009 at 11:01 am
GilaMonster (4/13/2009)
There's a lot more to consider than performance.Truncate requires ALTER TABLE permissions, delete requires DELETE permissions
This is called CONTROL permission, and yes the truncate will fail without it.
Truncate will also reset the identity column back to 1 (or whatever the initial value declared was) DELETE will not.
The probability of survival is inversely proportional to the angle of arrival.
April 15, 2009 at 6:57 am
also, if i remember correctly truncate will reset any seed\incremental value on ID columns,etc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 15, 2009 at 12:39 pm
SA (4/14/2009)
You can delete the data from the table which has the foreign key or you can disable constraint checks and then delete.So it really depends on your database design.
Or the "child" table can have the ON DELETE CASCADE clause as part of the FK constraint (to the parent table). So when the "parent" row is deleted, the associated entries are deleted from their respecitive tables. However, before you change the FK constraint to perform this, make sure that it is OK from a business rules standpoint!
April 16, 2009 at 5:33 am
/*
After you have run the TRUNCATE TABLE statement, it is important
then to manually update the statistics on this table using
UPDATE STATISTICS. This is because running TRUNCATE TABLE will
not reset the statistics for the table, which means that as you
add data to the table, the statistics for that table will be
incorrect for a time period. Of course, if you wait long enough,
and if you have Auto Update Statistics turned on for the database,
then the statistics will eventually catch up with themselves.
But this may not happen quickly, resulting in slowly performing
queries because the Query Optimizer is using out-of-date statistics.
*****
If you use TRUNCATE TABLE instead of DELETE to remove all
of the rows of a table, TRUNCATE TABLE will not work when
there are Foreign Key references present for that table.
A workaround is to DROP the constraints before firing the
TRUNCATE. Here's a generic script that will drop all existing
Foreign Key constraints on a specific table:
*/
CREATE TABLE dropping_constraints
(
cmd VARCHAR(8000)
)
INSERT INTO dropping_constraints
SELECT
'ALTER TABLE [' +
t2.Table_Name +
'] DROP CONSTRAINT ' +
t1.Constraint_Name
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS t1
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t2
ON
t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
WHERE t2.TABLE_NAME='your_tablename_goes_here'
DECLARE @stmt VARCHAR(8000)
DECLARE @rowcnt INT
SELECT TOP 1 @stmt=cmd FROM dropping_constraints
SET @rowcnt=@@ROWCOUNT
WHILE @rowcnt0
BEGIN
EXEC (@stmt)
SET @stmt = 'DELETE FROM dropping_constraints WHERE cmd ='+ QUOTENAME(@stmt,'''')
EXEC (@stmt)
SELECT TOP 1 @stmt=cmd FROM dropping_constraints
SET @rowcnt=@@ROWCOUNT
END
DROP TABLE dropping_constraints
April 16, 2009 at 9:52 am
All I am trying to do delete data from the table where date >=30
Correct me if I'm wrong, but as far as I'm aware, you can't perform a conditional TRUNCATE, so your only option would be to use the DELETE command.
April 16, 2009 at 9:54 am
GilaMonster (4/13/2009)
There's a lot more to consider than performance.Truncate deletes all the data in the table. Delete can have a where clause allowing conditional deletes.
Truncate does not fire triggers, delete does.
Truncate is not allowed on a replicated table or one with foreign key constraints, delete is.
Truncate requires ALTER TABLE permissions, delete requires DELETE permissions
Truncate also will RESEED a table.
April 16, 2009 at 9:56 am
I guess I should have read all pages in the thread before the last two posts to reduce post redundancy:-)
April 16, 2009 at 1:54 pm
rgillings (4/16/2009)
Truncate also will RESEED a table.
that is correct
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply