November 12, 2016 at 5:53 pm
Hello guys,
This would be me example. I have some query and my final table looks like:
|Country|Region|Mark|RW|December 26, 2015|January 2, 2016|January 9, 2016|... etc.
Also I have and second table where I need to copy the data from first table and this one looks like:
|Country|Region|Mark|RW|January 3, 2015|January 10, 2015|January 17, 2015|....|December 26, 2015|January 2, 2016|January 9, 2016|...
and I have to copy, exactly, December 26, 2015 (from first) to December 26, 2015 (in second) and so on and so forth.
I have tried with INSERT INTO, INTO...but every time got some error (the tables are not the same or something like that)
Any idea how to make this?
Thank you in advance.
November 12, 2016 at 6:13 pm
tocy1980 (11/12/2016)
Hello guys,This would be me example. I have some query and my final table looks like:
|Country|Region|Mark|RW|December 26, 2015|January 2, 2016|January 9, 2016|... etc.
Also I have and second table where I need to copy the data from first table and this one looks like:
|Country|Region|Mark|RW|January 3, 2015|January 10, 2015|January 17, 2015|....|December 26, 2015|January 2, 2016|January 9, 2016|...
and I have to copy, exactly, December 26, 2015 (from first) to December 26, 2015 (in second) and so on and so forth.
I have tried with INSERT INTO, INTO...but every time got some error (the tables are not the same or something like that)
Any idea how to make this?
Thank you in advance.
INSERT can only have one INTO. Post the query, the table definitions please, and the exact error you're getting.... "or something like that". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2016 at 5:18 am
delete from na_wf
;
insert into na_wf
SELECT [Country]
,[Region]
,[Br]
,[Tr]
,[Div]
,[Type]
,[RW]
,[December 26, 2015]
,[January 2, 2016]
,[January 9, 2016]
,[January 16, 2016]
,[January 23, 2016]
,[January 30, 2016]
,[February 6, 2016]
,[February 13, 2016]
,[February 20, 2016]
,[February 27, 2016]
FROM [na_wf]...['Final WOs$']
GO
So, this is my first query and I pull out the data from linked server (excel file).
Now, how can I copy this data to this table (I have used "Script table as: Create to - just to see the Design of the table):
CREATE TABLE [dbo].[namer_wf_test](
[Country] [nvarchar](255) NULL,
[Region] [nvarchar](255) NULL,
[Br] [nvarchar](255) NULL,
[Tr] [nvarchar](255) NULL,
[Div] [nvarchar](255) NULL,
[Type] [nvarchar](255) NULL,
[RW] [float] NULL,
[January 3, 2015] [float] NULL,
[January 10, 2015] [float] NULL,
[January 17, 2015] [float] NULL,
[January 24, 2015] [float] NULL,
[January 31, 2015] [float] NULL,
[February 7, 2015] [float] NULL,
[February 14, 2015] [float] NULL,
[February 21, 2015] [float] NULL,
[February 28, 2015] [float] NULL,...
,[December 26, 2015] [float] NULL,...
,[January 2, 2016] [float] NULL,...
,[January 9, 2016] [float] NULL,...
,[January 16, 2016] [float] NULL,...
,[January 23, 2016] [float] NULL,...
,[January 30, 2016] [float] NULL,...
,[February 6, 2016] [float] NULL,...
,[February 13, 2016] [float] NULL,...
,[February 20, 2016] [float] NULL,...
,[February 27, 2016]
The difference between these 2 tables is that the second starts from first week in 2015 and the first starts from first week in 2016.
I have deleted the query for this copy/insert data so I am asking you for the help 🙂
Thanks for helping me!
November 13, 2016 at 9:37 am
To be honest, this is a train wreck that happens every time that you bring new data in because you have to add columns to the [dbo].[namer_wf_test] table every time there's an import. That also causes some massive page splits of any clustered index you may have and makes querying temporal time frames a nightmare even when no new columns are added. There's a way around all that and it all could be made to be totally automatic and self-healing but that would also require re-engineering of the whole process and the underlying table not to mention the minds of the people supporting the process.
Basically, you're storing the data in a pivoted presentation format and that almost never works out well. It requires you to add columns to a table, is resistant to proper indexing, is resistant to different aggregations and grouped aggregates (think monthly totals and line totals for given temporal periods), and is resistant to automation (you'll need to change code every time you do an import) just to name a few of the problems that you're going to run into now and in the future.
Starting at the first table, instead of doing a DELETE to ensure the table is empty, do a TRUNCATE instead. It'll be faster and much less resource intensive.
For the inserts, you must first identify something that uniquely identifies the rows in both tables. Since you didn't post what the PK was for the [dbo].[namer_wf_test] table, I'll assume that all of the non-temporal columns make up the PK. In other words, I'll assume that the following columns uniquely identify each row in both tables.
Country,
Region,
Br,
Tr,
Div,
[Type],
RW
Now, I suspect that you don't actually want to do an INSERT (creates new rows) in the [dbo].[namer_wf_test] table if a row already exists according to the PK list of columns above (which I'll refer to as only the "PK columns" from here on). Instead, I suspect you want to do an UPDATE to populate existing rows with values for the new date columns. To do that using the examples you've posted, you'll need to write an UPDATE similar to the following UNTESTED code.
UPDATE tgt
SET [December 26, 2015] = src.[December 26, 2015]
,[January 2, 2016] = src.[January 2, 2016]
,[January 9, 2016] = src.[January 9, 2016]
,[January 16, 2016] = src.[January 16, 2016]
,[January 23, 2016] = src.[January 23, 2016]
,[January 30, 2016] = src.[January 30, 2016]
,[February 6, 2016] = src.[February 6, 2016]
,[February 13, 2016] = src.[February 13, 2016]
,[February 20, 2016] = src.[February 20, 2016]
,[February 27, 2016] = src.[February 27, 2016]
FROM dbo.namer_wf_test tgt
JOIN dbo.na_wf src
ON src.Country = tgt.Country
AND src.Region = tgt.Region
AND src.Br = tgt.Br
AND src.Tr = tgt.Tr
AND src.Div = tgt.Div
AND src.[Type] = tgt.[Type]
AND src.RW = tgt.RW
;
That will take care of any rows with existing PK columns but it doesn't take care of the possibility of any new unique PK column possibilities, which actually do need to be inserted. The following UNTESTED code should take care of that eventuality.
INSERT INTO dbo.namer_wf_test
(
[Country]
,[Region]
,[Br]
,[Tr]
,[Div]
,[Type]
,[RW]
,[December 26, 2015]
,[January 2, 2016]
,[January 9, 2016]
,[January 16, 2016]
,[January 23, 2016]
,[January 30, 2016]
,[February 6, 2016]
,[February 13, 2016]
,[February 20, 2016]
,[February 27, 2016]
)
SELECT [Country]
,[Region]
,[Br]
,[Tr]
,[Div]
,[Type]
,[RW]
,[December 26, 2015]
,[January 2, 2016]
,[January 9, 2016]
,[January 16, 2016]
,[January 23, 2016]
,[January 30, 2016]
,[February 6, 2016]
,[February 13, 2016]
,[February 20, 2016]
,[February 27, 2016]
FROM dbo.na_wf src
WHERE NOT EXISTS
(
SELECT *
FROM dbo.namer_wf_test tgt
WHERE src.Country = tgt.Country
AND src.Region = tgt.Region
AND src.Br = tgt.Br
AND src.Tr = tgt.Tr
AND src.Div = tgt.Div
AND src.[Type] = tgt.[Type]
AND src.RW = tgt.RW
)
;
The combination of the UPDATE and INSERT above is what is frequently referred to as an "UPSERT" or, more commonly, a "MERGE". Yes, there is actually a MERGE statement that you could use but, because of problems with the statement in the past, I don't use it at all because I don't trust it.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2016 at 9:59 am
Again, just to emphasize, this whole design of the originally stated problem, I wouldn't create either table in the current "horizontal" format. I'd make them both vertical and I'd use dynamic CROSSTAB code for any queries. That's the only way that we could actually take advantage of temporal indexing and the only easy way to allow for flexibility in future reporting requirements. Considering the number of PK columns and their width, I might also separate the PK columns out into their own table and assign a surrogate key to each unique combination in both tables.
It's all a little more work up front with great rewards able to be realized now and in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2016 at 12:28 pm
Thank you Jeff.
Really I am still the newbie in SQL so this is still hard for me...
At this moment I am just trying to find for me familiar solution and I need more time for SQL 🙂
I'll try this solution and let you know my final result.
Cheers! 🙂
November 17, 2016 at 9:52 pm
If you have any say in the structure of the database, stay away from a design where you have dates/information for column names. Been there, done that. It was an incredible nightmare to use, fix, query... Just an all-around disaster. The mess I dealt with was in Access, so I could cheat and create dynamic SQL to fix it, but save yourself the hassle and don't make the mess to begin with.
Take Jeff's advice! If you have questions on how to fix what you have, please ask. But a design like you posted is going to be really brittle and difficult to query, I promise. If the design of the databases I worked on were correct, I could have done all the the reporting in less than a day, and not 50 hours a week for 6 weeks.
November 17, 2016 at 11:43 pm
tocy1980 (11/13/2016)
Thank you Jeff.Really I am still the newbie in SQL so this is still hard for me...
At this moment I am just trying to find for me familiar solution and I need more time for SQL 🙂
I'll try this solution and let you know my final result.
Cheers! 🙂
That's why I'm telling you that you're making a seriously grave mistake in the design... so that you don't stay a "newbie" forever. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply