Numero de Factura Insert Masivo

12/08/2003 - 01:30 por Carlos Silberberg | Informe spam

Hola
Tengo 2 tablas casi iguales SalesB y SalesC.La tabla SalesC no tiene el
campo InvN (numero de factura). Desde la tabla SalesC pretendo hacer un
insert masivo a la tabla SalesB, pero me encuertro con el problema de como
le asigno el numero de Factura correlativo a la Tabla SalesB?
Espero ser claro, transcribo las tablas, desde ya muchas gracias.
CREATE TABLE [SalesC] (
[InvId] [int] IDENTITY (1, 1) NOT NULL ,
[InvDate] [datetime] NULL ,
[PaymentTerms] [int] NULL ,
[DueDate] AS ([invdate] + [paymentTerms]) ,
[CustomerId] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TotalInv] [smallmoney] NULL ,
[CustomerPO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShippingMethod] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipDate] [datetime] NULL ,
[DateEntry] [datetime] NULL ,
[UserBelstone] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [char] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SalesPersonId] [int] NULL ,
[PO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PL] [numeric](18, 0) NULL ,
[Shipping] [int] NULL ,
CONSTRAINT [PK_SalesC] PRIMARY KEY CLUSTERED
(
[InvN]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [SalesB] (
[InvId] [int] IDENTITY (1, 1) NOT NULL ,
[InvN] [int] NOT NULL ,
[InvDate] [datetime] NULL CONSTRAINT [DF_SalesB_InvDate] DEFAULT
(getdate()),
[PaymentTerms] [int] NULL ,
[DueDate] AS ([invdate] + [paymentTerms]) ,
[CustomerId] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TotalInv] [money] NULL ,
[CustomerPO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShippingMethod] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_SalesB_ShippingMethod] DEFAULT ('Cust. Pickup'),
[ShipDate] [datetime] NULL CONSTRAINT [DF_SalesB_ShipDate] DEFAULT
(getdate()),
[DateEntry] [datetime] NULL CONSTRAINT [DF_SalesB_DateEntry] DEFAULT
(getdate()),
[UserBelstone] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_SalesB_UserBelstone] DEFAULT (suser_sname()),
[Notes] [char] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SalesPersonId] [int] NULL ,
[PO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PL] [numeric](18, 0) NULL ,
[Shipping] [int] NULL ,
[NoPrint] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_SalesB] PRIMARY KEY CLUSTERED
(
[InvN]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Siga el debate

3 respuestas

Tengo una respuesta

Preguntas similare

Mostrar todos los temas similares

Leer las respuestas

#1 Liliana Sorrentino

12/08/2003 - 15:58 | Informe spam

Carlos:
¿no podrías definir InvN de SalesB como IDENTITY (1,1)?

"Carlos Silberberg" escribió en el mensaje
news:uZ#

Hola
Tengo 2 tablas casi iguales SalesB y SalesC.La tabla SalesC no tiene el
campo InvN (numero de factura). Desde la tabla SalesC pretendo hacer un
insert masivo a la tabla SalesB, pero me encuertro con el problema de como
le asigno el numero de Factura correlativo a la Tabla SalesB?
Espero ser claro, transcribo las tablas, desde ya muchas gracias.
CREATE TABLE [SalesC] (
[InvId] [int] IDENTITY (1, 1) NOT NULL ,
[InvDate] [datetime] NULL ,
[PaymentTerms] [int] NULL ,
[DueDate] AS ([invdate] + [paymentTerms]) ,
[CustomerId] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

[TotalInv] [smallmoney] NULL ,
[CustomerPO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShippingMethod] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipDate] [datetime] NULL ,
[DateEntry] [datetime] NULL ,
[UserBelstone] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [char] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SalesPersonId] [int] NULL ,
[PO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PL] [numeric](18, 0) NULL ,
[Shipping] [int] NULL ,
CONSTRAINT [PK_SalesC] PRIMARY KEY CLUSTERED
(
[InvN]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [SalesB] (
[InvId] [int] IDENTITY (1, 1) NOT NULL ,
[InvN] [int] NOT NULL ,
[InvDate] [datetime] NULL CONSTRAINT [DF_SalesB_InvDate] DEFAULT
(getdate()),
[PaymentTerms] [int] NULL ,
[DueDate] AS ([invdate] + [paymentTerms]) ,
[CustomerId] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

[TotalInv] [money] NULL ,
[CustomerPO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShippingMethod] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_SalesB_ShippingMethod] DEFAULT ('Cust. Pickup'),
[ShipDate] [datetime] NULL CONSTRAINT [DF_SalesB_ShipDate] DEFAULT
(getdate()),
[DateEntry] [datetime] NULL CONSTRAINT [DF_SalesB_DateEntry] DEFAULT
(getdate()),
[UserBelstone] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_SalesB_UserBelstone] DEFAULT (suser_sname()),
[Notes] [char] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SalesPersonId] [int] NULL ,
[PO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PL] [numeric](18, 0) NULL ,
[Shipping] [int] NULL ,
[NoPrint] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_SalesB] PRIMARY KEY CLUSTERED
(
[InvN]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Responder a este mensaje

#2 Carlos Silberberg

12/08/2003 - 22:57 | Informe spam

Liliana,
Gracias por contestar. La idea es no usar InvN como Identity, alguna idea?
Saludos, Carlos
"Carlos Silberberg" wrote in message
news:uZ%

Hola
Tengo 2 tablas casi iguales SalesB y SalesC.La tabla SalesC no tiene el
campo InvN (numero de factura). Desde la tabla SalesC pretendo hacer un
insert masivo a la tabla SalesB, pero me encuertro con el problema de como
le asigno el numero de Factura correlativo a la Tabla SalesB?
Espero ser claro, transcribo las tablas, desde ya muchas gracias.
CREATE TABLE [SalesC] (
[InvId] [int] IDENTITY (1, 1) NOT NULL ,
[InvDate] [datetime] NULL ,
[PaymentTerms] [int] NULL ,
[DueDate] AS ([invdate] + [paymentTerms]) ,
[CustomerId] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

[TotalInv] [smallmoney] NULL ,
[CustomerPO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShippingMethod] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipDate] [datetime] NULL ,
[DateEntry] [datetime] NULL ,
[UserBelstone] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [char] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SalesPersonId] [int] NULL ,
[PO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PL] [numeric](18, 0) NULL ,
[Shipping] [int] NULL ,
CONSTRAINT [PK_SalesC] PRIMARY KEY CLUSTERED
(
[InvN]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [SalesB] (
[InvId] [int] IDENTITY (1, 1) NOT NULL ,
[InvN] [int] NOT NULL ,
[InvDate] [datetime] NULL CONSTRAINT [DF_SalesB_InvDate] DEFAULT
(getdate()),
[PaymentTerms] [int] NULL ,
[DueDate] AS ([invdate] + [paymentTerms]) ,
[CustomerId] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

[TotalInv] [money] NULL ,
[CustomerPO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShippingMethod] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_SalesB_ShippingMethod] DEFAULT ('Cust. Pickup'),
[ShipDate] [datetime] NULL CONSTRAINT [DF_SalesB_ShipDate] DEFAULT
(getdate()),
[DateEntry] [datetime] NULL CONSTRAINT [DF_SalesB_DateEntry] DEFAULT
(getdate()),
[UserBelstone] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_SalesB_UserBelstone] DEFAULT (suser_sname()),
[Notes] [char] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SalesPersonId] [int] NULL ,
[PO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PL] [numeric](18, 0) NULL ,
[Shipping] [int] NULL ,
[NoPrint] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_SalesB] PRIMARY KEY CLUSTERED
(
[InvN]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Responder a este mensaje

#3 Javier Loria\(MVP\)

13/08/2003 - 03:08 | Informe spam

Hola Carlos:
Asumiendo que no tienen MILES de filas en esa tabla, ya que el codigo no
es muy eficiente y que la idea es que InvN sea algo asi como una nueva
numeracion, podrias hacer algo como:
INSERT SALESB (InvID, InvN, )
SELECT *
FROM ( SELECT C1.InvID, COUNT(*), MAX(C1.InvDate), ,
FROM SalesC AS C1 JOIN SalesC as C2 ON
C1.InvId>Â.InvID
GROUP BY C1.InvID) AS K

Los campos ... del SELECT interno deben usar alguna funcion de argregacion
para que corran o en su defecto estar en el GROUP BY y los Nulos podrian
perjudicar el query.

Saludos,

Javier Loria
Costa Rica (MVP)
Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
que pueda ser copiado y pegado al Query Analizer.
La version de SQL y Service Pack tambien ayuda.

Carlos Silberberg escribio:

Liliana,
Gracias por contestar. La idea es no usar InvN como Identity, alguna
idea? Saludos, Carlos
"Carlos Silberberg" wrote in message
news:uZ%

Hola
Tengo 2 tablas casi iguales SalesB y SalesC.La tabla SalesC no tiene
el campo InvN (numero de factura). Desde la tabla SalesC pretendo
hacer un insert masivo a la tabla SalesB, pero me encuertro con el
problema de como le asigno el numero de Factura correlativo a la
Tabla SalesB?
Espero ser claro, transcribo las tablas, desde ya muchas gracias.
CREATE TABLE [SalesC] (
[InvId] [int] IDENTITY (1, 1) NOT NULL ,
[InvDate] [datetime] NULL ,
[PaymentTerms] [int] NULL ,
[DueDate] AS ([invdate] + [paymentTerms]) ,
[CustomerId] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL , [TotalInv] [smallmoney] NULL ,
[CustomerPO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShippingMethod] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [ShipDate] [datetime] NULL ,
[DateEntry] [datetime] NULL ,
[UserBelstone] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [Notes] [char] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , [SalesPersonId] [int] NULL ,
[PO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PL] [numeric](18, 0) NULL ,
[Shipping] [int] NULL ,
CONSTRAINT [PK_SalesC] PRIMARY KEY CLUSTERED
(
[InvN]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [SalesB] (
[InvId] [int] IDENTITY (1, 1) NOT NULL ,
[InvN] [int] NOT NULL ,
[InvDate] [datetime] NULL CONSTRAINT [DF_SalesB_InvDate] DEFAULT
(getdate()),
[PaymentTerms] [int] NULL ,
[DueDate] AS ([invdate] + [paymentTerms]) ,
[CustomerId] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL , [TotalInv] [money] NULL ,
[CustomerPO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShippingMethod] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL CONSTRAINT [DF_SalesB_ShippingMethod] DEFAULT ('Cust. Pickup'),
[ShipDate] [datetime] NULL CONSTRAINT [DF_SalesB_ShipDate] DEFAULT
(getdate()),
[DateEntry] [datetime] NULL CONSTRAINT [DF_SalesB_DateEntry] DEFAULT
(getdate()),
[UserBelstone] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_SalesB_UserBelstone] DEFAULT (suser_sname()),
[Notes] [char] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SalesPersonId] [int] NULL ,
[PO] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PL] [numeric](18, 0) NULL ,
[Shipping] [int] NULL ,
[NoPrint] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_SalesB] PRIMARY KEY CLUSTERED
(
[InvN]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Siga el debate Respuesta

Responder a este mensaje

Ads by Google

Hacer una pregunta

Tengo una respuesta

Busqueda sugerida