[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Insert into a table using a select and values?

Ally

3/20/2007 11:24:00 AM

Hi,

I am trying to convert some SAS into SQL and I am struggling with the
following

What I need to do is for each distinct reference ID populate the table
with this ID and then in the other column add 0,1,2,3,4. The table
should look like this.

ID Decision_Week
12345 0
12345 1
12345 2
12345 3
12345 4

Every distinct ID should have decision_weeks 0-4.

I thought I might be able to do this by creating a table: -

CREATE TABLE [dbo].[TD_Pipeline_Data_Test_3](
[ID] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[dec1] [int]
) ON [PRIMARY]

but I am struggling with how to insert from a select and using values
0-4, don't know if I could create an array to do it or not? All the
other ID's are in a table called dbo.TD_Pipeline_Data_Test_2.

Hope this makes sense as I am a bit of a newby at T-SQL.

Thanks in advance for your help,

Alistair
4 Answers

TheSQLGuru

3/20/2007 1:38:00 PM

0

One bruteforce and very simple method is to simply do 5 select statements:

insert dbo.td_pipeline_data_test_3
select id, 0 from dbo.TD_Pipeline_Data_Test_2

insert dbo.td_pipeline_data_test_3
select id, 1 from dbo.TD_Pipeline_Data_Test_2
.
.
.
insert dbo.td_pipeline_data_test_3
select id, 5 from dbo.TD_Pipeline_Data_Test_2

You could also tie all of those selects into one UNION ALL statement to have
just one insert. Oh, and if you really do need the DISTINCT for the ID
column, add that in. Do not use it unless you need it since it takes extra
overhead.

Note that TSQL does not have 'arrays' you can use. You could do them with
CLR if you are using SQL2005.

--
TheSQLGuru
President
Indicium Resources, Inc.

"Ally" <alistair.robertson@abbey.com> wrote in message
news:1174389838.255524.118470@e1g2000hsg.googlegroups.com...
> Hi,
>
> I am trying to convert some SAS into SQL and I am struggling with the
> following
>
> What I need to do is for each distinct reference ID populate the table
> with this ID and then in the other column add 0,1,2,3,4. The table
> should look like this.
>
> ID Decision_Week
> 12345 0
> 12345 1
> 12345 2
> 12345 3
> 12345 4
>
> Every distinct ID should have decision_weeks 0-4.
>
> I thought I might be able to do this by creating a table: -
>
> CREATE TABLE [dbo].[TD_Pipeline_Data_Test_3](
> [ID] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [dec1] [int]
> ) ON [PRIMARY]
>
> but I am struggling with how to insert from a select and using values
> 0-4, don't know if I could create an array to do it or not? All the
> other ID's are in a table called dbo.TD_Pipeline_Data_Test_2.
>
> Hope this makes sense as I am a bit of a newby at T-SQL.
>
> Thanks in advance for your help,
>
> Alistair
>


Ally

3/20/2007 2:08:00 PM

0

On 20 Mar, 13:37, "Kevin G. Boles" <kgbo...@earthlink.net> wrote:
> One bruteforce and very simple method is to simply do 5 select statements:
>
> insert dbo.td_pipeline_data_test_3
> select id, 0 from dbo.TD_Pipeline_Data_Test_2
>
> insert dbo.td_pipeline_data_test_3
> select id, 1 from dbo.TD_Pipeline_Data_Test_2
> .
> .
> .
> insert dbo.td_pipeline_data_test_3
> select id, 5 from dbo.TD_Pipeline_Data_Test_2
>
> You could also tie all of those selects into one UNION ALL statement to have
> just one insert. Oh, and if you really do need the DISTINCT for the ID
> column, add that in. Do not use it unless you need it since it takes extra
> overhead.
>
> Note that TSQL does not have 'arrays' you can use. You could do them with
> CLR if you are using SQL2005.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
> "Ally" <alistair.robert...@abbey.com> wrote in message
>
> news:1174389838.255524.118470@e1g2000hsg.googlegroups.com...
>
>
>
> > Hi,
>
> > I am trying to convert some SAS into SQL and I am struggling with the
> > following
>
> > What I need to do is for each distinct reference ID populate the table
> > with this ID and then in the other column add 0,1,2,3,4. The table
> > should look like this.
>
> > ID Decision_Week
> > 12345 0
> > 12345 1
> > 12345 2
> > 12345 3
> > 12345 4
>
> > Every distinct ID should have decision_weeks 0-4.
>
> > I thought I might be able to do this by creating a table: -
>
> > CREATE TABLE [dbo].[TD_Pipeline_Data_Test_3](
> > [ID] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> > [dec1] [int]
> > ) ON [PRIMARY]
>
> > but I am struggling with how to insert from a select and using values
> > 0-4, don't know if I could create an array to do it or not? All the
> > other ID's are in a table called dbo.TD_Pipeline_Data_Test_2.
>
> > Hope this makes sense as I am a bit of a newby at T-SQL.
>
> > Thanks in advance for your help,
>
> > Alistair- Hide quoted text -
>
> - Show quoted text -

Thanks Kevin,

You've solved it for me - I am using 2005 but maybe I'll leave CLR
until I've mastered the basics.

Cheers, Alistair

Hugo Kornelis

3/20/2007 6:42:00 PM

0

On 20 Mar 2007 04:23:58 -0700, Ally wrote:

>What I need to do is for each distinct reference ID populate the table
>with this ID and then in the other column add 0,1,2,3,4. The table
>should look like this.
>
>ID Decision_Week
>12345 0
>12345 1
>12345 2
>12345 3
>12345 4
>
>Every distinct ID should have decision_weeks 0-4.

Hi Ally,

Here's a different way to do it

INSERT INTO NewTable (ID, Decision_Week)
SELECT DISTINCT a.ID, n.Number
FROM SourceTable AS a
CROSS JOIN (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2
UNION ALL SELECT 3 UNION ALL SELECT 4) AS n(Number);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Scout

3/3/2014 10:38:00 PM

0



"Tom McDonald" <kiltmac@gmail.com> wrote in message
news:lf2r3v$9rn$2@dont-email.me...
> On 3/3/2014 3:01 PM, Just Wondering wrote:
>> On 3/3/2014 12:02 PM, Dakota wrote:
>>> On 3/3/2014 12:37 PM, RD Sandman wrote:
>>>> Just Wondering <fmhlaw@comcast.net> wrote in
>>>> news:5314c8d7$0$59051$862e30e2@ngroups.net:
>>>>
>>>>> On 3/3/2014 4:49 AM, Dakota wrote:
>>>>>> On 3/2/2014 10:25 PM, Scout wrote:
>>>>>>>
>>>>>>>
>>>>>>> "Michael Ejercito" <mejercit@hotmail.com> wrote in message
>>>>>>> news:lf0u99$5ll$1@dont-email.me...
>>>>>>>> On Sunday, March 2, 2014 7:29:22 PM UTC-8, John Manning wrote:
>>>>>>>>> On 3/2/2014 9:31 PM, hlil...@juno.com
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>> On Sunday, March 2, 2014 2:43:32 PM UTC-8, spencer wrote:
>>>>>>>>>
>>>>>>>>> Liberal and minority groups can't get Voter ID cards?
>>>>>>>>>
>>>>>>>>> But it is harder to get those if you are not a citizen. And
>>>>>>>>> it
>>>>>>>>> is hard to get two of them so you can vote twice (or three or four
>>>>>>>>> or ten).
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> The GOP's Voter ID laws intentionally and maliciously make
>>>>>>>>> it harder for
>>>>>>>>>
>>>>>>>>> poor and minority voters - who usually vote Democratic - to
>>>>>>>>> vote.
>>>>>>>>>
>>>>>>>> They make it equally hard for everyone to vote. There is no
>>>>>>>> reason
>>>>>>>> why a minority voter would have a harder time getting ID than a
>>>>>>>> majority voter.
>>>>>>>
>>>>>> There are many reasons.
>>>>>>>
>>>>>>>> In any event, federal already requires photo ID to purchase a
>>>>>>>> firearm. Is the federal government making it harder for poor and
>>>>>>>> minority people to purchase firearms?
>>>>>>>
>>>>>>> Heck the federal government requires Photo ID to obtain the aid
>>>>>>> provided for the poor.
>>>>>>>
>>>>>>> Is the federal government attempting to keep from aiding the poor?
>>>>>>>
>>>>>> The stupidity of you right-wingers is palpable.
>>>>>>
>>>>>> The federal government does not require photo IDs from voters in the
>>>>>> various states. Photo ID laws have been enacted only in some
>>>>>> individual US states.
>>>>>>
>>>>>> No one has a constitutional right to aid for the poor. Every citizen
>>>>>> over the age of 18 has a constitutional right to vote unless
>>>>>> incarcerated in some states.
>>>>>>
>>>>> And non-citizens have NO right to vote, and every vote by one not
>>>>> entitled to vote diminishes the right of those who are entitled. This
>>>>> makes it eminently sensible to impose a minimum requirement on
>>>>> prospective voters to prove they are citizens before giving them
>>>>> access to a ballot.
>>>>>
>>>>>
>>>>
>>>> Works for me.
>>>>
>>> Voter fraud is so rare that it's absurd to claim it's the reason for
>>> photo ID laws.
>>>
>> One illegal vote is one too many.
>
> One voter denied the franchise is one too many.

Agreed, so let's insure that voter can obtain the necessary ID by making it
free to obtain and working with any legitimate voter to make sure they can
obtain it in a reasonable manner.

Oh, shit, we do that.

Meanwhile, how may voters are denied the franchise simply because they
failed to register to vote?

Yet we don't hear any objection from you on that issue.

>The trade-off is clearly on the side of clearing barriers to voting, not
>creating more.

I agree that clearing the barriers to LEGITIMATE voters is necessary, but
creating barriers for ILLEGITIMATE voters is just as necessary.

Further, let's look at this for an example. You say we should make sure that
people can vote, however if their vote is canceled out by a fraudulent
vote....what have you accomplished? The net result is just the same as if
they couldn't vote.

We need to make sure that every who can vote is able to do so., and that
those who aren't allowed to vote can't do so.