May 9, 2013 at 9:32 am
Ok so i am diving back into SQL now and I am stuck. I am using views to manipulate my data and get it to where i want it. But now I need to make it into a table... the problem is the column names are all different than the tables I have used previously and i want to make sure that all the data (from 2002 till now) are compatible and have the same format. So i need a way to append to these new tables to the old ones.
I have tried a few things but nothing seems to work. Any help will be appreciated.
May 9, 2013 at 10:23 am
slunt01 (5/9/2013)
Ok so i am diving back into SQL now and I am stuck. I am using views to manipulate my data and get it to where i want it. But now I need to make it into a table... the problem is the column names are all different than the tables I have used previously and i want to make sure that all the data (from 2002 till now) are compatible and have the same format. So i need a way to append to these new tables to the old ones.I have tried a few things but nothing seems to work. Any help will be appreciated.
With no more details than what you posted there is nothing anybody can do. We can't see what you see and have no idea what you are trying to do. You are going to have to explain this in a lot more detail before anybody can offer any advice.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2013 at 3:47 pm
are you creating one denormalized table using data from several views? i've done something like this recently and although it's labor intensive, it's not difficult to do. please provide more details so i don't steer you in the wrong direction.
Dana
"Drats! Foiled again!"May 10, 2013 at 9:49 am
I wish I could show screen shots for further help but I am unable to because of the company policies. I will try and post more details about it.
thank you.
May 10, 2013 at 9:51 am
slunt01 (5/10/2013)
I wish I could show screen shots for further help but I am unable to because of the company policies. I will try and post more details about it.thank you.
Even a better explanation would help. There just isn't anything resembling a clear picture of what you are trying to do here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 10, 2013 at 9:55 am
danawexler (5/9/2013)
are you creating one denormalized table using data from several views? i've done something like this recently and although it's labor intensive, it's not difficult to do. please provide more details so i don't steer you in the wrong direction.
Um I am not 100% sure what you mean by denormalized. (sorry I am very new to the SQL world)
I have a series of 5 views in which data is being pulled in and manipulated. Tmy final join is what I want to append into an existing table. I ran a "create table" for if so that you get the idea of the structures for both at the current state.
This is the format of the existing table:
CREATE TABLE [dbo].[curr_yrmo_master](
[membid] [char](33) NOT NULL,
[memb_keyid] [char](36) NULL,
[membname] [char](20) NULL,
[provid] [char](20) NULL,
[pcp_keyid] [char](36) NULL,
[provname] [char](20) NULL,
[vendor] [char](20) NULL,
[vendornm] [char](40) NULL,
[hpcode] [char](10) NOT NULL,
[opt] [char](20) NULL,
[hpname] [char](30) NOT NULL,
[lob] [char](10) NOT NULL,
[compname] [char](30) NOT NULL,
[compcode] [char](10) NOT NULL,
[adjust] [bit] NOT NULL,
[pcp_mm] [decimal](6, 0) NOT NULL,
[net] [decimal](8, 2) NOT NULL,
[excluded] [bit] NOT NULL,
[voided] [bit] NOT NULL,
[pd_mo] [char](2) NOT NULL,
[pd_yr] [char](4) NOT NULL,
[pd_yrmo] [char](6) NOT NULL,
[prodyrmo] [char](6) NOT NULL,
[ac_mo] [char](5) NOT NULL,
[ac_yr] [char](4) NOT NULL,
[ac_yrmo] [char](9) NOT NULL,
[chprefix] [decimal](6, 0) NOT NULL,
[check] [decimal](10, 0) NOT NULL,
[age] [decimal](6, 0) NOT NULL,
[age_grp] [char](10) NOT NULL,
[sex] [char](1) NOT NULL,
[category] [char](3) NOT NULL,
[char](23) NOT NULL,
[userid] [char](8) NOT NULL,
[input_dt] [char](10) NOT NULL,
[input_tim] [char](8) NOT NULL,
[pcpfromdt] [date] NULL,
[pcpthrudt] [date] NOT NULL
This is what the final view looks like:
INSERT INTO [SethTest].[dbo].[Join 5]
([MEMBID]
,[MEMB_KEYID]
,[MEMBNAME]
,[PROVID]
,[PROV_KEYID]
,[PROVNAME]
,[VENDOR]
,[VENDORNM]
,[HPCODE]
,[OPT]
,[HPNAME]
,[LOBCODE]
,[COMPANY_DESC]
,[COMPANY_ID]
,[ADJUST]
,[NETMM]
,[NETCAP]
,[EXCLUDED]
,[VOIDED]
,[PD_YR]
,[PD_MO]
,[PD_YRMO]
,[CAPMONTH]
,[CAPYEAR]
,[AC_YRMO]
,[CHPREFIX]
,[CHECK]
,[AGE]
,[AGE_GRP]
,[SEX]
,[CATEGORY]
,
,[USERID]
,[INPUT_DT]
,[INPUT_TIM]
,[PCPFROMDT]
,[PCPTHRUDT])
VALUES
(<MEMBID, varchar(25),>
,<MEMB_KEYID, uniqueidentifier,>
,<MEMBNAME, varchar,>
,<PROVID, varchar(25),>
,<PROV_KEYID, uniqueidentifier,>
,<PROVNAME, varchar,>
,<VENDOR, varchar(25),>
,<VENDORNM, varchar(40),>
,<HPCODE, varchar(10),>
,<OPT, varchar(25),>
,<HPNAME, varchar(40),>
,<LOBCODE, varchar(50),>
,<COMPANY_DESC, varchar(100),>
,<COMPANY_ID, varchar(10),>
,<ADJUST, varchar(1),>
,<NETMM, int,>
,<NETCAP, decimal(15,2),>
,<EXCLUDED, varchar(1),>
,<VOIDED, varchar(1),>
,<PD_YR, varchar(1),>
,<PD_MO, varchar(1),>
,<PD_YRMO, varchar(1),>
,<CAPMONTH, smallint,>
,<CAPYEAR, smallint,>
,<AC_YRMO, varchar,>
,<CHPREFIX, varchar(1),>
,<CHECK, varchar(1),>
,<AGE, decimal(6,0),>
,<AGE_GRP, varchar(7),>
,<SEX, varchar(3),>
,<CATEGORY, varchar(3),>
,<NOTE, varchar(1),>
,<USERID, varchar(1),>
,<INPUT_DT, varchar(10),>
,<INPUT_TIM, varchar(8),>
,<PCPFROMDT, datetime,>
,<PCPTHRUDT, datetime,>)
GO
I dont know if this helps at all. I hope it does.
May 10, 2013 at 9:57 am
Sean Lange (5/10/2013)
slunt01 (5/10/2013)
I wish I could show screen shots for further help but I am unable to because of the company policies. I will try and post more details about it.thank you.
Even a better explanation would help. There just isn't anything resembling a clear picture of what you are trying to do here.
I just posted a little bit more, I am not sure if it will help.
Basically I want to join or move the data from the view to the table. And in the format of the table being that it is the format of tables going years back.
May 10, 2013 at 10:37 am
here's a bit of information on rules of normalization: http://qa.sqlservercentral.com/blogs/abhijit_desai/2010/09/07/noramlization/ However, in this case, you're looking at data that seems to have a clear relationship, so a denormalized table is not what you're doing. (my project required combining data from 5 different business sections into a single table - there was no clear relationship between the original tables, so combining that data created a denormalized table.)
back to your project...what kind of query are you trying and what (if any) error message or result have you received? you will need to convert the data if the view datatype does not match the datatype in the existing table. also, you have a field in the existing table that does not exist in the view (prodyrmo). remember your query needs to list the same number of fields - if you are listing all 38 fields in the "INSERT INTO" statment, you also need to have 38 VALUES. also watch out for Null values in fields that do not allow Nulls.
i hope this helps!
Dana
"Drats! Foiled again!"May 10, 2013 at 10:51 am
danawexler (5/10/2013)
here's a bit of information on rules of normalization: http://qa.sqlservercentral.com/blogs/abhijit_desai/2010/09/07/noramlization/ However, in this case, you're looking at data that seems to have a clear relationship, so a denormalized table is not what you're doing. (my project required combining data from 5 different business sections into a single table - there was no clear relationship between the original tables, so combining that data created a denormalized table.)back to your project...what kind of query are you trying and what (if any) error message or result have you received? you will need to convert the data if the view datatype does not match the datatype in the existing table. also, you have a field in the existing table that does not exist in the view (prodyrmo). remember your query needs to list the same number of fields - if you are listing all 38 fields in the "INSERT INTO" statment, you also need to have 38 VALUES. also watch out for Null values in fields that do not allow Nulls.
i hope this helps!
That does help! Thank you!
I am actually trying to create a stored procedure to run this so that we can use Access as a front end (GUI) type of thing.
I will try converting the data in the view and then see if it will append using the stored procedure.
But you said that if I have the 38 fields that I NEED to have data in every field? I am guessing that if I have in ' ' (blanks) that counts as data...
May 10, 2013 at 10:55 am
yes, you can use ' ' or n/a or anything else like that.
Dana
"Drats! Foiled again!"Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply