April 29, 2009 at 8:34 am
I'm very new at SQL and I'm trying to get results from multiple tables into a new table with the common factor coming from a column in one of the tables.
Let me break it down....
I have 12 tables, each have different column names BUT has one in common called "cubs_id"
I want to select from all 12 tables ONLY if the "group_name" column in one of the tables is equal to "Closed Cases" and then I want to grab all those entries and grab the same entries in the other tables by using the common column "cubs_id".
Please help me out if you can....
Thanks
April 29, 2009 at 9:22 am
You'll want to join the tables using that column, then put the other part in the Where clause.
If you can put the create scripts for the tables into the forum, and list what columns you want in the final query, we can help you write it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2009 at 10:45 am
Here is what I would like to happen....
I have an SQL database with 12 tables. All tables are linked to one commmon column "cubs_case_id". What I am trying to do is to export the data in groups. For example, we have 60,000 records and in group 1 we want to export only 10,000 using a column "group_name" that exists in 1 of the tables "Case_Basic". What I want as a result is to have 12 new tables with just those 10,000 records and then go forard with other groups as I need them. Does this make sense to you?
April 29, 2009 at 12:46 pm
GSquared (4/29/2009)
If you can put the create scripts for the tables into the forum, and list what columns you want in the final query, we can help you write it.
We'd like to help, but we are all volunteers here, and we ask for your help in doing so. If you can include the DDL and DML statements to create your tables / data, this means that we can see exactly what you are trying to do. So, please provide the CREATE TABLE and INSERT statements for the tables in question. See the link in my signature for more information.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 29, 2009 at 12:52 pm
mmclaughlin (4/29/2009)
Here is what I would like to happen....I have an SQL database with 12 tables. All tables are linked to one commmon column "cubs_case_id". What I am trying to do is to export the data in groups. For example, we have 60,000 records and in group 1 we want to export only 10,000 using a column "group_name" that exists in 1 of the tables "Case_Basic". What I want as a result is to have 12 new tables with just those 10,000 records and then go forard with other groups as I need them. Does this make sense to you?
That does make sense, but I can't take it much further than that without more data, like the table structure.
What you're trying to do is relatively easy, and if you want to figure it out on your own (often the way I go, just because I consider that kind of thing to be fun), then you should be able to do so pretty easily just by looking up Select in Books Online and following it down from there. If, on the other hand, you want more substantial help on it, you'll need to give us a look at what the tables look like.
If you aren't sure how to do that:
In Management Studio, connect to the server you want to do this work on, and navigate through the tree-view to the database and table you want want to start with.
Right-click the table, and select "Script Table as", then "Create to", then "Clipboard", then paste that into the forum.
Repeat for each table involved here.
If it's a lot of text, it might be easier to do the copy-and-paste into a text file, in Notepad or some such, and then save the file, and upload it to the forum.
Either way, we can then see what you're talking about working on, and help to write the code to do what you need. Without that, we'd be guessing, and it wouldn't help you much.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2009 at 1:15 pm
USE [SK_Servicing]
GO
/****** Object: Table [dbo].[Case_Basic] Script Date: 04/29/2009 15:09:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Case_Basic](
[cubs_case_guid] [uniqueidentifier] NOT NULL,
[cubs_case_id] [int] NOT NULL,
[case_status_code_id] [varchar](256) NULL,
[case_status_code_description] [varchar](256) NULL,
[group_name] [varchar](256) NULL,
[product_code_id] [char](2) NOT NULL,
[is_open_yn] [char](1) NULL,
[siebel_id] [varchar](256) NULL,
[setup_dt] [datetime] NULL,
[closed_dt] [datetime] NULL,
[case_sold_addendum_needed_yn] [char](1) NULL,
[case_sold_addendum_sent] [datetime] NULL,
[case_sold_to_foothill] [datetime] NULL,
[coa_status] [varchar](256) NULL,
[ph_status] [varchar](256) NULL,
[doc_status] [varchar](256) NULL,
[aoa_status] [varchar](256) NULL,
[ww_work_order_status] [varchar](256) NULL,
[supportwatch_commission_chg_dt] [datetime] NULL,
[supportwatch_cp_refused_dt] [datetime] NULL,
[supportwatch_dt_in] [datetime] NULL,
[beacon_scr] [int] NULL,
[mortgage_yn] [char](1) NULL,
[pay_class] [varchar](256) NULL,
[ccr_close_reason] [varchar](20) NULL,
[non_close_reason] [varchar](20) NULL,
[cancelled_date] [datetime] NULL,
[assigned_to] [varchar](256) NULL,
[assigned_date] [datetime] NULL,
[created_date] [datetime] NOT NULL,
[updated_date] [datetime] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
[siebel_account_id] [varchar](15) NULL,
[desk_num_id] [varchar](256) NULL,
CONSTRAINT [PK_Case_Basic] PRIMARY KEY CLUSTERED
(
[cubs_case_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-----------------------------------
USE [SK_Servicing]
GO
/****** Object: Table [dbo].[Case_Financial] Script Date: 04/29/2009 15:12:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Case_Financial](
[cubs_case_financial_guid] [uniqueidentifier] NOT NULL,
[cubs_case_id] [int] NOT NULL,
[k_claim_amt] [numeric](38, 4) NULL,
[orig_claim_amt] [numeric](38, 4) NULL,
[arrears_calc_from_dt] [datetime] NULL,
[arrears_calc_to_dt] [datetime] NULL,
[arrears_contract_balance] [numeric](38, 4) NULL,
[arrears_dt_payout_projected] [datetime] NULL,
[initial_balance_amt] [numeric](38, 4) NULL,
[initial_interest_amt] [numeric](38, 4) NULL,
[initial_principle_amt] [numeric](38, 4) NULL,
[cse_balance_amt] [numeric](38, 4) NULL,
[cse_interest_amt] [numeric](38, 4) NULL,
[cse_principle_amt] [numeric](38, 4) NULL,
[monthly_interest_amt] [numeric](38, 4) NULL,
[ncp_balance_amt] [numeric](38, 4) NULL,
[ncp_interest_amt] [numeric](38, 4) NULL,
[ncp_principle_amt] [numeric](38, 4) NULL,
[total_obligation_amt] [numeric](38, 4) NULL,
[current_monthly_sppt_dt] [datetime] NULL,
[total_monthly_wagewithholding_amt] [numeric](38, 4) NULL,
[current_monthly_sppt_due_1] [numeric](38, 4) NULL,
[current_monthly_sppt_due_2] [numeric](38, 4) NULL,
[current_monthly_sppt_due_3] [numeric](38, 4) NULL,
[current_monthly_sppt_due_4] [numeric](38, 4) NULL,
[current_monthly_sppt_due_5] [numeric](38, 4) NULL,
[arrears_mnthly_amt_child_1] [numeric](38, 4) NULL,
[arrears_mnthly_amt_child_2] [numeric](38, 4) NULL,
[arrears_mnthly_amt_child_3] [numeric](38, 4) NULL,
[arrears_mnthly_amt_child_4] [numeric](38, 4) NULL,
[arrears_mnthly_amt_child_5] [numeric](38, 4) NULL,
[ww_final_stop_dt] [datetime] NULL,
[ww_stop_dt_period_1] [datetime] NULL,
[ww_stop_dt_period_2] [datetime] NULL,
[ww_stop_dt_period_3] [datetime] NULL,
[ww_stop_dt_period_4] [datetime] NULL,
[ww_stop_dt_period_5] [datetime] NULL,
[current_interest_rate] [numeric](38, 4) NULL,
[interest_state_id] [char](2) NULL,
[interest_sum_amt] [numeric](38, 4) NULL,
[judgement_interest_rate_simple_yn] [char](1) NULL,
[first_payment_amt] [numeric](38, 4) NULL,
[first_payment_dt] [datetime] NULL,
[admin_fee_amt] [numeric](38, 4) NULL,
[admin_fee_paid_amt] [numeric](38, 4) NULL,
[admin_fee_balance_amt] [numeric](38, 4) NULL,
[pass_thru_amt] [numeric](38, 4) NULL,
[pass_thru_dt] [datetime] NULL,
[ar_verified_amt] [numeric](18, 2) NULL,
[ar_verified_dt] [datetime] NULL,
[contract_template_id] [int] NULL,
[cp_fee] [numeric](18, 2) NULL,
[contract_description] [varchar](25) NULL,
[cp_fee_comm_rate] [numeric](5, 4) NULL,
[principal_comm_rate] [numeric](18, 2) NULL,
[cp_fee_collected] [numeric](18, 2) NULL,
[nsf_fee] [numeric](18, 2) NULL,
[last_payment_dt] [datetime] NULL,
[last_payment_amt] [numeric](18, 2) NULL,
[assigned_amount] [numeric](18, 2) NULL,
[assigned_amount_collected] [numeric](18, 2) NULL,
[interest_owing] [numeric](18, 2) NULL,
[interest_collected] [numeric](18, 2) NULL,
[cancelled_assigned_amt] [numeric](18, 2) NULL,
[cancelled_other_amt] [numeric](18, 2) NULL,
[court_owing] [numeric](18, 2) NULL,
[court_collected] [numeric](18, 2) NULL,
[attorney_collected] [numeric](18, 2) NULL,
[attorney_owing] [numeric](18, 2) NULL,
[misc_owing] [numeric](18, 2) NULL,
[misc_collected] [numeric](18, 2) NULL,
[cubs_contract_balance] [numeric](18, 2) NULL,
[created_date] [datetime] NOT NULL,
[updated_date] [datetime] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Case_Financial] PRIMARY KEY CLUSTERED
(
[cubs_case_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
------------------
USE [SK_Servicing]
GO
/****** Object: Table [dbo].[Children_Basic] Script Date: 04/29/2009 15:12:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Children_Basic](
[cubs_children_guid] [uniqueidentifier] NOT NULL,
[cubs_case_id] [int] NOT NULL,
[child_1_name] [varchar](256) NULL,
[child_1_ssn] [char](11) NULL,
[child_1_gender] [char](1) NULL,
[child_1_dob] [datetime] NULL,
[child_1_age] [int] NULL,
[child_1_lives_with] [varchar](256) NULL,
[child_1_emancipates] [datetime] NULL,
[child_2_name] [varchar](256) NULL,
[child_2_ssn] [char](11) NULL,
[child_2_gender] [char](1) NULL,
[child_2_age] [int] NULL,
[child_2_dob] [datetime] NULL,
[child_2_lives_with] [varchar](256) NULL,
[child_2_emancipates] [datetime] NULL,
[child_3_name] [varchar](256) NULL,
[child_3_ssn] [char](11) NULL,
[child_3_gender] [char](1) NULL,
[child_3_age] [int] NULL,
[child_3_dob] [datetime] NULL,
[child_3_lives_with] [varchar](256) NULL,
[child_3_emancipates] [datetime] NULL,
[child_4_name] [varchar](256) NULL,
[child_4_ssn] [char](11) NULL,
[child_4_gender] [char](1) NULL,
[child_4_age] [int] NULL,
[child_4_dob] [datetime] NULL,
[child_4_lives_with] [varchar](256) NULL,
[child_4_emancipates] [datetime] NULL,
[child_5_name] [varchar](256) NULL,
[child_5_ssn] [char](11) NULL,
[child_5_gender] [char](1) NULL,
[child_5_age] [int] NULL,
[child_5_dob] [datetime] NULL,
[child_5_lives_with] [varchar](256) NULL,
[child_5_emancipates] [datetime] NULL,
[child_cont_posession_yn] [char](1) NULL,
[child_custody_notes] [varchar](256) NULL,
[created_date] [datetime] NOT NULL,
[updated_date] [datetime] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Children_Basic] PRIMARY KEY CLUSTERED
(
[cubs_case_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-----------------------------------
USE [SK_Servicing]
GO
/****** Object: Table [dbo].[Court_Order_Basic] Script Date: 04/29/2009 15:12:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Court_Order_Basic](
[cubs_case_gov_guid] [uniqueidentifier] NOT NULL,
[cubs_case_id] [int] NOT NULL,
[court_order_begin_dt] [datetime] NULL,
[court_order_signed_dt] [datetime] NULL,
[court_order_amt] [numeric](18, 2) NULL,
[court_order_recurring_amt] [numeric](18, 2) NULL,
[court_order_frequency_type_id] [tinyint] NULL,
[court_order_per_child_yn] [char](1) NULL,
[court_order_mnthly_arrears_amt] [numeric](18, 2) NULL,
[court_order_ttl_mnthly_amt] [numeric](18, 2) NULL,
[court_order_last_modification_dt] [datetime] NULL,
[court_order_last_modification_amt] [numeric](18, 2) NULL,
[court_order_last_judgement_amt] [numeric](18, 2) NULL,
[court_order_last_judgement_dt] [datetime] NULL,
[court_order_cause_num] [varchar](256) NULL,
[court_order_tx_case_id] [varchar](256) NULL,
[court_order_tx_cin] [varchar](256) NULL,
[court_order_title] [varchar](256) NULL,
[court_order_judicial_dist] [varchar](256) NULL,
[court_order_cp_plantiff_yn] [char](1) NULL,
[court_order_ww_language_yn] [char](1) NULL,
[created_date] [datetime] NOT NULL,
[updated_date] [datetime] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Court_Order_Basic] PRIMARY KEY CLUSTERED
(
[cubs_case_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-----------------------------------
USE [SK_Servicing]
GO
/****** Object: Table [dbo].[Custodial_Parent] Script Date: 04/29/2009 15:12:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Custodial_Parent](
[cubs_cp_guid] [uniqueidentifier] NOT NULL,
[cubs_case_id] [int] NOT NULL,
[first_name] [varchar](256) NULL,
[last_name] [varchar](256) NULL,
[suffix] [varchar](256) NULL,
[ssn] [varchar](256) NULL,
[dob] [datetime] NULL,
[addr1] [varchar](256) NULL,
[addr2] [varchar](256) NULL,
[city] [varchar](256) NULL,
[state_id] [char](2) NULL,
[zip] [varchar](10) NULL,
[phone] [varchar](50) NULL,
[alt_phone] [varchar](50) NULL,
[work_phone] [varchar](50) NULL,
[work_phone_ext] [varchar](25) NULL,
[fax] [char](12) NULL,
[email_addr] [varchar](256) NULL,
[employer_name] [varchar](256) NULL,
[gender] [varchar](256) NULL,
[created_date] [datetime] NOT NULL,
[updated_date] [datetime] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Custodial_Parent] PRIMARY KEY CLUSTERED
(
[cubs_case_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-------------------
USE [SK_Servicing]
GO
/****** Object: Table [dbo].[Employers] Script Date: 04/29/2009 15:13:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employers](
[cubs_case_id] [int] NOT NULL,
[employer_guid] [uniqueidentifier] NOT NULL,
[corporate_name] [varchar](256) NULL,
[verification_status] [varchar](50) NULL,
[employer_status] [varchar](50) NULL,
[verified_date] [datetime] NULL,
[data_asset_source] [varchar](50) NULL,
[primary_employer_yn] [char](1) NULL,
[self_employed_yn] [char](1) NULL,
[job_title] [varchar](256) NULL,
[ww_cooperative_yn] [char](1) NULL,
[archive_reason] [varchar](50) NULL,
[employer_archive_date] [datetime] NULL,
[contact_name] [varchar](256) NULL,
[attn] [varchar](256) NULL,
[address1] [varchar](256) NULL,
[address2] [varchar](256) NULL,
[city] [varchar](256) NULL,
[state_id] [char](2) NULL,
[zip] [varchar](10) NULL,
[phone1] [char](12) NULL,
[ext1] [int] NULL,
[fax] [char](12) NULL,
[varchar](256) NULL,
[phone2] [char](12) NULL,
[ext2] [int] NULL,
[created_date] [datetime] NOT NULL,
[updated_date] [datetime] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Employers] PRIMARY KEY CLUSTERED
(
[employer_guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
--------------------------------
USE [SK_Servicing]
GO
/****** Object: Table [dbo].[Gov_Entity_Library] Script Date: 04/29/2009 15:13:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Gov_Entity_Library](
[ENTITY_ID] [int] NOT NULL,
[CONTACT_ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
[Class] [varchar](10) NULL,
[Contact] [varchar](50) NULL,
[Attn] [varchar](50) NULL,
[Addr1] [varchar](50) NULL,
[Addr2] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [char](2) NULL,
[Zip] [varchar](10) NULL,
[County] [varchar](50) NULL,
[Phone] [varchar](20) NULL,
[Ext] [varchar](10) NULL,
[Fax] [varchar](20) NULL,
[Email] [varchar](100) NULL,
[COA_YN] [char](1) NULL,
[PH_YN] [char](1) NULL,
[REG_YN] [char](1) NULL,
[DOC_YN] [char](1) NULL,
[COURT_YN] [char](1) NULL,
[COA_COOP_TYPE] [varchar](10) NULL,
[COA_COOP_CONFIRMED] [datetime] NULL,
[COA_REQUIRE_ORIGINAL_YN] [char](1) NULL,
[COA_REQUIRE_NOTARIZED_YN] [char](1) NULL,
[PH_COOP_TYPE_YN] [char](1) NULL,
[PH_COOP_CONFIRMED] [datetime] NULL,
[PH_REQUIRE_ORIGINAL_YN] [char](1) NULL,
[PH_REQUIRE_NOTARIZED_YN] [char](1) NULL,
[PH_COST] [decimal](18, 2) NULL,
[PH_COST_PER_PAGE_YN] [char](1) NULL,
[PH_HAVE_ESCROW_ACCOUNT_YN] [char](1) NULL,
[DOC_COOP_TYPE_YN] [char](1) NULL,
[DOC_COOP_CONFIRMED] [datetime] NULL,
[DOC_REQUIRE_ORIGINAL_YN] [char](1) NULL,
[DOC_REQUIRE_NOTARIZED_YN] [char](1) NULL,
[DOC_COST] [decimal](18, 2) NULL,
[DOC_COST_PER_PAGE_YN] [char](1) NULL,
[DOC_HAVE_ESCROW_ACCOUNT_YN] [char](1) NULL,
[WEBSITE] [varchar](100) NULL,
[CRT_EXCRO_YN] [char](1) NULL,
[CRT_BILLING_YN] [char](1) NULL,
[CRT_SPECIAL] [char](1) NULL,
[CRT_VERIFIED] [datetime] NULL,
[PH_MO_ONLY_YN] [char](1) NULL,
[DOC_MO_ONLY_YN] [char](1) NULL,
[DOC_WILL_BILL_YN] [char](1) NULL,
[DOC_WEBSITE] [varchar](100) NULL,
[DOC_NO_COST_YN] [char](1) NULL,
[PH_NO_COST_YN] [char](1) NULL,
[PH_WILL_BILL_YN] [char](1) NULL,
[COA_SEE_NOTES_YN] [char](1) NULL,
[COA_FAX_INITIAL_REQUEST_YN] [char](1) NULL,
[COA_SUPPRESS_FOLLOW_UP_YN] [char](1) NULL,
[DOC_SEE_NOTES_YN] [char](1) NULL,
[DOC_CERT_COST] [decimal](18, 2) NULL,
[DOC_EXMPFD_COST] [decimal](18, 2) NULL,
[PH_SEE_NOTES_YN] [char](1) NULL,
[PH_WEBSITE] [varchar](100) NULL,
[DOC_UP_FRONT_FEE_YN] [char](1) NULL,
[DOC_ESCROW_ACCT_NO] [varchar](50) NULL,
[DOC_RESEARCH_FEE] [decimal](18, 2) NULL,
[PH_UP_FROM_FEE_YN] [char](1) NULL,
[PH_ESCROW_ACCT_NO] [varchar](50) NULL,
[COA_REQ_ALLOW_FAX_YN] [char](1) NULL,
[PH_REQ_ALLOW_FAX_YN] [char](1) NULL,
[DOC_REQ_ALLOW_FAX_YN] [char](1) NULL,
[CREATED_DATE] [datetime] NOT NULL,
[UPDATED_DATE] [datetime] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Gov_Entity_Library] PRIMARY KEY CLUSTERED
(
[CONTACT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
---------------------
USE [SK_Servicing]
GO
/****** Object: Table [dbo].[Lien_Activity] Script Date: 04/29/2009 15:13:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Lien_Activity](
[cubs_lien_activity_guid] [uniqueidentifier] NOT NULL,
[cubs_case_id] [int] NOT NULL,
[interstate_lien_rqsted_dt] [datetime] NULL,
[interstate_lien_sent_dt] [datetime] NULL,
[interstate_lien_completed_dt] [datetime] NULL,
[tx_lien_rqsted_dt] [datetime] NULL,
[tx_lien_sent_dt] [datetime] NULL,
[tx_lien_completed_dt] [datetime] NULL,
[lien_status] [varchar](256) NULL,
[lien_payment_amt] [numeric](38, 4) NULL,
[lien_account_num] [varchar](256) NULL,
[lien_amt] [numeric](38, 4) NULL,
[lien_established_dt] [datetime] NULL,
[lien_holder_account_num] [varchar](256) NULL,
[lien_holder_description] [varchar](256) NULL,
[lien_holder_name] [varchar](256) NULL,
[lien_holder_addr] [varchar](256) NULL,
[lien_holder_citystzip] [varchar](256) NULL,
[lien_holder_phone] [char](12) NULL,
[lien_recorder_key] [varchar](256) NULL,
[lien_recorder_name] [varchar](256) NULL,
[lien_recorder_title] [varchar](256) NULL,
[lien_recorder_attn] [varchar](256) NULL,
[lien_recorder_contact] [varchar](256) NULL,
[lien_recorder_addr] [varchar](256) NULL,
[lien_recorder_citystzip] [varchar](256) NULL,
[lien_recorder_phone] [varchar](256) NULL,
[lien_recorder_fax] [varchar](256) NULL,
[lien_recorder_email] [varchar](256) NULL,
[created_date] [datetime] NOT NULL,
[updated_date] [datetime] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Lien_Activity] PRIMARY KEY CLUSTERED
(
[cubs_case_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
--------------------------
USE [SK_Servicing]
GO
/****** Object: Table [dbo].[Memo] Script Date: 04/29/2009 15:13:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Memo](
[row_guid] [uniqueidentifier] NOT NULL,
[cubs_case_id] [int] NOT NULL,
[fieldnbr] [int] NOT NULL,
[memo_type] [char](1) NOT NULL,
[memo_date] [datetime] NULL,
[memo_text] [varchar](255) NULL,
[created_date] [datetime] NOT NULL,
[updated_date] [datetime] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Memo] PRIMARY KEY CLUSTERED
(
[row_guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
---------------------------
USE [SK_Servicing]
GO
/****** Object: Table [dbo].[NonCustodial_Parent] Script Date: 04/29/2009 15:14:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NonCustodial_Parent](
[cubs_ncp_guid] [uniqueidentifier] NOT NULL,
[cubs_case_id] [int] NOT NULL,
[first_name] [varchar](256) NULL,
[middle_name] [varchar](256) NULL,
[last_name] [varchar](256) NULL,
[suffix] [varchar](256) NULL,
[goes_by] [varchar](256) NULL,
[ssn] [char](11) NULL,
[dob] [datetime] NULL,
[approximate_age] [int] NULL,
[race] [varchar](256) NULL,
[addr_is_current_yn] [char](1) NULL,
[primary_addr1] [varchar](256) NULL,
[primary_addr2] [varchar](256) NULL,
[primary_city] [varchar](256) NULL,
[primary_state_id] [char](2) NULL,
[primary_zip] [varchar](12) NULL,
[secondary_addr_1] [varchar](256) NULL,
[secondary_addr_2] [varchar](256) NULL,
[secondary_city] [varchar](256) NULL,
[secondary_state_id] [char](2) NULL,
[secondary_zip] [varchar](12) NULL,
[prospective_addr1] [varchar](256) NULL,
[prospective_addr2] [varchar](256) NULL,
[prospective_city_state_zip] [varchar](256) NULL,
[prospective_phone] [char](12) NULL,
[previous_addr] [varchar](256) NULL,
[previous_city] [varchar](256) NULL,
[previous_state_id] [char](2) NULL,
[prevous_zip] [varchar](12) NULL,
[previous_addr_to] [datetime] NULL,
[prevous_addr_from] [datetime] NULL,
[phone] [char](12) NULL,
[alt_phone] [char](12) NULL,
[email_addr] [varchar](256) NULL,
[gender] [varchar](256) NULL,
[estimated_wages_by_cp_amt] [varchar](50) NULL,
[work_history] [varchar](256) NULL,
[work_training] [varchar](256) NULL,
[remarried_yn] [char](1) NULL,
[remarried_spouse_name] [varchar](256) NULL,
[asset_information] [varchar](256) NULL,
[retirement_yn] [char](1) NULL,
[retirement_info] [varchar](256) NULL,
[professional_license_yn] [char](1) NULL,
[professional_license_num] [varchar](256) NULL,
[professional_license_state_id] [char](2) NULL,
[professional_license_type] [varchar](256) NULL,
[banks_yn] [char](1) NULL,
[bank_info_current_yn] [char](1) NULL,
[bank_account_num] [varchar](256) NULL,
[bank_name] [varchar](256) NULL,
[bank_branch] [varchar](256) NULL,
[inheritance_received_yn] [char](1) NULL,
[bank_location] [varchar](256) NULL,
[inheritance_received_dt] [datetime] NULL,
[inheritance_source] [varchar](256) NULL,
[inheritance_notes] [varchar](256) NULL,
[ncp_owns_land_yn] [char](1) NULL,
[ncp_claims_credit_yn] [char](1) NULL,
[ncp_credit_claim_amt] [numeric](38, 4) NULL,
[ncp_credit_claim_reason] [varchar](256) NULL,
[drivers_license_num] [varchar](256) NULL,
[drivers_license_state_id] [char](2) NULL,
[in_military] [char](1) NULL,
[in_military_desc] [varchar](256) NULL,
[jail_in_yn] [char](1) NULL,
[jail_city] [varchar](256) NULL,
[jail_county] [varchar](256) NULL,
[jail_st] [char](2) NULL,
[parole_yn] [char](1) NULL,
[probation_yn] [char](1) NULL,
[probation_city] [varchar](256) NULL,
[probation_county] [varchar](256) NULL,
[probation_state_id] [char](2) NULL,
[created_date] [datetime] NOT NULL,
[updated_date] [datetime] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_NonCustodial_Parent] PRIMARY KEY CLUSTERED
(
[cubs_case_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
------------------------------------
USE [SK_Servicing]
GO
/****** Object: Table [dbo].[Voluntary_Pay_Plan] Script Date: 04/29/2009 15:14:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Voluntary_Pay_Plan](
[cubs_case_id] [int] NOT NULL,
[expected_amt] [decimal](18, 2) NULL,
[frequency] [int] NULL,
[grace_period_days] [int] NULL,
[last_dt_pymt_made] [datetime] NULL,
[last_posting_dt] [datetime] NULL,
[last_pymt_amt] [decimal](18, 0) NULL,
[setup_dt] [datetime] NULL,
[to_collector_yn] [char](1) NULL,
[created_date] [datetime] NULL,
[updated_date] [datetime] NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Voluntary_Pay_Plan] PRIMARY KEY CLUSTERED
(
[cubs_case_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
---------------------------
USE [SK_Servicing]
GO
/****** Object: Table [dbo].[Wage_Withholding_Work_Orders] Script Date: 04/29/2009 15:14:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Wage_Withholding_Work_Orders](
[work_order_guid] [uniqueidentifier] NOT NULL,
[cubs_case_id] [int] NULL,
[work_order_status] [varchar](256) NULL,
[employer_guid] [uniqueidentifier] NULL,
[corporate_name] [varchar](256) NULL,
[started_dt] [datetime] NULL,
[ended_dt] [datetime] NULL,
[outcome] [varchar](256) NULL,
[vol_ww_rqstd_amt] [decimal](18, 2) NULL,
[activity_plan_name] [varchar](256) NULL,
[ww_mthly_ww_amt_rqstd] [decimal](18, 2) NULL,
[ww_payment_level] [varchar](256) NULL,
[ww_amt_rcvd] [decimal](18, 2) NULL,
[ww_amt_rcvd_dt] [datetime] NULL,
[ww_amt_rcvd_frequency] [varchar](256) NULL,
[ww_periodic_amt_expected] [decimal](18, 2) NULL,
[ww_amt_expected_dt] [datetime] NULL,
[created_date] [datetime] NOT NULL,
[updated_date] [datetime] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Wage_Withholding_Work_Orders] PRIMARY KEY CLUSTERED
(
[work_order_guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 98) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
April 29, 2009 at 3:09 pm
mmclaughlin (4/29/2009)
Here is what I would like to happen....I have an SQL database with 12 tables. All tables are linked to one commmon column "cubs_case_id". What I am trying to do is to export the data in groups. For example, we have 60,000 records and in group 1 we want to export only 10,000 using a column "group_name" that exists in 1 of the tables "Case_Basic". What I want as a result is to have 12 new tables with just those 10,000 records and then go forard with other groups as I need them. Does this make sense to you?
Okay, I see what you're doing here.
Do the tables you want to export to already exist, or do you want to create them at runtime? I'm assuming create them at runtime (when you want to run the export), so here's a sample of how to go about doing that:
select CB.*
into dbo.Children_Basic_X
from dbo.Children_Basic CB
where cubs_case_id in
(select cubs_case_id
from dbo.Case_Basic
where group_name = 'X');
Try that, see if it does what you want in that case. Of course, you'll have to put in a real value where I have the "X", to really test it.
Note that this won't create primary keys, etc., on the new tables, just columns of the appropriate data type. Alter Table commands after the creation can accomplish that part.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply