October 2, 2017 at 6:29 am
Taking AdventureWorks as example
1. Create New Table
CREATE TABLE Insert1
(
ContactID INT,
FirstName NVARCHAR(100),
MiddleName NVARCHAR(100),
LastName NVARCHAR(100),
FullName NVARCHAR(100)
)
2. Inserting records to this table
INSERT INTO Insert1
SELECT
ContactID,
FirstName,
MiddleName,
LastName,
CASE WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName
ELSE FirstName + ' ' + MiddleName + ' ' + LastName END AS FullName
FROM AdventureWorks.Person.Contact
3. Add two new columns
ALTER TABLE Insert1
ADD TotalDue MONEY
ALTER TABLE Insert1
ADD OrderDate DATETIME
4. these new columns come from adventureworks.sales.Salesorderheader table
The relationship between Person.Contact table and sales.Salesorderheader table is 1 to many
after adding the table structure looks like below
ContactID FirstName MiddleName LastName FullName TotalDue OrderDate
1 Gustavo NULL Achong Gustavo Achong NULL NULL
1 Gustavo NULL Achong Gustavo Achong NULL NULL
1 Gustavo NULL Achong Gustavo Achong NULL NULL
1 Gustavo NULL Achong Gustavo Achong NULL NULL
1 Gustavo NULL Achong Gustavo Achong NULL NULL
1 Gustavo NULL Achong Gustavo Achong NULL NULL
1 Gustavo NULL Achong Gustavo Achong NULL NULL
2 Catherine R. Abel Catherine R. Abel NULL NULL
2 Catherine R. Abel Catherine R. Abel NULL NULL
2 Catherine R. Abel Catherine R. Abel NULL NULL
2 Catherine R. Abel Catherine R. Abel NULL NULL
Expected OutPut
ContactID FirstName MiddleName LastName FullName OrderDate TotalDue
1 Gustavo NULL Achong Gustavo Achong 2001-09-01 00:00:00.000 5370.284
1 Gustavo NULL Achong Gustavo Achong 2002-03-01 00:00:00.000 5410.064
1 Gustavo NULL Achong Gustavo Achong 2002-06-01 00:00:00.000 3516.5416
1 Gustavo NULL Achong Gustavo Achong 2002-09-01 00:00:00.000 36330.7417
1 Gustavo NULL Achong Gustavo Achong 2002-12-01 00:00:00.000 43214.9511
1 Gustavo NULL Achong Gustavo Achong 2003-03-01 00:00:00.000 32078.747
1 Gustavo NULL Achong Gustavo Achong 2003-06-01 00:00:00.000 49846.693
2 Catherine R. Abel Catherine R. Abel 2003-09-01 00:00:00.000 42123.1691
2 Catherine R. Abel Catherine R. Abel 2003-12-01 00:00:00.000 37324.429
2 Catherine R. Abel Catherine R. Abel 2004-03-01 00:00:00.000 29091.4559
2 Catherine R. Abel Catherine R. Abel 2004-06-01 00:00:00.000 43962.7901
October 2, 2017 at 7:46 am
I might be missing something, but my copy of Adventure works doesn't have a table Person.Contact, thus I can't run your scripts.
If, however, the relationship is many to one, how are you determining what value is being inserted into Order Date and Total Due? What happens if you add an order to Sales.SalesOrderHeader, are you ok with the data becoming stale in your insert1 table?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 2, 2017 at 8:37 am
You really should not insert the name values into your new table. That rather defeats the point of relational data. You should instead insert only the ContactID. If the name changes in the User table your data will be out of date. However, sometimes in a sales type of environment you really do want to denormalize and store the name at the time of the sale. But this does not seem to be what you are attempting to do here.
In the example you posted I would not make this a new table. Instead this is simply a query that joins those two tables together and storing that data is not the best approach. A view is probably the best way to handle what you are demonstrating 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/
October 2, 2017 at 9:09 am
First, you haven't really asked a question.
Second, when you add a column, you need to decide where the data comes from. A column is like adding a new column to a table in Word. There's no data until you decide what data goes in there. If you're asking how to insert this data, you need to provide answers to the questions about about what data you think goes in there, meaning the rules.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply