February 17, 2011 at 10:12 am
Hello, I need to insert data from table 1 and table2, table 2 table is not accepting dubs,
That tables has 4 fields uniq. but 5 fields is is not uniq.. Can someone help me please.
Thank you
February 17, 2011 at 10:26 am
Krasavita, you're going to be a lot more explicit in your problem for specific assistance.
Basically you'll have to dedupe your data into table 2 from table 1 to get it to insert.
If you want more help, you'll really need to post DDL for the two tables, including constraints and indexes, sample data that shows the issue, and what you'd like it to do.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 10:35 am
table2 structure:
[SUN_DB] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NAME] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_SUN_Vendors] PRIMARY KEY CLUSTERED
(
[SUN_DB] ASC,
ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
table1 structure
[SUN_DB] [char](3) COLLATE Latin1_General_BIN NOT NULL,
[CATEGORY] [char](2) COLLATE Latin1_General_BIN NOT NULL,
[char](15) COLLATE Latin1_General_BIN NOT NULL,
[LOOKUP] [char](10) COLLATE Latin1_General_BIN NOT NULL,
[UPDATED] [char](8) COLLATE Latin1_General_BIN NOT NULL,
[NAME] [char](30) COLLATE Latin1_General_BIN NOT NULL,
[PROHB_POST] [char](1) COLLATE Latin1_General_BIN NOT NULL,
[BUDGET_CHECK] [char](1) COLLATE Latin1_General_BIN NOT NULL,
[BUDGET_STOP] [char](1) COLLATE Latin1_General_BIN NOT NULL,
[DATA_1] [char](5) COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY]
I need to insert fields:SUN_DB, CODE, [NAME]
Thank you
February 17, 2011 at 10:42 am
What is the insert statement that you are trying to use?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 17, 2011 at 10:44 am
insert into table2
SUN_DB, CODE, [NAME]
select
SUN_DB, CODE, [NAME]
from table1
February 17, 2011 at 10:46 am
There must be duplicate data in Table1 or rows already exist in Table2.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2011 at 10:55 am
CONSTRAINT [PK_SUN_Vendors] PRIMARY KEY CLUSTERED
(
[SUN_DB] ASC,
ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
Here's the reason you're not able to insert dupes. So, you'll need to find any dupes in table 1, and also make sure they don't already exist in table 2.
So:
SELECT sun_db, code from table1 group by sun_db, code having count(*) > 1
That will let you find dupes in the table1 on the key for table 2. You'll have to then go in and clean up the data.
Next:
select t1.*, t2.* from table1 as t1 join table2 as t2 on t1.sun_db = t2.sun_db and t1.code = t2.code
That will show you dupes between the tables.
You'll have to decide what you want to do about that, too. You could where clause them out, use a left join, etc.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 11:56 am
Thank you, but what I need if I enter data from t1 to t2 and there is a record like that in t2 don't enter and insert next one.
Thank you
February 17, 2011 at 12:01 pm
I'd use either a LEFT OUTER JOIN or a NOT EXISTS subqry.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply