Re: Moving Data Without CURSORS or Looping.

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 10/12/04 

Date: Tue, 12 Oct 2004 11:29:30 -0500

You will need to create an INSERT INTO....SELECT statement for each table
that you want to move.
Example:

INSERT INTO TheNewTable (lines__id, org__cd, ......)
SELECT seasonal_number, org_code, ......
FROM plan_transaction
WHERE ...

You can use the WHERE clause to make sure that you only insert rows that you
want.
The IF check -- you can probably do that with the CASE statement (refer to
SQL Server Books Online - within the SQL Server program group) for more
information on how to use CASE.
If columns need to come from other tables you can JOIN the tables together
within the SELECT portion of the INSERT statement. You could also go back
an UPDATE them after the table has been loaded, but it is probably more
efficient to do it all in one step. 

-- 
Keith
"Bob Cannistraci" <Bob Cannistraci@discussions.microsoft.com> wrote in
message news:25C77FD5-FCE0-4D0E-A60F-74D81BEEB6EA@microsoft.com...
> 46 tables from an old DB need to be moved into a new DB. The transfer is
not
> always literal. The new tables can contain data from multiple tables from
the
> old system. Some fields require a check by an IF statment before being
> transformed.
>
> I rather do this using SQL as it is intended to be used and deal with this
> task by moving sets. However, as I read the BOL my procedural mind can
only
> come up with using a cursor to solve this.
>
> Here is the DDL of one old table mapping to a new table. Is it possible to
> move this data without cursors or any type of looping and handle
> transformations where needed?
>
> CREATE TABLE lines  --new table
> (
> lines__id               integer NOT NULL,
> org__cd         char(10) NULL,
> position__cd char(13) NULL,
> fund_source__cd char(4) NULL,
> wrk_days_per_week smallint NULL,
> is_recurring bit    NULL,
> is_step_up bit    NULL,
> line_start_date smalldatetime NULL,
> line_end_date smalldatetime NULL,
> no_of_positions smallint NULL,
> budgets__cd char(4) NULL,
> entered_by char(7) NULL,
> entered_date smalldatetime NULL
> )
>
> CREATE TABLE plan_transaction
> (
> seasonal_number integer, -- lines__id
> org_code char(10), -- org__cd
> position_code           char(13), -- position__cd
> line_start_date    smalldatetime, -- line_start_date
> line_end_date    smalldatetime, -- line_end_date
> no_of_position          smallint, -- no_of_positions
> wrk_days_per_week smallint, --wrk_days_per_week
> budget_code char(4), -- budgets__cd
> fund_source_code char(4), -- fund_source__cd
> next_year_ind char(1), -- is_recurring
> number_of_steups smallint, -- is_step_up
> entered_by char(7), -- entered_by
> entered_date    smalldatetime, -- entered_by
> fiscal_year         smallint, -- dropped
> position_short_desc     char(10), -- dropped
> fy_work_days smallint, -- dropped
> rate                     integer, -- dropped
> plan_line_status char(1)  -- dropped
> )
>
> Thanks for your help.
>
> (Sorry if the formatting is screwed up. It looked perfect in the edit
window.)

Relevant Pages

  • Re: SmallDateTime
    ... Underneath the hood, it creates a SqlParameter of the proper type, so you ... Here is your corrected string: ... The second example will strongly type for SQL Server and will definitely ... convert from datetime to smalldatetime... ...
    (microsoft.public.dotnet.framework.adonet)
  • SQL Server Job step failing.
    ... but fails as part of the Job in '2005 (on the SQL Server 2005 d/b). ... smalldatetime data type resulted in an out-of-range smalldatetime value. ... Any thoughts as to why the '2005 Job will not execute this step, ...
    (microsoft.public.sqlserver.programming)
  • Re: Data Driven Query - Lookup where Clause
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... > im trying to write a lookup query for a Data driven Query. ... > im trying to compare a string value from a text file to a smalldatetime value. ... > Ive tried converting the textfile value to '27/02/2004' but am getting nowhere ...
    (microsoft.public.sqlserver.dts)
  • Re: Cross Tab Query Help!
    ... Have you tried a SQL Server news group? ... This is my first query: ... FROM dbo.ViwOEE_Source LEFT OUTER JOIN (SELECT SeqNum as Seq, MachNum ... WHERE (DateCode BETWEEN CAST('20060713' AS smalldatetime) ...
    (microsoft.public.access.queries)
  • Rank
    ... CREATE TABLE #TempForfaits ... TF_SNID [smallint] NOT NULL, ... TF_Day smalldatetime NOT NULL) ... Stijn Verrept. ...
    (microsoft.public.sqlserver.programming)
Quantcast