Hi all,



Can anyone help me with an SQL statement?



I want to use an already existing table to populate a second table.



The original table is tbl_company and contains the fields,

intCompanyID, strCompany, strAddress, and strRefferals



I want to populate table tbl_stats with four fields:



intID (primary key), intCompanyID, strRefferals and dStatsMonth (where 

companyID and strReferrals are taken from the original table) and 

dStatsMonth is the current month.



Each month i want to append the data from tbl_company into tbl_stats.



thanks,



Nick

You want something like:

INSERT INTO tbl_stats (intCompanyId, strRefferals) 

SELECT intCompanyId, strRefferals

FROM tbl_company

WHERE ... something so you don't insert the same things again next month



Where will you get a value for the primary key?



> Hi all,

> 

> Can anyone help me with an SQL statement?

> 

> I want to use an already existing table to populate a second table.

> 

> The original table is tbl_company and contains the fields,

> intCompanyID, strCompany, strAddress, and strRefferals

> 

> I want to populate table tbl_stats with four fields:

> 

> intID (primary key), intCompanyID, strRefferals and dStatsMonth (where 

> companyID and strReferrals are taken from the original table) and 

> dStatsMonth is the current month.

> 

> Each month i want to append the data from tbl_company into tbl_stats.

> 

> thanks,

> 

> Nick

thanks phil, still a small query though,



My primary key is an identity field (like autonumber in Access) which 

automatically increments by 1.



How do i put the current date into the dDateAdded field? Can this be done? 

It was not mentioned in the SQL statement,



INSERT INTO tbl_stats (intCompanyId, intRefferals) 

SELECT intCompanyId, intRefferals

FROM tbl_company



I do not think i will need the WHERE clause as I want to add the same 

companies to the table each month so i can compare a companies number of 

referrals over a period of months.



thanks again,



Nick

Which database are you using?  If SQL Server I think you can set the field 

to have a default value of the current month - I think its MONTH(GETDATE())



> thanks phil, still a small query though,

> 

> My primary key is an identity field (like autonumber in Access) which 

> automatically increments by 1.

> 

> How do i put the current date into the dDateAdded field? Can this be 

done? 

> It was not mentioned in the SQL statement,

> 

> INSERT INTO tbl_stats (intCompanyId, intRefferals) 

> SELECT intCompanyId, intRefferals

> FROM tbl_company

> 

> I do not think i will need the WHERE clause as I want to add the same 

> companies to the table each month so i can compare a companies number of 

> referrals over a period of months.

> 

> thanks again,

> 

> Nick

I am using SQL Server 7 but don't understand how to mix the MONTH(GETDATE

()) statement into the SQL statement as how does it mix data from the 

original table with the 'new' date data.



thanks,



Nick

Ok, I was suggesting you put a default value on the field in the table's 

underlying structure, but I guess you could mix it with the SQL like this:

INSERT INTO tbl_stats (intCompanyId, strRefferals, dStatsMonth) 

SELECT intCompanyId, strRefferals, MONTH(GETDATE())

FROM tbl_company



Alternatively, since you're using SQL Server you can execute a stored 

procedure to return the values for inserting, e.g

INSERT INTO tbl_stats (intCompanyId, strRefferals, dStatsMonth) 

EXEC sproc

...where sproc is just the SELECT statement shown above.



One of these should work for you.

Phil

> I am using SQL Server 7 but don't understand how to mix the MONTH(GETDATE

> ()) statement into the SQL statement as how does it mix data from the 

> original table with the 'new' date data.

> 

> thanks,

> 

> Nick