Copy columns from 2 tables and insert into another table 1

[Bluejay07]

Hello,

I am not strong in creating complex queries and I am looking for assistance in creating a query that will insert values into a table based on the values of two other tables. All tables are in the same database. I'm also using SQL Server 2005.

Table #1

SELECT projectid
FROM projects
WHERE simid > ''

'simid may contain nulls or 0

Table #2

SELECT GICID, BudgetCycleYear
FROM GICProjectData

These two tables are linked by simid and GICID.
The values are to be inserted into a new table structured as follows:

ProjectID (from Table#1)
GICID     (from Table#2)
BudgetCycle (from Table#2)

Any help would be greatly appreciated.
Thank you in advance.




If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

Not sure if this will help but the joined query is (schema name omitted):

SELECT projects.projectid, GICProjectData.GICID, GICProjectData.BudgetCycleYear
FROM projects INNER JOIN
GICProjectData ON projects.simid = GICProjectData.GICID
WHERE projects.simid > ''

If at first you don't succeed, then sky diving wasn't meant for you!

 

[gmmastros]

If your table already exists.....

[!]Insert Into YourNewTableName(ProjectId, GICID, BudgetCycleYear)[/!]
SELECT projects.projectid, GICProjectData.GICID, GICProjectData.BudgetCycleYear
FROM projects INNER JOIN
GICProjectData ON projects.simid = GICProjectData.GICID
WHERE projects.simid > ''

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

Woo Hoo.
George to the rescue again.

Thank you very much. Your query worked perfectly.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[gmmastros]

You did all the heavy lifting here, not me.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

I'm now looking for a bit more help.
I need to create a list of entries that were not matched so I can figure out why.

Table #1 has 550 entries
The insert query only matched 170 entries.
I now need to determine why 380 entries were not matched.

I thought I would need a query similar to:

SELECT projects.projectid, projects.simid
FROM projects LEFT OUTER JOIN
GICProjectData ON projects.simid = GICProjectData.GICID
WHERE (projects.simid > '') AND (projects.simid <> GICProjectData.GICID)

However, the query is returning 0 rows.
In other words, I am looking for all rows in projects where there is a valid simid and the simid cannot be matched to the GICID.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[gmmastros]

SELECT projects.projectid, projects.simid
FROM   projects 
       LEFT OUTER JOIN GICProjectData 
         ON projects.simid = GICProjectData.GICID
WHERE (projects.simid > '') 
      [!]AND GICProjectData.GICID is NULL[/!]

You cannot use the <> test because unmatched rows will have GICID of NULL, and you cannot test NULL with equal and/or not equal. It must be "is NULL".

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bluejay07]

Thank you very much again gmmastros.
Thanks as well for the explanation.

That modification returned exactly 380 rows.

If at first you don't succeed, then sky diving wasn't meant for you!