Query Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Issue

Author

Topic

SP273
Starting Member

14 Posts

Posted - 2011-02-25 : 10:03:58

Hello All,

I am trying to write a query where in the child column in table A have a project name that is 16 characters. What i want to do is to populate a new table B in a way where in i just have first 5 letters from the child column of project name from table A. Which command can i use for this?

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2011-02-25 : 13:35:42

[code]

CREATE TABLE #TableA (ProjectName varchar(16))
GO

INSERT INTO #TableA (ProjectName)
SELECT 'Project 12345678' UNION ALL
SELECT 'Long Trail Brew ' UNION ALL
SELECT 'Anchor Steam XXX' UNION ALL
SELECT 'Loose Cannon BX '
GO

SELECT * FROM #TableA
GO

SELECT SUBSTRING(ProjectName,1,5) AS ProjectName INTO #TableB FROM #TableA
GO

SELECT * FROM #TableB
GO

DROP TABLE #TableA, #TableB
GO
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

SP273
Starting Member

14 Posts

Posted - 2011-02-25 : 14:55:11

Hi,

Thanks for your reply.

Below is the sample code to my query

INSERT INTO dbo.HS_Projects_Hierarchy_New
(
[LoadID]
,[Parent]
,[Child]
,[DataStore]
,[MemberValid1]
,[Aggregation]
,[Alias]
)

( select [LoadID]
,[Parent]
,[Child]
,[DataStore]
,[MemberValid1]
,[Aggregation]
,[Alias]

from dbo.HS_Project_Hierarchy_OLD where child in
(
Select distinct project from
(
select distinct Project from dbo.HS_Hours_I
union all
select distinct Project from dbo.HS_Hours_II
) a
)
)

Now whats the best way to modify the query so that in table HS_Projects_Hierarchy_New I just load the 5 digits project code as child & all the remaining column are populated from the table HS_Projects_Hierarchy_Old as they are being populated now.

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2011-02-25 : 15:31:46

what column do you want to truncate?

Just us the substring like in my example

did you run it?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

SP273
Starting Member

14 Posts

Posted - 2011-02-25 : 16:12:07

Hi,

Thanks for your reply. I ran your code & modified my code as follows:

INSERT INTO dbo.HS_Projects_Hierarchy
(
[LoadID]
,[Parent]
,[Child]
,[DataStore]
,[MemberValid1]
,[Aggregation]
,[Alias]
)

( select [LoadID]
,[Parent]
,[Child]
,[DataStore]
,[MemberValid1]
,[Aggregation]
,[Alias]

from dbo.HS_Project_Hierarchy where child in
(
Select distinct substring (project,1,6) from
(
select distinct Project from dbo.HS_Hours_I
union all
select distinct Project from dbo.HS_Hours_II
) a
)
)

Is there a way where in can create put values in the new table without creating one more staging table as you did in your example.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources