load data to table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

load data to table

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2012-03-30 : 12:21:01

I have a new sql server 2008 r2 database that I am in the process of setting up. I would like to know the best method for loading data daily to table that has 2 columns that are foreign keys for two other tables. The data will be loaded to this history table daily and will be appended to the end of the table.

Thus can you tell me and/or point me to a reference that will show me if I would use an alter statement, possibly drop and recreate the table will all the data, use a truncate table statement and load the data? What do you suggest?

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-30 : 13:06:15

What the DDL of the table you are going to append to?

I would create a stage table...stage_yourTable (Col1 varchar(max), Col2 varchar(max), and I would TRUNCATE, then bcp the data to that table

I would then perform Audits to make sure the data was ok.

I would then insert the data to your base table

Is there a primary key on your base table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2012-03-30 : 15:53:03

The following pertains to your response:

1. My base table looks like the following:
CREATE TABLE [dbo].[Rej_History](
[MRH_Id] [int] IDENTITY(1,1) NOT NULL,
[MRH_Plan_Id] [int] NULL,
[MRH_Create_Date] [datetime] NULL,
[MRH_Code_ID] [int] NULL,
[MRH_Tran_Count] [numeric](18, 0) NULL,
[MRH_Batch_Size] [numeric](18, 0) NULL,
[MRH_Tran_Code_Description] [varchar](max) NULL
CONSTRAINT [PK_Rej_History] PRIMARY KEY CLUSTERED
(
[MRH_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Rej_History] WITH NOCHECK ADD CONSTRAINT [PK_MRH_RNumber] FOREIGN KEY([MRH_Plan_Id])
REFERENCES [dbo].[Rej_Plans] ([MRP_Plan_Id])
GO

ALTER TABLE [dbo].[Rej_History] WITH NOCHECK ADD CONSTRAINT [PK_MRH_Tran_Codes] FOREIGN KEY([MRH_Code_ID])
REFERENCES [dbo].[Tran_Codes] ([TRC_Code_Id])

2. There is no data in the table right now. i will be loading data for the first time.
3. Why would I do the following:
'I would create a stage table...stage_yourTable (Col1 varchar(max), Col2 varchar(max), and I would TRUNCATE, then bcp the data to that table'? Wouldn't it be better to just load the data directly?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-30 : 17:11:33

There a lots of ways to get the data from one palce to another. If you are going across servers you might want to look at SSIS. If it's on the same server you could use run a stored procedure (assuming the batch size isn't too large).

This seems pretty straight forward on the surface. Is there some issue or problem that you are trying to overcome?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-30 : 17:36:18

quote:
Originally posted by jassie

I have a new sql server 2008 r2 database that I am in the process of setting up. I would like to know the best method for loading data daily to table that has 2 columns that are foreign keys for two other tables. The data will be loaded to this history table daily and will be appended to the end of the table.

Thus can you tell me and/or point me to a reference that will show me if I would use an alter statement, possibly drop and recreate the table will all the data, use a truncate table statement and load the data? What do you suggest?

you cant truncate data from table if it has foreign keys.You might have to put a logic to find deltas (changes) since last run and then add it to history. Before that insert you should have two conditional inserts onto dependent table to check if master entry exists, if nit ,insert and generate a new reference key.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2012-04-01 : 17:04:07

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-02 : 00:17:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources