More SQL Questions..

Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
32,826
Reaction score
3,033
Location
On the toilet
Is it possible to copy over the data from a View into another Table without the relationships?
 
Short answer: Yes.

Long answer: Probably

Longest answer: Might involve a trick or 2.

A view is just a table, call it a dynamic table if you want.

So the question is: Is it possible to copy over the data from one table into another table (without the relationships) ...

And this then is pretty easy:

insert into TargetTable(field1, field2, field3)
select field1, field2, field3 ... fieldN from SourceView
 
xrapidx said:
Or
SELECT * INTO NewTable FROM View
Click to expand...

Only works if your view and table is in the same format and fields are in the same order, etc.

Safer to manually select the fields you are interested in -- don't mislead the noobs X :-)
 
bin3 said:
Only works if your view and table is in the same format and fields are in the same order, etc.

Safer to manually select the fields you are interested in -- don't mislead the noobs X :-)
Click to expand...

Only works if the table doesn't exist.

The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server.
Click to expand...
 
Pho3nix said:
Is it possible to copy over the data from a View into another Table without the relationships?
Click to expand...

If you mean the table you are copying to has physical dependencies(i.e. foreign keys) linked to other tables then it will work as long as those dependencies exist in the result from the view, otherwise you'll get a dependency error trying to copy it over. If you really need to copy the data over then remove the dependency, but that will just lead to dirty data in your table.
 
Well I kinda figured it out but will attend to it using code tomorrow. I used the SSIS tool in SQL Biz Management to copy data over from the view. "Dirty" data yes, but I needed a solution that wouldn't alter the source data if anything went wrong. Thanks for your thoughts guys, will give it a try and report back.
 
Ok, that worked, thanks guys :) . Another question though (I'm a SQL Noob). I need a query that will get me the Current month from the System(?), then select only records in the table which are in that month. Hope you guys get me.

So far i have :
Code: 
 SELECT * from Test_Table WHERE MONTH(Date_Worked)=MONTH(GETDATE())
but it gets stuck during debugging.. wrong syntax?
 
Pho3nix said:
Ok, that worked, thanks guys :) . Another question though (I'm a SQL Noob). I need a query that will get me the Current month from the System(?), then select only records in the table which are in that month. Hope you guys get me.

So far i have :
Code: 
 SELECT * from Test_Table WHERE MONTH(Date_Worked)=MONTH(GETDATE())
but it gets stuck during debugging.. wrong syntax?
Click to expand...


Shouldn't it be :

Code: 
 SELECT * from Test_Table WHERE MONTH(Date_Worked)=GETDATE()

Comparing the MONTH field to the current date... Not sure what you wanted to do with MONTH(GETDATE()...
 
No... because that'll only get the current day, not the month.

Code: 
SELECT * from Test_Table WHERE MONTH(Date_Worked)=MONTH(GETDATE())

Wont work because a month can be in each year...

Code: 
SELECT * from Test_Table WHERE MONTH(Date_Worked)=MONTH(GETDATE()) 
AND YEAR(Date_Worked)=YEAR(GETDATE())
 
xrapidx said:
No... because that'll only get the current day, not the month.

Code: 
SELECT * from Test_Table WHERE MONTH(Date_Worked)=MONTH(GETDATE())

Wont work because a month can be in each year...

Code: 
SELECT * from Test_Table WHERE MONTH(Date_Worked)=MONTH(GETDATE()) 
AND YEAR(Date_Worked)=YEAR(GETDATE())
Click to expand...


Ahhh right, sorry thought Month was the field name, my bad.
 
xrapidx said:
No... because that'll only get the current day, not the month.

Code: 
SELECT * from Test_Table WHERE MONTH(Date_Worked)=MONTH(GETDATE())

Wont work because a month can be in each year...

Code: 
SELECT * from Test_Table WHERE MONTH(Date_Worked)=MONTH(GETDATE()) 
AND YEAR(Date_Worked)=YEAR(GETDATE())
Click to expand...

Thanks :) forgot that I might need to make sure that the items selected are from this year. I'll be back with more questions ;)
 
Is it possible to run more than one SQL Query ie. First Query (Global?) selects all the data from the month, the other queries set how many rows should be selected because the eg.price of an item is less than 20 and I want to select 1% of the items that fit this description. Thoughts? Hope I made sense.
 
Pho3nix said:
Is it possible to run more than one SQL Query ie. First Query (Global?) selects all the data from the month, the other queries set how many rows should be selected because the eg.price of an item is less than 20 and I want to select 1% of the items that fit this description. Thoughts? Hope I made sense.
Click to expand...

Looks like a nested query...

Think of the Global query results as a table, you can then do a select from that as if it were a normal table.

For example

SELECT * FROM (SELECT * from Test_Table WHERE MONTH(Date_Worked)=MONTH(GETDATE())) AS TBL1 WHERE TBL1.PRICE < 20

Although you could just use an "AND" in this example, but its an example so sue me :P

Does that make sense, although i didn't read the whole thread.
 
Yes a sub-query or nested (not sure about the difference), after the rows from this month are selected, I would like to set the number of rows to be selected to be equal to a value a get from a "count()" and this value might need to be rounded of to a to one significant number or whole number ie.40.5 = 41 etc.
 
Way I would do it

SELECT top 40 percent * FROM Test_Table WHERE MONTH(Date_Worked)=MONTH(GETDATE())) and PRICE < 20

Not sure if this would work in anything other than ms sql
 
Edit: sorry, was on the wrong page of posts.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X